SMS Query Related

• Can I extract SMS query data into other programs? [15/10/99]
• Copying a Query [19/2/01]
• Creating a Query based on a Custom SMS_DEF.MOF [19/12/99]
• Creating a Subselect Query [21/12/99]
• Defining a Site in a Query/Collection [5/1/01]
• Getting the Remote Tools and Resource Explorer options to Display in Query Results [12/8/01]
• How do I Query for a Certain Piece of Software on my Network? [5/10/00]
• How to do a NOT Query [20/10/99]
• How to Inventory Internet Explorer [6/6/01]
• Is the SMS 2.0 Database Schema Published Anywhere? [13/02/04]
• Is there a Way to Query for Failed Client Installations? [09/02/04]
• Laptop Query [20/9/01]
• Producing a Query of Which Packages are Installed on Which Machines [23/6/03]
• Query Analyzer Tips [20/9/01]
• Query in Collection Rule May Fail [20/9/01]
• Querying the State of a Registry Key [18/9/01]
• Quick Snapshot of Applications and Their Descriptions [19/9/01]
• Running some of the Pre-defined Queries in the Admin Console return no results [13/11/00]
• Systems Not Appearing in Default Queries [24/1/01]
• Using NULL in Queries [20/9/01]
• Using Wildcards in SMS Queries [30/10/01]
• What are the Version Numbers for Office XP? [2/5/03]

Can I extract SMS query data into other programs?

Yes - If you are using version 1.2 of the MMC, you can use the 'Export List...' menu item (available by right-clicking on an item in the MMC), you can save information in the following file formats:

• Text (Tab Delimited) (*.txt)
• Text (Comma Delimited) (*.csv)
• Unicode Text (Tab Delimited) (*.txt)
• Unicode Text (Comma Delimited) (*.csv)

The link Where can I get version 1.2 of the MMC from provides details on where to obtain version 1.2 of the MMC.

In addition to the above, the BackOffice Resource Kit 4.5 provides the "SMSExtract.xlt" and "SMSExtract.xls" files which you can use to extract SMS query results into Excel spreadsheets where you can manipulate them. There is also an Access version ("SMSExtract.mdb") included in the Resource Kit.

