SMS Queries

This page contains examples of Queries. Problems and issues relating to Queries can be found on the Query Related page.

How to Query for All Software [19/10/00]
Queries for Client Advertisement Status [23/9/01]
Query for All NT and W2K Workstations, Clients and Their Service Pack Level [23/9/01]
Query for All Software on All Systems [23/9/01]
Query for All Systems with WMI v1.5 [21/9/01]
Query for Free Disk Space on Server Clients [25/1/01]
Query for Hardware Scan Over 5 Days Old (Servers) [23/9/01]
Query for Hardware Scan Over 5 Days Old (Workstations) [24/9/01]
Query for Installed Applications [22/7/01]
Query for Last Logon User, Machine Name and Full Name [22/9/01]
Query for NAV CE v7.5 [13/9/01]
Query for Systems Without NT SP4 or SP5 [21/9/01]
Query That Lists All Windows 95 Users, Their CPU and Processor Speed [21/2/02]
Query to Cross Reference User IDs in a Group by NetBIOS Name [17/9/01]
Query to Delete a Machine From the SMS Database [12/9/01]
Query to Detect All Systems with Static IP Addresses [20/9/01]
Query to Determine Master Browser [2/2/02]
Query to Find All Status of a Service [23/9/01]
Query to Find and Delete Systems with Inventory over 30 Days Old [updated 26/6/02]
Query to Find Old Versions of WUSER32.EXE [23/9/01]
Query to Find PDAs [9/9/01]
Query to Find Systems With a Particular File [23/9/01]
Query to Find Systems With THIS But Not THAT [22/9/01]
Query to Find Systems Without a File [21/9/01]
Query to Find Systems Without THIS or THAT [22/9/01]
Query to Find the Version of DCOM and WMI on 9x/ME [20/9/01]
Query to Identify the Type of SQL Connection [19/9/01]
Query to Isolate Server from Workstation [18/9/01]
Query to List by OS and SP Level [21/9/01]
Query to Look for Workstations that Haven't Run Inventory Since a Prompted Date [13/1/01]
Query to See Which Windows 95 Clients have File and Print Sharing Enabled [22/10/01]
Query to Select Defined Packages [22/9/01]
Query to Select DHCP Clients [10/9/01]
Query to Select ONLY SMS SP2 Clients [23/9/01]
Query to Select Specific File Name(s) [20/9/01]
Query to Show All Clients that Don't Travel [5/1/01]
Query to Show All Known Products by Company [20/9/01]
Query to Show DPs and CAPs [23/9/01]
Query to Show Machines Reporting Problems Longer Than 20 Days [19/9/01]
Query to Show Servers Rebooted Within Last 30 Days [21/9/01]
Query to Show the WBEM Version [23/9/01]
Query to Show which Workstations are on which Subnet [1/12/00]
Query to Test for DCOM [2/9/01]
Query to Test for NAV [23/9/01]
Query to Test for Office Patch Level [12/9/01]

How to Query for All Software

Two choices here:

Using SQL Query Analyzer, Access, Excel, whatever, run this Query against your SMS database to generate a list of all installed software:

select * from softwareproduct order by companyname, productname, productversion

