RE: SMS 2003 Query Software Distribution Status

Tech-Archive recommends: Speed Up your PC by fixing your registry


A collection can only contain system resources, user resources or group
resources.

This statement would allow you to create a collection of all systems
that reported this specific status message.

select
SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.Res
ourceDomainORWorkgroup,SYS.Client from SMS_StatusMessage as stat left
join SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID left
join SMS_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID
inner join SMS_StatMsgAttributes as att2 on stat.RecordID =
att2.RecordID join sms_r_system as SYS on stat.machinename = SYS.name
where stat.ModuleName = "SMS Client" and stat.MessageID = 10007 and
att2.AttributeID = 401 and att2.AttributeValue = "S0020001"

Kim Oppalfens
MVP SMS
In article <553F83A6-72FA-4083-912B-7A597B0907A1@xxxxxxxxxxxxx>,
BrianCfromBoulder@xxxxxxxxxxxxxxxxxxxxxxxxx says...
> Thank you for your reply! Yes, the query now works as intended. Question: How
> to turn this into a collection.
>
> When I import the query I receive a message back when I click OK indicating
> "The query statement that you entered is not valid. Please enter a valid
> query statement."
>
> How can I use the query as a collection? Can a collection only contain
> System Resource?
>
> "Kim Oppalfens" wrote:
>
> > In article <6918E94F-B77B-4660-BBF9-D29203CC4CD8@xxxxxxxxxxxxx>,
> > BrianCfromBoulder@xxxxxxxxxxxxxxxxxxxxxxxxx says...
> > > I found a solution that sort of works for me, but I need help fine-tuning it.
> > > Basically I've modified the original script down to show me systems that
> > > failed, but I need to hard code the Advertisement ID so it will work in a
> > > collection and not prompt.
> > >
> > > The original code is...
> > > select stat.*, ins.*, att1.*, att1.AttributeTime from SMS_StatusMessage as
> > > stat left join SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID
> > > left join SMS_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID
> > > inner join SMS_StatMsgAttributes as att2 on stat.RecordID = att2.RecordID
> > > where stat.ModuleName = "SMS Client" and stat.MessageID >= 10008 and
> > > stat.MessageID <= 10009 and att2.AttributeID = 401 and att2.AttributeValue =
> > > ##PRM:SMS_StatMsgAttributes.AttributeValue## and stat.SiteCode =
> > > ##PRM:SMS_StatusMessage.SiteCode## and att2.AttributeTime >=
> > > ##PRM:SMS_StatMsgAttributes.AttributeTime## order by att1.AttributeTime desc
> > >
> > > My modification is...
> > > select stat.*, ins.*, att1.*, att1.AttributeTime from SMS_StatusMessage as
> > > stat left join
> > > SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID left join
> > > SMS_StatMsgAttributes as att1
> > > on stat.RecordID = att1.RecordID inner join SMS_StatMsgAttributes as att2 on
> > > stat.RecordID =
> > > att2.RecordID where stat.ModuleName = "SMS Client" and stat.MessageID =
> > > 10070 and att2.AttributeID = 401 and att2.AttributeValue =
> > > ##PRM:SMS_StatMsgAttributes.AttributeValue##
> > >
> > > When I try and hard code AttributeValue it errors. Any help in getting this
> > > last step working is greatly appreciated!
> > >
> > > Original source for this code is
> > > http://myitforum.techtarget.com/articles/1/view.asp?id=843
> > >
> > > "Brian C. from Boulder" wrote:
> > >
> > > > Hi! I've been trying to create a query that I can then turn into a collection

--
Kim Oppalfens
MVP SMS
Computacenter Belgium
.

Relevant Pages

  • Re: Membership Query Rules
    ... Create a collection for system resources. ... Create a query rule, ... On the Criteria tab ad a criteria. ...
    (microsoft.public.sms.admin)
  • Re: system resource exceeded and query too complex
    ... "Too complex" occurs when Access doesn't understand the query as well as cases where the query is beyond limits ... "System resources" can be anything: a bad VBA function call, a bad driver, too many database links, transaction space exceeded, not enough temp space on the system drive, or calling a routine repeatedly that fails to release memory ... One of the problems with this kind of data is the possibility of infinite recursion What I have usually done is to cheat and create a local temp table that resolves the data into a non-normalized format, ... The code must be able to resolve the data in a finite number of steps, and if it can't it reports the issues the user must resolve brefore continuing. ...
    (microsoft.public.access.queries)
  • Re: Create a Collection of Systems by..
    ... What you want to do is to use a query which comes with the default SMS ... > To Create a "Collection" of system resources, where the LoggedOn User is a ... > pull together a collection of Systems based on the Group Membership ...
    (microsoft.public.sms.swdist)
  • RE: SMS 2003 Query Software Distribution Status
    ... The query worked as a collection and retrieved the systems so we ... "Kim Oppalfens" wrote: ... > A collection can only contain system resources, ... > MVP SMS ...
    (microsoft.public.sms.admin)
  • Re: checking available system memory?
    ... Darren> slice up one of my arrays so my routine works within the ... Darren> available system resources. ... Darren> can query how much memory is available? ...
    (comp.lang.python)