If these don't do what you want, check out http://www.i405.com/sms/ which contains a better version of the Excel spreadsheet or check out Web Reports for SMS from Computing Edge's (a link to the Computing Edge's website can be found from the Third Party Tools and Utilities page)
 

Copying a Query

Couple of options:

• Go to 'Show Query Language' and copy what is in there, paste it into a new Query, and edit what you need to change. Very quick to do.
   
• Use the Query Backup and Restore Tool ('QryEdit.exe') from the BORK 4.5

Creating a Query based on a Custom SMS_DEF.MOF

I've created and tested a new SMS_DEF.MOF. Now I want to make a Query with the new fields, but I can't find it. Written in the 'ServicePacks.dos' is the following:

The class that the client component version data is available from is SMS_G_System_SMS_CLIENT in the root\SMS\site_<sitecode> namespace.

When you create a new query in the MMC, under 'Criteria' you get the 'General' window, with 'Simple Value' displayed, and alongside the 'WHERE:' field is the 'Select' button. Click 'Select' to get the 'ATTRIBUTE CLASS' dropdown list.
 

Creating a Subselect Query

I'm trying to make a Query based on a Collection that seems like it should be pretty simple. All members of 'Collection X' which are not a member of 'Collection Y' should be members of 'Collection Z'.

This is known as a "subselect" query. The following is an overview from Ian Turek and Dave Yarborough.

Contributed By: Ian Turek
Use a Subselect Query, for example:

select whatever where .resourceID is not in (collection selecting .resourceID

Contributed By: Dave Yarborough
Here is an example of a Query I made to find everyone who doesn't have Norton Anti-Virus:

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName ="navw32.exe")
 

Defining a Site in a Query/Collection

Using Travelling Mode is what causes the creation of multiple Site information as well as multiple IP addresses for the Client record (from DHCP assigning different addresses at the different visited locations), so I have seen many instances of a Client record with 3 or 4 different SMS "Assigned" sites and an equal amount of different IP addresses in the record.

You could write a Query similar to the one below that will show you all clients that don't travel:

All Systems where SMS Assigned site = "ED"
and
not
SMS Assigned Site = "Other code 1, Other code 2, other code 3"

Getting the Remote Tools and Resource Explorer options to Display in Query Results

Contributed By: Cliff Hobbs [MVP SMS]
Want to know how to get the "Remote Tools" and "Resource Explorer" options to display in your Query results? Here's how...  [Go to article]
 

How do I Query for a Certain Piece of Software on my Network?

I am trying to find a certain piece of software on our network unsuccessfully. How can I do a report (Query), on every software company and executable? Also what is the best way to learn queries?

This following query will show you all known software products by company:

select distinct SMS_G_System_SoftwareProduct.CompanyName, SMS_G_System_SoftwareProduct.ProductName from  SMS_R_System inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceID = SMS_R_System.ResourceId order by SMS_G_System_SoftwareProduct.CompanyName, SMS_G_System_SoftwareProduct.ProductName

As for learning about queries enable the SMS Provider log, run an adhoc query or predefined through the MMC.  Then go back to the SMS Provider log to see what commands the MMC issued against the database to see the query you just built in the Admin UI.  That's a pretty easy way to get the exact syntax for any query you could possibly want to right without being a SQL or SMS WMI guru.
 

How to do a NOT Query

How do I do a query where I want to find machines that are NOT in another query?

Details of how to this can be found in the following article which can be accessed at http://www.myitforum.com/articles/1/view.asp?id=179 but is reproduced here in case it becomes unavailable in the future.

You have to create two queries, the first one will give you a list of everyone who has the software, the second will return a list of everyone who does not have the software. The second will rely on the WQL that comprises the first. Create the first query like this:

1. Right click on 'Queries', select 'New', then 'Query'.
    
2. Give your query a name like 'All Systems with Microsoft Access'
    
3. Click on 'Edit Query Statement'.
    
4. In the 'General' tab click the add button (the yellow star('*')). In the 'Result Properties' dialog click on the 'Select' button. Select 'System' under 'Attribute Class' then select the 'Name' attribute. Click 'OK'. Click 'OK' again to close the 'Result Properties' dialog.
    
5. Next select the 'Criteria' tab and click the add button. In the 'Criterion Properties' dialog click the 'Select' button. In the 'Select Attribute' dialog select the Attribute class 'Software Files' and choose 'File Name' as the Attribute. Click 'OK'. Back in 'Criterion Properties' leave the 'Operator' set to 'is equal to' and type 'msaccess.exe' in the 'Value' field. Click 'OK' to close the Criterion Properties dialog.

Now you have designed the query that finds systems with the software. You will use this query in your subselected query to find systems that do not have this software.

Now to Create the Subselected Query:

1. Right click on "Queries", select "New", then "Query".
    
2. Give your query a name like "All Systems w/out Microsoft Access".
    
3. Click on "Edit Query Statement".
    
4. In the "General" tab click the add button (the yellow star). In the "Result Properties" dialog click on the "Select" button. Select "System" under "Attribute Class" then select the "Name" attribute. Click "OK". Click "OK" again to close the "Result Properties" dialog.
    
5. Next select the "Criteria" tab and click the add button. Use the "Criterion Type" pull down to select "Subselected values" as your Criterion Type. Click the "Select" button. In the "Select Attribute" dialog select the Attribute class "System" and choose "Name" as the Attribute. Note that you used "System.Name" as the results for your first query. Click "OK".
    
6. Back in "Criterion Properties" set the "Operator" to "is not in".
    
7. Below that, click "Browse" under "Subselect". In the "Browse Query" dialog select the "All Systems with Microsoft Access" query that you just created. Click "OK". You'll see the WQL code from your first query is pulled into your subselect statement for this query. Click "OK". Click "OK" again, then one last time to save your new query.

Now you have a query that uses the results of another query (the subselected values) to create it's own result set. Simply put this query says "Show me all the systems that aren't in this list".

The WQL for the second query might look something like this:

select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join
SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID =
SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select
distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join
SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID =
SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on
SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where
SMS_G_System_SoftwareFile.FileName = "hf070799.pwr")

How to Inventory Internet Explorer

How do I to create a query to get the different versions of Internet Explorer?

Contributed By: Cliff Hobbs [MVP SMS]
See the following article on myITforum:

http://www.myitforum.com/articles/1/view.asp?id=177

Remember that by default Software Inventory only inventories EXE files. To add DLL files, go to 'Client Agents | Software Inventory Client Agent' and click the 'Inventory Collection' tab then the Gold asterisk (*). Enter the file type(s) you want to add. The next time software inventory runs, the new information will be collected. Bear in mind that inventorying all DLLs on a machine may increase the size of the SMS database considerably (depending on the number of clients).
 

Is the SMS 2.0 Database Schema Published Anywhere?

Contributed By: Cliff Hobbs [MVP SMS] and "Hermes"
Wondering if the SMS Schema is published anywhere?..  [Go to article]
 

Is there a Way to Query for Failed Client Installations?

Contributed By: Wally Mead [MS]
Want to know which machines haven’t installed the SMS Client? Here’s some advice...  [Go to article]
 