Don't forget that when you press the 'Edit Query Statement' button (from the 'General' tab when creating the query), to check the 'Omit duplicate rows (select distinct)' from the 'General' tab of the 'Query Statement Properties'. If you don't do this, you will see each product listed several times (for example if the product is installed on ten PCs, you'll see it ten times).

An alternative query is

SELECT COUNT(SoftwareInventory.ClientId) AS "# of Users", SoftwareProduct.CompanyName, SoftwareProduct.ProductName, SoftwareProduct.ProductVersion FROM SoftwareInventory INNER JOIN SoftwareProduct ON SoftwareInventory.ProductId = SoftwareProduct.ProductId GROUP BY SoftwareProduct.CompanyName, SoftwareProduct.ProductName, SoftwareProduct.ProductVersion

This query could take a little time depending on the number of lines in your software inventory table.

If you get a syntax error message when copying and pasting this (or any other queries), into SMS:

Always be sure to preface your SQL statement with 'use databasename' where 'databasename' is the name of your SMS database. Or if you choose, you can just select the database name in the pull down box on SQL Analyzer.
Remove any '>' signs that maybe present when emails containing queries have been forwarded on.

Queries for Client Advertisement Status

Contributed By: Cliff Hobbs [MVP SMS]
Here are some queries that will tell you your Client Advertisement Status ... [Go to article]

Query for All NT and W2K Workstations, Clients and Their Service Pack Level

Contributed By: Cliff Hobbs [MVP SMS]
So you want to know what NT and Windows 2000 clients you have out there and their Service Pack level? No problem, this query will tell you ... [Go to article]

Query for All Software on All Systems

Contributed By: Cliff Hobbs [MVP SMS]
Want to know what software is out there? Here's a query to help ... [Go to article]

Query for All Systems with WMI v1.5

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query that will list all systems with WMI v1.5 ... [Go to article]

Query for Free Disk Space on Server Clients

This Query will show disk information. Limit to 'All Servers' or 'All NT Servers'.

select Sys.Name, LD.Name, LD.FreeSpace, LD.Size
   from SMS_R_System
      inner join SMS_G_System_LOGICAL_DISK as LD
         on LD.ResourceID = SMS_R_System.ResourceId
      inner join SMS_G_System_SYSTEM as Sys
         on Sys.ResourceID = SMS_R_System.ResourceId
   order by Sys.Name, LD.Name

Query for Hardware Scan Over 5 Days Old (Servers)

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query that will show you all of your Servers that haven't run a Hardware Inventory for 5 days or more ... [Go to article]

Query for Hardware Scan Over 5 Days Old (Workstations)

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query that will show you all of your Workstations that haven't run a Hardware Inventory for 5 days or more ... [Go to article]

Query for Installed Applications

Does anyone know of a way to collect or report an inventory of all "installed" Applications through SMS? I can bring up a list from reporting the .EXE files however this also includes programs that have been copied to the hard drive and not installed. I need to provide an accurate list of installed applications.

Contributed By: Cliff Hobbs [MVP SMS]
Yes as detailed in the following article:

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

Query for Last Logon User, Machine Name and Full Name

Contributed By: Cliff Hobbs [MVP SMS]

Want to know who the last logged onto your machines? Here's a query that will show you the Last Logon User, Machine Name and Full Name ... [Go to article]

Query for NAV CE v7.5

Contributed By: Cliff Hobbs [MVP SMS]
The name says it all... [Go to article]

If you want a query to test for Norton Anti-Virus then look no further ... [Go to article]

Query for Systems Without NT SP4 or SP5

Contributed By: Cliff Hobbs [MVP SMS]
Need to know which of your systems aren't running NT SP4 or SP5? This query will tell you ... [Go to article]

Query That Lists All Windows 95 Users, Their CPU And Processor Speed

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query that lists all Windows 95 Users, their CPU and Processor Speed ... [Go to article]

Query to Cross Reference User IDs in a Group by NetBIOS Name

Contributed By: Cliff Hobbs [MVP SMS]
Need a query to cross reference User IDs in a Group by NetBIOS Name? Look no further... [Go to article]

Query to Delete a Machine From the SMS Database

Contributed By: Cliff Hobbs [MVP SMS]
Need to do some housekeeping on your SMS database? Here's a query to help... [Go to article]

Query to Detect All Systems with Static IP Addresses

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query to show you all of your systems with static IP addresses ... [Go to article]

Query to Determine Master Browser

Contributed By: Cliff Hobbs [MVP SMS]
This query courtesy of Gabrie van Zanten will help you determine your Master Browser ... [Go to article]

Query to Find All Status of a Service

Contributed By: Cliff Hobbs [MVP SMS]
Want to know the Status of a Service? Here's an SMS Query to help you ... [Go to article]

Query to Find and Delete Systems with Inventory over 30 Days Old

Contributed By: Cliff Hobbs [MVP SMS]
How can you keep your SMS database up-to-date? Here's a couple of ways... [Go to article]

Query to Find Old Versions of WUSER32.EXE

Contributed By: Cliff Hobbs [MVP SMS]
Remote Control Problems? Want a query to find old versions of WUSER32.EXE? Use this query to identify the 1.x versions so it can be removed to alleviate problems upgrading to SMS 2.x... [Go to article]

Query to Find PDAs

Contributed By: Cliff Hobbs [MVP SMS]
You've got PDAs out there but how to you query for them? Here's how ... [Go to article]

Query to Find Systems With a Particular File

Contributed By: Cliff Hobbs [MVP SMS]
You want to find out which systems have a particular file but how do you write the query to do this? Here's one to help you along ... [Go to article]

Query to Find Systems With THIS But Not THAT

Contributed By: Cliff Hobbs [MVP SMS]
How do you write a query that finds systems with a particular file but also without a different file? Here's how ... [Go to article]

Query to Find Systems Without a File

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query to find systems without a file ... [Go to article]

Query to Find Systems Without THIS or THAT

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query to find systems without THIS file or THAT file ... [Go to article]

Query to Find the Version of DCOM and WMI on 9x/ME

Contributed By: Cliff Hobbs [MVP SMS]
How do you query on the version of DCOM and WMI on 9x/ME? Here's how ... [Go to article]

Query to Identify the Type of SQL Connection

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query to identify the type of SQL Connection ... [Go to article]

Query to Isolate Server from Workstation

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query that will tell you whether a system is a server or a workstation... [Go to article]

Query to List by OS and SP Level

Contributed By: Cliff Hobbs [MVP SMS]
Want to do a query to list your systems by OS and SP Level? Take a peek at this one ... [Go to article]

Query to Look for Workstations that Haven't Run Inventory Since a Prompted Date

I am trying to do query to look for workstations that have not run inventory collection since a prompted date. Here is what I have:

select SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEPART(DD, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan) < ##PRM:SMS_G_System_WORKSTATION_STATUS.LastHardwareScan##

It doesn't work though because of the format of the information returned in the last part of the query (I think)

Contributed By: Cliff Hobbs [MVP SMS]
Here's one for last Hardware Inventory:

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

Take special notice of the way the date must be formatted.

Query to See Which Windows 95 Clients have File and Print Sharing Enabled

Contributed By: Ed Aldrich [MVP SMS]
Ed has supplied the following for Windows 95 clients which you can revise as necessary:

select SMS_R_System.NetbiosName, SMS_R_System.LastLogonUserName, SMS_R_System.IPSubnets, SMS_G_System_DRIVER_VXD.Name from SMS_R_System inner join SMS_G_System_DRIVER_VXD on SMS_G_System_DRIVER_VXD.ResourceID =SMS_R_System.ResourceId where SMS_G_System_DRIVER_VXD.Name = "VSERVER"

Query to Select Defined Packages

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query to select Defined Packages ... [Go to article]

Query to Select DHCP Clients

Contributed By: Cliff Hobbs [MVP SMS]
Want to know how to query for DHCP Clients? Here's how... [Go to article]

Query to Select ONLY SMS SP2 Clients

Contributed By: Cliff Hobbs [MVP SMS]
Want to know which of your clients are SP2? Here's a query that solves the puzzle ... [Go to article]

Query to Select Specific File Name(s)

Contributed By: Cliff Hobbs [MVP SMS]
Here's a query you can use select specific file name(s) ... [Go to article]

Query to Show All Clients that Don't Travel

Here's a Query to show you all SMS 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"

Query to Show All Known Software Products by Company

Contributed By: Cliff Hobbs [MVP SMS]
So you want a query to show all known software products by company? Here you go... [Go to article]

Query to Show DPs and CAPs

Contributed By: Cliff Hobbs [MVP SMS]
Know where your DPs and CAPs are? If not here's a query that tell you ... [Go to article]

Query to Show Machines Reporting Problems Longer than 20 Days

Contributed By: Cliff Hobbs [MVP SMS]
Trying to keep tabs on machines with problems? Here's a query to show machines reporting problems longer than 20 Days ... [Go to article]

Query to Show Servers Rebooted Within Last 30 Days

Contributed By: Cliff Hobbs [MVP SMS]
The boss wants to know which servers have been rebooted in the last 30 days. How are you going to find out? Use this query … [Go to article]

Query to Show the WBEM Version

Contributed By: Cliff Hobbs [MVP SMS]
Want a Query to Show the version of WBEM your clients are running? Your wish is my command ... [Go to article]

Query to Show which Workstations are on which Subnet

I am trying to make a bunch of queries which will give me workstations on certain TCP/IP subnets. I have been able to create and export these queries with SMS Site Properties Manager. Here is an example of a query.

{*Query SMS_Query Name=InfoCenter - All NT Workstations on Subnet172.16.000.0 QueryID=DTC000B2*}
[SecurityVerbs(1287)]
instance of SMS_Query
{
Comments = "";
Expression = "select IPAddresses, IPSubnets, Name, NetbiosName, LastLogonUserName, LastLogonUserDomain, OperatingSystemNameandVersion, MACAddresses, SMSAssignedSites, ResourceDomainORWorkgroup, SMSUniqueIdentifier, ResourceId, ResourceType from SMS_R_System where IPAddresses like \"172.16.0.%\" and (OperatingSystemNameandVersion like \"Microsoft Windows NT Workstation 4.0\" or OperatingSystemNameandVersion like \"Microsoft Windows NT Workstation 5.0\") order by IPAddresses";
LimitToCollectionID = "";
Name = "InfoCenter - All NT Workstations on Subnet 172.16.000.0";
QueryID = "DTC000B2";
TargetClassName = "SMS_R_System";
};

What I have tried to do is import this example query into Access, Excel, Word etc. and generate a series of queries which run through Subnet 172.16.xxx.0 through Subnet 172.16.yyy.0. This will save me a lot of time if I can get it to work. The problem is the code that is generated does not work at all. It seems that there are certain special characters which are part of the exported query which are not happy being manipulated.

Has anyone done this before, and if so what tool should I use to do this? I would hate to manually enter 250 queries while I can have a machine increment numbers for me and import it with Site Properties Manager.

If you have already created the queries then you can run them in Excel using the 'SMSQuery.xls' spreadsheet that comes with SMS. Stephen Gutknecht updated it to work with more sophisticated queries and his version can be found at:

http://www.i405.com/sms/

This gives you a list of queries at a site and lets you import the results, and run several at once in batches.