Laptop Query

Contributed By: Cliff Hobbs [MVP SMS]
How do you query for Laptops in your organisation? Here are some suggestions...  [Go to article]
 

Producing a Query of Which Packages are Installed on Which Machines

From the microsoft.public.sms.admin newsgroup
So you’re wondering how you can produce a query that shows which SMS Packages have been installed on which machines? Well the answer is easier than you think…  [Go to article]
 

Query Analyzer Tips

Contributed By: Cliff Hobbs [MVP SMS]
Here are some useful tips on using Query Analyzer...  [Go to article]
 

Query in Collection Membership Rule May Fail

Contributed By: Cliff Hobbs [MVP SMS]
Are you having problems with queries used as collection membership rules returning invalid results? Read on...  [Go to article]
 

Querying the State of a Registry Key

Contributed By: Cliff Hobbs [MVP SMS]
How do you query the state of Registry key? Here's how ...  [Go to article]
 

Quick Snapshot of Applications and Their Descriptions

Contributed By: Cliff Hobbs [MVP SMS]
Want a quick snapshot of applications and their descriptions? Well here's the query ...  [Go to article]
 

Running some of the Pre-defined Queries in the Admin Console return no results

When I run some of the pre-defined queries in the Admin console they come back empty.

Contributed By: Cliff Hobbs [MVP SMS]
This is known issue as documented in 259861 'SMS: Some Queries Do Not Show Results Unless User Has Class Rights to Collections (All Instances)'.
 

Systems Not Appearing in Default Queries

Something similar happened at our site, except I thought that the site code data was there - just not the operating system, etc. When this happened to me, it was due to there being no hardware inventory in the database - only discovery data. I found that the WBEM software needed to be re-installed on many of my PCs before the hardware inventories could be properly collected.

You would be able to tell if this is your problem by looking at the 'hinv32.log' file for one of the machines with missing data. I can't remember the exact errors that were logged, but it's easy to see that it's not functioning properly. Microsoft was not very forthcoming in helping us to identify a cause or in finding an automated resolution. Basically you have to go to each of the machines, stop the 'WinMgmt' process, delete the 'WBEM' subdirectory, delete the 'WBEM' subdirectory key, and then re-run 'WBEMSDK.EXE' from the CAP. If you don't delete the stuff first, this exe won't run.
 

Using NULL in Queries

Contributed By: Cliff Hobbs [MVP SMS]
Want to know how and when to use NULL in SMS Queries? Here's how...  [Go to article]
 

Using Wildcards in SMS Queries

Contributed By: Cliff Hobbs [MVP SMS]
We're all familiar with the "%" wildcard, but did you know about all the OTHER ones? Check this out (from the Admin guide):

% (percent sign)
Any string of zero or more characters. For example:
Mo% searches for all names that begin with the letters mo (Mom, Morgan)
%mo searches for all names that end with mo (Satchmo)
%mo% searches for all names that include mo (Mom, Satchmo, tomorrow).

_ (underscore)
Any single character. For example:
_ill searches for any four-letter names ending with the letters ill (mill, Will)

[] (brackets)
Any single character within the specified range. For example:
[HD]an[eo]n searches for all names that begin with H or D, then an, then e or o, and then end with n (Hanson, Hansen, Danson, Dansen)
[B-K]enson searches for all names ending with enson that begin with any single letter from B through K (Benson, Jenson)

[^] (caret)
Any single character not within the specified range. For example:
M[^c]% searches for all names beginning with the letter M that do not have the letter c as the second letter (MacPherson)

To use the %, _, [], or [^] characters as literal characters rather than as wildcard characters, do the following:
Use square brackets around the percent sign, the underscore, and the open bracket (that is, [%], [_], and [[]). For example:
5[%] searches for 5%
[_]n searches for _n
[[] searches for [

Use the close bracket by itself. For example:
] searches for ]

Use the dash as the first character inside a set of square brackets. For example:
[-acdf] searches for -,a,c,d, or f

Hot Tip from Tony Smith: [15/10/01]

When configuring the query for a specific machine, the operator is set to look for "machine name is equal to" this "variable". When using wildcards this feature does not function properly. You must configure the query to look for all machines with "machine name is LIKE" (not equal to) this "variable". Otherwise it takes a long time to run and provides no return.

I'm sure most SMS Admin's could figure it out but having the instructions deal with wildcards and "equal to" and "is like" on the same sheet of paper may save someone time.
 

What are the Version Numbers for Office XP?

Contributed By: Cliff Hobbs [MVP SMS]
If you want to know the version numbers for Office XP then this article should help. If you’ve got any to add feel free to email them to Cliff and I’ll update this list…  [Go to article]