All about Systems Management / ConfigMgr

Saturday, August 9, 2008

ALL SQL Queries

SQL Query To Retrieve Advanced Clients Assigned Site Code And Client Version

This SQL Query will allow you to list the assigned site code and client version numbers for your advanced client resources.

SQL Query:

Select

SD.Name0 'Machine Name',

SC.SMS_Assigned_Sites0 'Assigned Site',

SD.Client_Version0 Version

From v_R_System SD

Join v_RA_System_SmsAssignedSites SC on SD.ResourceID = SC.ResourceID

Join v_GS_Operating_System OS on SD.ResourceID = OS.ResourceID

Where SD.Client0 = 1

And SD.Client_Type0 = 1

Order By 'Machine Name'

SQL Query To Gather Video Card Memory Information

This SQL query will gather video card memory information in Megabytes (Mb) for the machines in your database.

SQL Query:

Select

SD.Name0 'Machine Name',

VC.Name0 'Video Card',

Convert(VarChar, VC.AdapterRam0 / 1024) + ' MB'

From v_R_System SD

Join v_Gs_Video_Controller VC on SD.ResourceID = VC.ResourceID

Where VC.Name0 <> 'ConfigMgr Remote Control Driver'

Order By SD.Name0

SQL Query To See When Collections Were Last Updated

This SQL Query will list all of the collections on the site server from which the query is executed on and will write the collection ID, Collection Name and the time stamp for when the collection was last updated.

SQL Query:

Select

CC.CollectionID,

CN.CollectionName,

Convert(VarChar(10), CC.TimeUpdated, 101) 'Last Updated'

From Collection_MemberChg_Notif CC

Join Collections CN on CC.CollectionID = CN.SiteID

Order By CollectionName

SQL Query To Get Members Of A Specified Collection

This SQL Query will retrieve the machine names from a specified collection.

SQL Query:

Select

Members.Name

'Collection Members:'

From CollectionMembers Members

Join Collections Coll on Members.SiteID = Coll.SiteID

Where Coll.CollectionName = 'All Systems'

To see a list of all the available Collections use this query and then change the 'All Systems' collection name as needed:

Select CollectionName From Collections

SQL Query Get User Information From A Specified OU

This SQL query will return the following information from a specified Organizational Unit (OU): Machine name, User Name, Full name, Users OU and its Subnet.

SQL Query:

Select Distinct

CS.Name0 'Machine Name',

CS.UserName0 'User Name',

RU.Full_User_Name0 'Full Name',

UOU.User_OU_Name0 'Users OU',

RA.IP_Subnets0 'Subnet'

From v_Gs_Computer_System CS

Join v_RA_System_IPSubnets RA on RA.ResourceID = CS.ResourceID

Join v_R_User RU on RU.Unique_User_Name0 = CS.UserName0

Join v_RA_User_UserOUName UOU on UOU.ResourceID = RU.ResourceID

Where UOU.User_OU_Name0 = 'DomainName.COM/OuName'

Order by CS.Name0, CS.Username0, RU.Full_User_Name0, RA.IP_Subnets0

SQL Query For Windows Media Player Versions

This SQL query will allow you to retrieve Windows Media Player Version information for resources in your database.

SQL Query:

Select Distinct

v_R_System.Name0,

v_GS_SoftwareFile.FileName,

v_GS_SoftwareFile.FileDescription,

v_GS_SoftwareFile.FileVersion

From v_R_System

Join v_GS_SoftwareFile on v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID

Where FileName = 'wmplayer.exe'

SQL Query To Enumerate ConfigMgr 2007 Boundaries

This SQL query will enumerate your ConfigMgr 2007 site boundaries and will use Case statements for the Boundary Type, Connection and if it is protected or not.

SQL Query:

Select

Value,

SiteCode,

'Boundary Type' = Case

When BoundaryType = 0 Then 'IP Subnet'

When BoundaryType = 1 Then 'Active Directory Site'

When BoundaryType = 2 Then 'IPv6 Prefix'

When BoundaryType = 3 Then 'IP Address Range'

Else 'UnKnown'

End,

DisplayName,

'Connection' = Case

When BoundaryFlags = 0 Then 'Fast'

When BoundaryFlags = 1 Then 'Slow'

Else 'UnKnown'

End,

'Protected' = Case

When Action = 0 Then 'No'

When Action = 1 Then 'Yes'

Else 'UnKnown'

End

From Boundary

SQL Query Get Machine and User Information From A Specified Collection

This SQL query will return machine and user information from a specified collection.

Included will be the Machine name, Resource domain name, Login user ID, the account use domain, user's full name, serial number, manufacturer, model and the machines assigned site.

SQL Query:

Select

SD.Name0 'Machine Name',

SD.Resource_Domain_OR_Workgr0 'Resource Domain',

SD.User_Name0 'Login ID',

SD.User_Domain0 'Account Domain',

USR.Full_User_Name0 'Full Name',

PCB.SerialNumber0 'Serial Number',

CS.Manufacturer0 Manufacturer,

CS.Model0 Model,

SAS.SMS_Assigned_Sites0 'Assigned Site Code'

From v_R_System SD

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

Join v_R_User USR on SD.User_Name0 = USR.User_Name0

Join v_GS_PC_BIOS PCB on SD.ResourceID = PCB.ResourceID

Join v_GS_COMPUTER_SYSTEM CS on SD.ResourceID = CS.ResourceID

Join v_RA_System_SMSAssignedSites SAS on SD.ResourceID = SAS.ResourceID

Where COL.Name = 'All Systems'

Creating A Web Report To Get Machine and User Information From A Specified Collection

This post is based on a previous post entitled: SQL Query Get Machine and User Information From Specified Collection which was a SQL query that would return machine and user information from a specified collection.

Included will be the Machine name, Resource domain name, Login user ID, the account use domain, user's full name, serial number, manufacturer, model and the machines assigned site.

I am writing this post as part of a By Request to convert the pervious post into a Web Report.

To create a new Web report specify the following after selecting "New" > "Report":

Name: Get Machine and User Information From Specified Collection

Category: Users

Comment: This SQL query will return machine and user information from a specified collection.

Select "Edit SQL Statement" and add the following:

Select

SD.Name0 'Machine Name',

SD.Resource_Domain_OR_Workgr0 'Resource Domain',

SD.User_Name0 'Login ID',

SD.User_Domain0 'Account Domain',

USR.Full_User_Name0 'Full Name',

PCB.SerialNumber0 'Serial Number',

CS.Manufacturer0 Manufacturer,

CS.Model0 Model,

SAS.SMS_Assigned_Sites0 'Assigned Site Code'

From v_R_System SD

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

Join v_R_User USR on SD.User_Name0 = USR.User_Name0

Join v_GS_PC_BIOS PCB on SD.ResourceID = PCB.ResourceID

Join v_GS_COMPUTER_SYSTEM CS on SD.ResourceID = CS.ResourceID

Join v_RA_System_SMSAssignedSites SAS on SD.ResourceID = SAS.ResourceID

Where COL.Name = @ID

Select "Prompts" and select New (The *) and add the following:

Name: ID

Prompt Text: Specify a collection Name

Select the checkbox to "Provide a SQL statement" and the select "Edit SQL Statement" and enter the following:

Begin

If (@__filterwildcard = '')

Select Distinct Name From v_Collection

Order by Name

Else Select Distinct Name From v_Collection

Where Name like @__filterwildcard

Order By Name

End

Select "OK" to the dialog boxes until you return to the "New Report Wizard" task and then select "Next" until the report is created and then "Close" the wizard.

SQL Query To Retrieve Clients Last Boot up Date

This SQL Query will return machine names and their last boot up date timestamp.

Tip: To query an individual machine Change the line that reads Order By 'Machine Name' to Where SD.Name0 = 'MachineName'

Select

SD.Name0 'Machine Name',

SD.User_Name0 'Last Logged on User Name',

Convert(VarChar(10), OS.LastBootUpTime0, 101) 'Last Boot Date'

From v_R_System SD

Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID

Order By 'Machine Name'

SQL Query To List Machines With IIS , FTP Or Telnet Installed

This SQL query will list all of the machines that have the World Wide Web Publishing Service, the FTP Publishing Service or the Telnet server services installed by their Service name.

SQL Query:

Select

SD.Name0 'Machine Name',

SD.Operating_System_Name_and0 NOS,

SS.Name0 'Service Name',

SS.DisplayName0 'Display Name',

SS.StartMode0 'Start Type',

SS.Started0 Started,

SS.State0 State,

SS.Status0 Status

From System_DISC SD

Join Services_DATA SS

on SS.MachineID = SD.ItemKey

Where SS.Name0 In ('W3SVC', 'MsFtpSvc', 'TlntSvr')

Order By 'Machine Name'

SQL Query To Count The Number Of Client Machines With McAfee Virus Scan Installed

This SQL query was written as a request from a reader wanting to know how to count the number of machines that have McAfee VirusScan Enterprise installed by using the Add And Remove Programs applet.

SQL Query:

Select

Count(SD.Name0) Counts,

PF.DisplayName0,

PF.Version0

From v_Add_Remove_Programs PF

Join v_R_System SD on PF.ResourceID = SD.ResourceID

Where PF.DisplayName0 = 'McAfee VirusScan Enterprise'

Group By PF.DisplayName0, PF.Version0

Order By Counts, PF.Version0

To see the machine names rather than the counts use this query:

Select

SD.Name0,

PF.DisplayName0,

PF.Version0

From v_Add_Remove_Programs PF

Join v_R_System SD on PF.ResourceID = SD.ResourceID

Where PF.DisplayName0 = 'McAfee VirusScan Enterprise'

Group By SD.Name0, PF.DisplayName0, PF.Version0

Order By SD.Name0

Advertisement Status For Multiple Advertisement IDs

This SQL query will allow you to retrieve Advertisement information for multiple advertisement ID's.

SQL Query:

Select

OfferName Name,

Recieved,

Failures,

ProgramsStarted Started,

ProgramsFailure Errors,

ProgramsSuccess Success,

OfferID 'Advertisement ID'

From vOfferStatusSummarizerRoot

Where ScheduleToken = '0001128000080008'

And OfferID In ('XXX12345', 'XXX67890')

Order by OfferName

SQL Query To List Roaming Boundary IP Subnets

This SQL query will return all of the Roaming IP subnet boundaries for a specified site code.

SQL Query:

Select IPSubnet

From RoamingBoundaryIPSubnet

Where SiteCode = 'XXX'

Order by SiteCode

SQL Query To Locate Full User Names From A Specified Collection

This SQL query will allow you to retrieve the full user name and login ID name for all machines in a specific collection.

SQL Query:

Select

SD.Name0 'Machine Name',

SD.User_Name0 'Login ID',

USR.Full_User_Name0 'Full Name'

From v_R_System SD

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

Join v_R_User USR on SD.User_Name0 = USR.User_Name0

Where COL.Name = 'All Systems'

SQL Query To Find Machines In A Specified Collection Having A Specified Application Installed

This SQL query will allow you to find machines in a specified collection that have a specified application installed.

SQL Query:

Select

SD.Name0 'Machine Name',

SD.Resource_Domain_OR_Workgr0 'Resource Domain',

SD.User_Name0 'Login ID',

SD.User_Domain0 'Account Domain',

USR.Full_User_Name0 'Full Name',

ARP.DisplayName0 'Display Name'

From v_R_System SD

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection CN on FCM.CollectionID = CN.CollectionID

Join v_R_User USR on SD.User_Name0 = USR.User_Name0

Join v_Add_Remove_Programs ARP on SD.ResourceID = ARP.ResourceID

Where CN.Name = 'All Systems'

And ARP.DisplayName0 Like '%VPN%'

SQL Query To View Web Reports By Category

This SQL Query will allow you to view Web Reports by category for your reference.

SQL Query:

Select

Name,

Comment,

ReportId,

SQL Query To Retrieve A Users Full Name

This SQL Query will allow you to find the Full Name for a specified users Logon name.

SQL Query:

Select

SD.Name0 'Machine Name',

SD.User_Name0 'Logon Name',

UD.Full_User_Name0 'Full Name'

From v_R_System SD

Join v_R_User UD on SD.User_Name0 = UD.User_Name0

Where SD.User_Name0 = 'LogonId'

SQL Queries To Locate Client Resources For A Specified Subnet

Provided here are SQL queries that will allow you to locate client resources from a specified subnet. The first example specifies (=) a subnet, the second one uses a wildcard (Like) and finally the third allows you to specify multiple subnets.

Specific Search:

Select Distinct

SD.Name0,

IP.Ip_Subnets0

From v_Ra_System_IpSubnets IP

Join v_R_System SD on IP.ResourceID = SD.ResourceID

Where IP.Ip_Subnets0 = '192.168.1.0'

Wildcard Search:

Select Distinct

SD.Name0,

IP.Ip_Subnets0

From v_Ra_System_IpSubnets IP

Join v_R_System SD on IP.ResourceID = SD.ResourceID

Where IP.Ip_Subnets0 Like '192.168.1.%'

Multiple Searches:

Select Distinct

SD.Name0,

IP.Ip_Subnets0

From v_Ra_System_IpSubnets IP

Join v_R_System SD on IP.ResourceID = SD.ResourceID

Where IP.Ip_Subnets0 In

('192.168.1.0',

'192.168.2.0')

Order by IP.Ip_Subnets0,SD.Name0

SQL Query To Count Microsoft Office Versions From Add And Remove Programs

This SQL Query will allow you to get a count of the Microsoft Office versions deployed.

SQL Query:

Select

Count(ResourceID) Counts,

DisplayName0,

Publisher0,

Version0

From v_Add_Remove_Programs

Where Publisher0 = 'Microsoft Corporation'

And DisplayName0 Like 'Microsoft Office%'

Group By DisplayName0, Publisher0, version0

Order By Counts Desc

SQL Query To Enumerate ConfigMgr 2007 Web Reports Information

This SQL Query will list all of the installed Web Reports information for the sever on which the script is executed on.

The following information will be displayed: ReportID, Name, Category, Comment and the appropriate SQL query.

SQL Query:

Select

VR.ReportID,

VR.Name,

VR.Category,

VR.Comment,

VR.SQLQuery

From v_Report VR

Join v_ReportParameter RP

on VR.ReportID = RP.ReportID

SQL Query To Count Assigned And Installed Resources By Site Code

This SQL Query will allow you to count the assigned and installed resources for a specified site code.

SQL Query:

Select

SC.SiteCode 'Site Code',

(Select Count(ResourceId) From v_RA_System_SMSAssignedSites

Where SMS_Assigned_Sites0 = 'XXX') 'Assigned',

(Select Count(ResourceID) From v_RA_System_SMSInstalledSites

Where SMS_Installed_Sites0 = 'XXX')'Installed'

From v_Site SC

Order by SC.SiteCode

SQL Quick Counting Script Examples

Provided here are some quick SQL counting scripts to get you started in creating your own count scripts.

The first script counts all of the records in the System Discovery table. The second script counts all of the records in the System Discovery table where the resources are clients. Finally the third counts records in the System Discovery table where the resources are not clients.

The last three scripts are in the same order as the first three scripts but will show you how to specify a column name to count.

Select Count(*) 'Total Count' From System_Disc

Select Count(*) 'Client Count' From System_Disc

Where Client0 = 1

Select Count(*) 'Non-Client Count' From System_Disc

Where Client0 Is NULL

Specifies a column to count:

Select Count(Name0) 'Total Count' From System_Disc

Select Count(Name0) 'Client Count' From System_Disc

Where Client0 = 1

Select Count(Name0) 'Non-Client Count' From System_Disc

Where Client0 Is NULL

SQL Query To Count Computer Types For A Specified Collection Name

This SQL query will allow you to retrieve a count of the Computer types that reside in a specific collection. For example if you want to know how many Desktops and Laptops are in a collection such as the "All Systems" collection this query will allow you to see how many there are.

SQL Query:

Select

CN.Name 'Collection Name',

Case SE.ChassisTypes0

When 1 Then 'Other'

When 2 Then 'Unknown'

When 3 Then 'Desktop'

When 4 Then 'Low Profile Desktop'

When 5 Then 'PizzaBox'

When 6 Then 'Mini-Tower'

When 7 Then 'Tower'

When 8 Then 'Portable'

When 9 Then 'Laptop'

When 10 Then 'Notebook'

When 11 Then 'Handheld Device'

When 12 Then 'Docking Station'

When 13 Then 'All-In-One'

When 14 Then 'Sub-Notebook'

When 15 Then 'Space Saving'

When 16 Then 'Lunch Box'

When 17 Then 'Main System Chassis'

When 18 Then 'Expansion Chassis'

When 19 Then 'Sub-Chassis'

When 20 Then 'Bus Expansion Chassis'

When 21 Then 'Peripheral Chassis'

When 22 Then 'Storage Chassis'

When 23 Then 'Rack-Mount Chassis'

When 24 Then 'Sealed PC'

Else 'Unknown'

End 'Chassis Type',

Count(*) 'Chassis Count'

From v_Collection CN

Join v_FullCollectionMembership CM on CN.CollectionID = CM.CollectionID

Join v_R_System SD on CM.ResourceID = SD.ResourceID

Join v_Gs_System_Enclosure SE on SD.ResourceID = SE.ResourceID

Where CN.Name = 'All Systems'

Group By CN.Name, SE.ChassisTypes0

Order By CN.Name

SQL Query Add And Removed Programs For A Collection

This SQL Query will allow you to retrieve Add And Removed Programs for all resources in a specified collection.

SQL Query:

Select

SD.Name0 'Machine Name',

PF.DisplayName0 'Display Name',

PF.ProdID0 'Product ID',

PF.Publisher0 Publisher,

PF.Version0 Version

From v_R_System SD

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

Join v_Add_Remove_Programs PF on SD.ResourceID = PF.ResourceID

Where COL.Name = 'All Systems'

Group By SD.Name0, COL.CollectionID, COL.Name, PF.DisplayName0,

PF.ProdID0, PF.Publisher0, PF.Version0

Order By SD.Name0

SQL Query To Find Machines With Torrent Downloading Clients Installed

This SQL query will find machines with Peer-To-Peer (P2P) Torrent File Sharing downloading Client's installed.

The script below actually began as a By Request script that I was asked to create for a reader who wanted to know how to find machines with any versions of either UTorrent or BitTorrent installed. Since I know next to nothing about P2P clients I went to the web to find out more information. Then I added some of the more seemingly popular ones to the list. I chose to use "Like" rather than UTorrent.Exe to capture both the actual client applications and install downloads.

Note: I am sure that there are more P2P client applications out there specifically for downloading Torrent files so if you find any please comment on this post for the benefit of others.

Torrent SQL Query:

Select

SD.Name0,

SF.FileName

From v_Gs_SoftwareFile SF

Join v_R_System SD on SD.ResourceId = SF.ResourceId

Where SF.FileName Like '%Azureus%'

Or SF.FileName Like '%BitComet%'

Or SF.FileName Like '%BitLord%'

Or SF.FileName Like '%BitPump%'

Or SF.FileName Like '%BitTornado%'

Or SF.FileName Like '%BitTorrent%'

Or SF.FileName Like '%Shareaza%'

Or SF.FileName Like '%Utorrent%'

Order By SD.Name0, SF.FileName

Below is a SQL query to find applications that are not only used to download Torrent files but other files as well using P2P technology.

Other File Sharing Applications SQL Query:

Select

SD.Name0,

SF.FileName

From v_Gs_SoftwareFile SF

Join v_R_System SD on SD.ResourceId = SF.ResourceId

Where SF.FileName Like '%BearShare%'

Or SF.FileName Like '%eDonkey%'

Or SF.FileName Like '%Emule%'

Or SF.FileName Like '%Kazaa%'

Or SF.FileName Like '%LimeWire%'

Or SF.FileName Like '%Morpheus%'

Order By SD.Name0, SF.FileName

The following SMS WQL query will locate users within your SMS sites that have prohibited or unnecessary software installed on their machines.

##### Begin Copy And Paste #####

Select Distinct
SMS_R_System.Name,
SMS_R_System.LastLogonUserName,
SMS_G_System_USER.FullName,
SMS_G_System_SoftwareFile.FileName,
SMS_G_System_SoftwareFile.FileDescription
From SMS_R_System
Inner join SMS_G_System_SoftwareFile
On SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
Inner join SMS_G_System_USER
On SMS_G_System_USER.Name = SMS_R_System.LastLogonUserName
Where SMS_G_System_SoftwareFile.FileName like "%Crack%"
Or SMS_G_System_SoftwareFile.FileName like "%Diablo%"
Or SMS_G_System_SoftwareFile.FileName like "%Getadmin%"
Or SMS_G_System_SoftwareFile.FileName like "%Napster%"
Or SMS_G_System_SoftwareFile.FileName like "%Doom%"
Or SMS_G_System_SoftwareFile.FileName like "%Hack%"
Or SMS_G_System_SoftwareFile.FileName like "%Morpheus%"
Or SMS_G_System_SoftwareFile.FileName like "%Napster%"
Or SMS_G_System_SoftwareFile.FileName like "%Nuke%"
Or SMS_G_System_SoftwareFile.FileName like "%Quake%"
Or SMS_G_System_SoftwareFile.FileName like "%Kazaa%"
Or SMS_G_System_SoftwareFile.FileName like "%Lopht%"

##### End Copy And Paste #####

SQL Query To Retrieve Machines In A Specified Collections Last Logged On User Information

This SQL query will allow you to specify a collection name such as the All Systems collection and will retrieve the Machine name, Last logged on users Login ID and their Full user name.

SQL Query:

Select

C.CollectionName,

M.Name,

S.User_Name0,

U.Full_User_Name0

From Collections C

Join CollectionMembers M on C.SiteID = M.SiteID

Join System_Disc S on M.Name = S.Name0

Join User_DISC U on S.User_Name0 = U.User_Name0

Where CollectionName = 'All Systems'

SQL Query To Retrieve The Machine Name And Asset Tag For A Specified Serial Number

This SQL Query will allow you to search for a machine and its asset tag from a known serial number.

Note: Change MUR0FTIYM to the serial number you want to search for.

SQL Query:

Select

SD.Name0 'Machine Name',

SE.SerialNumber00 'Serial Number',

SE.SMBIOSAssetTag00 'Asset Tag'

From System_Disc SD

Join System_Enclosure_Data SE on SE.MachineID = SD.ItemKey

Where SE.SerialNumber00 = 'MUR0FTIYM'

SQL Query Get Users OU Name

SQL Query to retrieve users Organizational Unit (OU) name as well as their last logged on machine name, logon ID and full name.

SQL Query:

Select Distinct

S.Name0 'Machine Name',

S.User_Name0 'Logon ID',

U.Full_User_Name0 'Full Name',

O.User_Ou_Name0 'Organizational Unit'

From v_R_System S

Join v_R_User U on S.User_Name0 = U.User_Name0

Full Join v_Ra_User_UserOuName O on U.Unique_User_Name0 = O.User_Ou_Name0

Where O.User_Ou_Name0 Is Not Null

Retrieve All Queries Information

Use the SQL script below to retrieves All Queries Information from your SMS SQL server.

SQL Query:

Select

QueryKey 'Query ID',

Name 'Query Name',

Comments 'Query Comments',

Architecture 'Query Object Type',

CollectionID 'Limit To Collection ID'

From Queries

SQL Query To Find And Count Software File And Product Information For A Specified Executable

This SQL query will allow you to find and count software file and product information for a specified executable.

SQL Query:

Select

SF.FileId 'File ID',

SF.ProductId 'Product ID',

SF.FileName 'File Name',

SF.FileDescription 'File Description',

SF.FileSize 'File Size',

SF.FileVersion 'File Version',

SP.ProductName 'Product Name',

SP.ProductVersion 'Product Version',

SP.CompanyName 'Company Name',

Count(SF.FileName) 'Installed Count'

From System_DISC SD

Join vSMS_G_System_SoftwareFile SF on SF.ClientId = SD.ItemKey

Join vSMS_G_System_SoftwareProduct SP on SP.ClientId = SD.ItemKey

Where SF.ProductId = SP.ProductId

And SF.FileName = 'FileName.Exe'

Group by SF.FileId,SF.ProductId,SF.FileName,SF.FileDescription,

SF.FileSize,SF.FileVersion,SP.ProductName,SP.ProductVersion,SP.CompanyName

Order By SF.FileId ASC

List Total Number Of Records In The Software File Table

This query will return the number of software file records in the current database. Just for fun execute it to see just how many unique file executables you have in your database and be prepared for a shock

SQL Query:

Select Count(*) as 'Total Number Of Records'

From SoftwareFile

SQL Queries To Retrieve Serial Number Information

Here you will find SQL queries to allow you to retrieve All Systems Serial Numbers, The Serial Number For A Specified Machine and The Machine Name For A Specified Serial Number.

All Systems Serial Numbers

Select

SD.Name0 'Machine Name',

SB.SerialNumber0 'Serial Number'

From v_R_System SD

Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID

Order By SD.Name0

The Serial Number For A Specified Machine

Select

SD.Name0 'Machine Name',

SB.SerialNumber0 'Serial Number'

From v_R_System SD

Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID

Where SD.Name0 = 'Machine_Name'

Order By SD.Name0

The Machine Name For A Specified Serial Number

Select

SD.Name0 'Machine Name',

SB.SerialNumber0 'Serial Number'

From v_R_System SD

Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID

Where SB.SerialNumber0 = 'Serial_Number'

Order By SD.Name0

SQL Query To Return Add OR Remove Programs Installed On A Specified Machine

This SQL Query will return the Add and Remove applications listed on an SMS client machine.

SQL Query:

Select

P.DisplayName0,

P.ProdId0,

P.Publisher0,

P.Version0

From v_Add_Remove_Programs P

Join v_R_System S on P.ResourceId = S.ResourceId

Where Name0 = 'MachineName'

SQL Query To Find What Users Are Using What SQL Application

This SQL Query will allow you to see which users are accessing or using SQL based applications on your SQL server except for the sa or System (NT Authority\System) accounts.

SQL Query:

Use Master

Select

SP.SpId 'Session ID',

SP.Nt_UserName UserName,

SD.Name DatbaseName,

SP.LogiName LoginId,

SP.Program_Name App

From Sys.SysProcesses SP

Join Sys.SysDatabases SD on SP.DbId = SD.DbId

Where SP.Nt_UserName Not In ('sa', 'System')

And (SP.loginame <> 'sa')

Order By SpId

SQL Query To Retrieve The Distribution Points For A Site Server

This SQL query will list or write all of the Distribution Points for the server on which the script is executed on.

Other Available Roles:

SMS Distribution Point

SMS Management Point

SMS Site Server

SMS Site System

SMS SQL Server

SMS Component Server

SMS Reporting Point

SMS Server Locator Point

SQL Query:

Select

ServerName 'Site Server Name',

SiteCode 'Site Code',

RoleName 'Role'

From v_SystemResourceList

Where RoleName = 'SMS Reporting Point'

SQL Query Example To Show Row Numbers When Executing A Query

Here you will find a SQL query example of how to add row numbers to the results set window for your SQL queries.

SQL Query:

Select Row_Number()

Over (Order by Name0) as 'Row Count',

Name0

From v_R_System

Order by Name0

SQL Query To Locate SMS Clients That Have Generated New SMSID's

Here you will find a SQL query to list SMS client resources that have generated new SMS ID's along with their Resource ID and the timestamp that the change was generated.

In SMS a new SMSID (SMS Unique ID) or GUID (Globally Unique Identifier) is created when any of the following are changed on your client resources:

SmBIOS Serial Number

The Machine's System Identifier (SID)

Any one of 10 different Hardware ID Properties

Additional Microsoft Information:

"The Hardware ID mechanism examines 10 computer properties and generates an ID of the Properties by combining hashes of the properties into a single ID. If a certain number of these properties change, the computer is considered, and a new SMSID is created. The number of properties is 3 out of 10 for a desktop computer and 2 of 7 for a laptop computer."

SQL Query:

Select Distinct

SYS.Netbios_Name0 'Machine Name',

Convert(VarChar(10),

SYS.SMS_UUID_Change_Date0, 101) 'Change Generated',

SYS.ResourceID ID

From v_R_System SYS

JOIN v_R_System NEW on SYS.SMS_Unique_Identifier0 = NEW.Previous_SMS_UUID0

Order By 'Machine Name'

List All Tables In A Specified Database

This SQL server script will display all of the SQL user type tables in a given database using DatabaseName and SqlQuery variables.

SQL Query:

Declare @DatabaseName SysName

Declare @SqlQuery VarChar(200)

-- Change SMS_XXX To The Database you wish to query

Set @DatabaseName='SMS_XXX'

Set @SqlQuery='Select Table_Name as Tables From ' + @DatabaseName + '.Information_Schema.Tables

Where Table_Type = ''Base Table'''

Exec (@SqlQuery)

Using A Select Case Statement To Substitute Existing Null Values

This SQL query will provide you with an example of how to change a query results NULL value to something more readable or understandable.

Replaces Null with Unknown:

Select

Name0,

Case

When User_Name0 is Null

Then 'Unknown'

Else User_Name0

End User_Name0

From V_R_System

Replaces Null With An Empty Field:

Select

Name0,

Case

When User_Name0 is Null

Then ''

Else User_Name0

End User_Name0

From V_R_System

SQL Query To Retrieve ConFigMgr Client Registration And Heartbeat Discovery Times

This SQL query will return the Client Registration and Heartbeat time stamps for your ConFigMgr 2007 resources.

SQL Query:

Select

CS.Name0 'Machine Name',

Convert(VarChar(10), AD.AgentTime, 101) 'Last Update',

AD.AgentName

From v_Gs_Computer_System CS

Join v_AgentDiscoveries AD on CS.ResourceID = AD.ResourceId

Where AD.AgentName In

('MP_ClientRegistration', 'Heartbeat Discovery')

SQL Query Get Server Name And Date Stamp

This SQL script is part of a By Request script that I was asked to help a reader with that would allow them to get the current date as well as the SQL server name for one of their SQL 2005 Reporting Services scripts.

SQL Query:

Select

@@ServerName 'Server Name',

'Date Stamp:' = Convert(VarChar(10), GetDate(),101)

SQL Query To Count Operating Systems In A Specified Collection

This SQL Query will count the total number of client machine resources by their Network Operating System (NOS) name that are in a specified collection.

Note: If you would rather not type in the SMS consoles collection name you can change the line that reads: Where Col.Name = 'All Windows NT Systems' to: Where COL.CollectionId = 'SMS000CS'.

SQL Query:

Select

SD.Operating_System_Name_and0 NOS,

Count (SD.Operating_System_Name_and0) 'Total',

COL.CollectionId,

COL.Name

From v_R_System SD

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

Where Col.Name = 'All Windows NT Systems'

Group By SD.Operating_System_Name_and0, COL.CollectionId, COL.Name

Order By SD.Operating_System_Name_and0, COL.CollectionId, COL.Name

List Tables With Primary Keys

Use the SQL script below to list all of your tables with Primary Keys present.

SQL Query:

Select Object_Name(Id) 'Tables With Primary Keys:'

From SysIndexes

Where (Status&2048)<>0

Order by Object_Name(id)

SQL Query To List SMS Resources From A Specified Subnet

Use the SQL script below to list all the SMS resources found on a specified subnet.

SQL Query:

Select

Netbios_Name0 'Machine Name',

IP_Addresses0 'Last IP Address'

From System_IP_Address_ARR

Join System_Disc MachineName

on System_IP_Address_ARR.ItemKey = MachineName.ItemKey

Where IP_Addresses0 Like '192.168.1.%'

And Netbios_Name0 is not Null

Order by Name0 Asc

SQL Query To Gather ConfigMgr Or SMS Site Server Information

This SQL query will list all of the Site servers (In the current database) as well as their Type (Primary or Secondary) , Site code, Site name and their Parent (ReportingSiteCode) site code to the active window

SQL Query:

Select

ServerName,

'Site Type' = Case

When Type = 1 then 'Secondary'

Else 'Primary'

End,

SiteCode,

SiteName,

ReportingSiteCode,

InstallDir

From v_Site

Order By ReportingSiteCode

SQL Query To Find All SQL 2000 And SQL 2005 Servers From The SMS Database

This SQL query will return all of the Microsoft SQL 2000 and 2005 servers in the SMS database.

SQL Query:

Select Distinct

SD.Name0,

SP.ProductName

From System_Disc SD

Join vSMS_G_System_SoftwareProduct SP

On SP.ClientId = SD.ItemKey

Where SP.ProductName = 'Microsoft SQL Server'

Or SP.ProductName

like 'Microsoft SQL 2005 Server%'

Count Number Of Machines With Specified Application Installed

This SQL server script will count the number of machines with a specified executable installed. Replace WinWord.Exe with the executable you want to get a machine count for and specify the file version: And SoftwareFile.FileVersion = 'X.X'

SQL Query:

Select Distinct

Count(SoftwareFile.FileName) as 'Total Count:'

From System_Disc

Join vSms_G_System_SoftwareFile as SoftwareFile

On SoftwareFile.ClientId = System_Disc.ItemKey

Where SoftwareFile.FileName = 'WinWord.Exe'

And SoftwareFile.FileVersion = 'X.X'

Using A SQL Query To See What Your Current SQL Logon Name Is

This simple little SQL query will allow you to see what user name you are currently logged on as on the SQL server from which the script is executed on.

SQL Query:

Select Original_Login() 'Logon Name'

--or

SELECT USER as [User],

SESSION_USER as [Session_User],

CURRENT_USER as [Current_User],

SYSTEM_USER as [System_User],

ORIGINAL_LOGIN() as [Original_Login()]

SQL Query To Find Table Dependencies

Use this SQL script to return the table dependencies such as stored procedures for your database objects.

You need to change the XType value referenced in the query below to find any of the objects in the following chart:

C = Check Vonstraint

D = Default or Default Constraint

F = Foreign Key Constraint

L = Log

Fn = Scalar Function

If = Inlined Table Function

P = Stored Procedure

Pk = Primary Key Constraint

Rf = Replication Filter Stored Procedure

S = System Table

Tf = Table Function

Tr = Trigger

U = User Table

Uq = Unique Constraint

V = View

SQL Query:

Select Distinct

SysObjects.Name 'Table Name',

Procedures.Name 'Stored Procedure'

From SysObjects

Join (SysObjects Procedures

Join SysDepends

on Procedures.Id = SysDepends.Id)

On SysDepends.DepId = SysObjects.Id

Where SysObjects.XType = 'U'

-- Change XType Values here using chart above

And Procedures.XType = 'P'

Group by SysObjects.Name,

SysObjects.Id,

Procedures.Name

Order by SysObjects.Name Asc

SQL Query To List Excluded Servers With RegRead

This SQL query will allow you to retrieve or list the machines in the excluded Servers list for your SMS server exclusions using the extended procedure RegRead.

SQL Query:

Exec Master..Xp_RegRead

'HKEY_LOCAL_MACHINE',

'Software\Microsoft\Sms\Components\Sms_Discovery_Data_Manager',

'ExcludeServers'

SQL Query To Find Specified Table Column Names

This script will allow you look for any specified column name from all of the user tables from within the current database object. This can be of particular interest to those wanting for example to find which SQL table holds the LastHWScan column.

SQL Query:

Set NoCount On

Declare @ColVar VarChar(25)

Set @ColVar = '%last%' -- String To Find

Select

SysObjects.Name 'Table Names:',

SysColumns.Name 'Column Names:'

From SysObjects, SysColumns, SysTypes

Where SysObjects.ID = SysColumns.ID

And SysColumns.xType = SysTypes.xType

And SysColumns.Name like @ColVar

Order by SysObjects.Name Asc

Set NoCount Off

Usage Examples:

Pattern Match

Set @ColVar = '%Last%'

Set @ColVar = '%last%time%'

Exact Match

Set @ColumnNames = 'Last'

Adding Machines To The ConfigMgr 2007 Exclusions List Using A SQL Script

This SQL query script will use the undocumented Extended Stored Procedure (Xp) RegAddMultiString to add a machine name to the ConfigMgr 2007 SMS Discovery Data Managers ExcludeServers list value. It will then use the Extended Stored Procedure RegRead to read the list of excluded machines and send the results to the active window.

Tip: This SQL Query will also work with SMS 2003 as long as Microsoft SQL Server 2000 or greater is installed as the backend database.

Note: This must be executed on the server that holds the exclusions list. To use the script change the line that reads MachineOne to the machine name you want to add.

SQL Query:

Exec Master..Xp_RegAddMultiString

'HKEY_LOCAL_MACHINE',

'Software\Microsoft\Sms\Components\Sms_Discovery_Data_Manager',

'ExcludeServers',

'MachineOne'

Exec Master..Xp_RegRead

'HKEY_LOCAL_MACHINE',

'Software\Microsoft\Sms\Components\Sms_Discovery_Data_Manager',

'ExcludeServers'

RegAddMultiString Syntax

The RegAddMultiString syntax is shown below:

Exec Master..Xp_RegAddMultiString

'Root Key',

'Key',

'Value Name',

'New String Value'

Retrieving All SMS Server Components

Use the SQL script below to List all of your SMS server components.

To use the script you must replace 'XXX' with your three letter site code.

Declare @SiteCode NVarChar(128)

Set @SiteCode = 'XXX'

Select

RoleName,

Servername

From SysResList

Where SiteCode = @SiteCode

And ResourceType = 'Windows NT Server'

Order by RoleName

SQL Query To Gather Linked Servers Information

Use the SQL script below to gather OLE DB data source Linked Server Information.

SQL Query:

Use Master

Select

SrvName 'Servers Name',

SrvProduct 'Product Name',

DataSource 'OLE Data Source Value'

From SysServers

Where ProviderName = 'Sqloledb'

And IsRemote = 1

Order by SrvName Desc

Find Last Database Backup Time Stamp

Use the SQL script below to find your last database backup time.

SQL Query:

Use Master

Select (SubString(Database_Name,1,32)) 'Database Name',

Backup_Finish_Date 'Last Backup Time Stamp'

From Msdb.Dbo.Backupset

Retrieve SQL Server Days On Line Uptime

Use the SQL script below to return the number of days the specified SQL server has been online or when the Tempdb has been up since it is recreated when the server is Rebooted or restarted.

SQL Query:

Select

Filename,

DateDiff(D, CrDate, GetDate()) 'Days On Line'

From SysDatabases

Where Name = 'TempDb'

SMS Client Machines With Less Than 300 MB Free Disk Space On Their Primary Partition

This SQL script will return all of the SMS client machine names and their last logged on user name where their primary C: partition has less than 300 MB of remaining free disk space.

SQL Script:

Select

SD.Name0 'Machine Name',

SD.User_Name0 'User Name',

LD.FreeSpace0 'Free Space'

From v_R_System SD

Join v_Gs_Logical_Disk LD on SD.ResourceId = LD.ResourceId

Where LD.DeviceId0 = 'C:'

And LD.FreeSpace0 < 300

And SD.Client0 = 1

Query To Report Table Owners

Use the SQL script below to get a list of all of the tables and the associated table owners from your SQL Database.

SQL Script:

Select

Table_Name as 'Table Name',

Table_Schema as 'Database Owner'

From Information_Schema.Tables

Where Table_Type = 'Base Table'

order by Table_Name Asc

Query Client Status And GUID Information

This SQL server script will display all of the machines within the 'All Systems' Collection and return the client status results along with the GUID assignment for each machine.

SQL Query:

Select

Name 'Machine Name',

Domain 'Domain Name',

Sitecode 'Site Code',

'Client' = Case

When IsClient = 1 Then 'YES'

Else '<N0>'

End,

'Assigned' = Case

When IsAssigned = 1 Then 'YES'

Else '<NO> '

End,

SmsId

From _Res_Coll_Sms00001 -- All Systems Collection

Where IsClient = 1

SQL Query To Find Views To Use In SMS Web Reports

Use the SQL script below to list all of the Views in your SMS Database for SMS Web Reports.

SQL Query:

Select

Name 'Procedure Name',

Id 'Procedure ID',

CrDate 'Creation Date'

From SysObjects

Where Name like '%v%'

And XType = 'V'

Find And Add To Query Template

This script can be used to find column data to add to your SQL queries. This simple script can be used to find or locate column names from your SQL server that matches your search criteria.

Once the query has been executed simply drag and drop or otherwise copy the result set information that you want into the SQL query window without having to type the table and column names in. The script is written to take the work out of syntax formatting.

SQL Query:

Select Table_Name + '.'+Column_Name + ',' Result_Set

From Information_Schema.Columns

Where Column_Name like '%Advertisement%'

SQL Query To Find Machines Within Specified IP Subnet Range

This SQL query will allow you to find machines in a specified Subnet range using a Like statement for the subnets.

The script will use a series of And statements to ensure that only those resources that are client machines and are not decommissioned or obsolete are retrieved. The script will also show you how to combine the User Domain name and the last logged on user name as one string.

The script will then go on to show you how to retrieve the machines active local network adapters IP address using additional And statements to ensure that empty (Null) IP addresses or loopback IP addresses are not retrieved.

SQL Query:

Select

SD.Name0 'Machine Name',

SD.User_Domain0 + '\' + SD.User_Name0 'Last Logged On User',

NIC.IpAddress0 'IP Address'

From v_R_System SD

Join v_Gs_Network_Adapter_Configur NIC on SD.ResourceId = NIC.ResourceId

Where SD.Client0 = 1

And SD.Decommissioned0 = 0

And SD.Obsolete0 = 0

And NIC.IPAddress0 Is Not NULL

And NIC.IPAddress0 <> '0.0.0.0'

And NIC.IPAddress0 Like '192.168.1.%'

Or NIC.IPAddress0 Like '192.168.2.%'

Or NIC.IPAddress0 Like '192.168.3.%'

Or NIC.IPAddress0 Like '192.168.4.%'

Or NIC.IPAddress0 Like '192.168.5.%'

Order By SD.Name0 , NIC.IPAddress0

Display Machines With A Specified Percentage Of Free Local Disk Space

This SQL server script will display machines with a specified percentage of free local disk free space available.

Select

SD.Name0 'Machine Name',

LD.DeviceID0 Drive,

LD.FileSystem0 'File System',

LD.Size0 'Total Drive Size',

LD.FreeSpace0 'Total Free Space',

LD.FreeSpace0 *100/ LD.Size0 'Percent Free'

From System_Disc SD, Logical_Disk_Data LD

Where SD.ItemKey = LD.MachineId

And LD.Description0 = 'Local Fixed Disk'

/* Change <xx Below To The Percentage Of Free Disk Space You Wish To Find.

Examples: <05 Will Return 5% Free And <10 Will Display 10% Free. */

And LD.FreeSpace0 *100/ LD.Size0 <20

Order By SD.Name0 Asc

SMS Web Report To Get Current Site System Status

This SQL query was developed to be used as a Web Report to provide site admins with a quick overview of the site status for the following SMS roles: Client Access Point, Component Server, Distribution Point, Management Point, Reporting Point, Server Locator Point, Site Server and SMS SQL Server.

Note: To use the SQL script as a web report copy and paste the Name, Category, Description and of course the SQL statement shown below to the appropriate new web report boxes or modify to your liking as needed.

Name: Get Current Site System Status

Category: SMS Site – General

Description: Displays current site system status information for: Client Access Point, Component Server, Distribution Point, Management Point, Reporting Point, Server Locator Point, Site Server and SMS SQL Server.

SQL Statement:

Select Distinct

SiteCode,

Role,

'Status' = Case

When Status = 0 Then 'OK'

When Status = 1 Then 'Warning'

When Status = 2 Then 'Critical'

Else ' '

End

From v_SiteSystemSummarizer

SQL Query To Return Microsoft Licensed Product Information For A Specified Machine

This SQL query can be easily adapted to be used as an SMS web report to allow you to see Microsoft Licensed Product information for a specified machine.

It will include the following information: Microsoft Family Name, Licensed Product Name, Version, License Type and the Last Update timestamp as in the example below:

Microsoft Family Name: Office Professional

Licensed Product Name: Office Professional 2003

Version: 2003

License Type: Microsoft Volume License

Last Update: 2007-04-20 12:35:31.007

SQL Query:

Select Distinct

LIC.MlsFamilyName 'Microsoft Family Name',

LIC.MlsProductName 'Licensed Product Name',

LIC.VersionCode Version,

LIC.LicenseTypeName 'License Type',

LIC.LastUpdated 'Last Update'

From v_R_System SD

Join v_Gs_Installed_Software_Ms SW On SD.ResourceID = SW.ResourceID

Join v_Lu_MsProd LIC On SW.MPC0 = LIC.MPC

Where SD.Name0 = 'Machine_Name'

Group By LIC.MlsFamilyName, LIC.MlsProductName,

LIC.VersionCode, LIC.LicenseTypeName, LIC.LastUpdated

Order By LIC.MlsFamilyName

SQL Query To Retrieve Free Disk Space For SMS Distribution Point Servers

This SQL query will allow you to quickly view the percentage of free disk space for your SMS Distribution Point (DP) servers.

SQL Query:

Select Distinct

SD.Name0,

SR.RoleName,

LD.DeviceID0,

LD.FreeSpace0 * 100 / LD.Size0 'Free Disk Space'

From v_R_System SD

Join v_Gs_Logical_Disk LD on SD.ResourceId = LD.ResourceId

Join v_SystemResourceList SR on SD.Netbios_Name0 = SR.ServerName

Where LD.DriveType0 = 3

and SR.RoleName = 'SMS Distribution Point'

SQL Query To Find Machines With A Specified Application Installed Using A Variable

This SQL query uses a column variable @ColVar much like my VBS scripts use an input dialog box where you can specify a file name to search for.

Note: Be sure to change File_Name.Exe with the file name you want to search for.

Tip: To enumerate machines that are installed to a specific site code add the additional And statement below at the end of the existing And statements where XXX is the three letter Site Code you want to enumerate:

And SC.SMS_Installed_Sites0 = 'XXX'

SQL Query:

Declare @ColVar VarChar(255)

Set @ColVar = 'File_Name.Exe'

Select Distinct

SD.Name0 'Machine Name',

SD.User_Domain0 + '\' + SD.User_Name0 'Last Logged On User',

AC.IpAddress0 'IP Address',

SC.SMS_Installed_Sites0 'Installed Site',

SF.FileName 'File Name',

SF.FileDescription Description,

SF.FileVersion Version,

SF.FilePath Location

From v_Gs_SoftwareFile SF

Join v_R_System SD on SF.ResourceId = SD.ResourceId

Join v_Ra_System_SmsInstalledSites SC on SD.ResourceId = SC.ResourceId

Join v_Gs_Network_Adapter_Configur AC on SD.ResourceId = AC.ResourceId

Where SF.FileName = @ColVar

And SD.Client0 = 1

And SD.Decommissioned0 = 0

And SD.Obsolete0 = 0

And AC.IPAddress0 Is Not NULL

And AC.IPAddress0 <> '0.0.0.0'

Order By SD.Name0

SMS SQL Queries To Use As Web Reports To Find Machines With A Static IP Address

Locating machines in your SMS database with a static assigned IP address is actually quite easily accomplished. The key is to find machines that are not using DHCP to retrieve their IP address. Once this has been specified you need to further specify that you want machines where the IP address is not empty or blank. If the Where DhcpEnabled0 = 0 and the And IpAddress0 Is Not NULL were not specified then you could potentially get results such as the one shown below:

Name0 = Machine1970 IPAddress0 = NULL

Name0 = Machine1970 IPAddress0 = 0.0.0.0

Name0 = Machine1970 IPAddress0 = 192.168.1.111

Basic SQL Query:

Select Distinct

Sys.Name0,

Nic.IPAddress0

From v_R_System Sys

Join v_Gs_Network_Adapter_Configur Nic

On Sys.ResourceID = Nic.ResourceId

Where DhcpEnabled0 = 0

And IpAddress0 Is Not NULL

Order By Name0

Server SQL Query:

Select Distinct

Sys.Name0,

Nic.IPAddress0,

Nos.Caption0

From v_R_System Sys

Join v_Gs_Network_Adapter_Configur Nic

On Sys.ResourceID = Nic.ResourceId

Join v_Gs_Operating_System Nos

On Sys.ResourceID = Nos.ResourceId

Where DhcpEnabled0 = 0

And IpAddress0 Is Not NULL

And Caption0 Like '%Server%'

Note: To find machines that are not servers yet have static IP addresses change the line that reads: And Caption0 Like '%Server%' To: And Caption0 Not Like '%Server%'

Retrieving Parent Child SMS Site Information

This query will return to you a brief overview of your SMS site hierarchy including child and parent relations as well as your current site status.

SQL Query:

Select

SiteServer 'Server Name',

SiteName 'Site Name',

SiteCode 'Site Code',

'Parent Site Code' = Case

When ReportToSite = '' Then '<None>'

Else ReportToSite

End,

Version 'Site Version',

'Site Type' = Case

When sitetype = 1 then 'Secondary'

Else 'Primary'

End,

'Current Status' = Case

When status = 1 then 'Active'

When status = 2 then 'Pending'

When status = 3 then 'Failed'

When status = 4 then 'Deleted'

Else 'Upgrade'

End

From Sites

SQL Query To Retrieve Basic Computer Hardware Information For A Specified Assigned Site Code

This SQL query will allow you to specify a site code (Assigned Site) and will return the following information for the resources that are assigned to the specified site as client resources:

Machine Name, User Name, Computer Manufacturer, Computer Type (i.e. Desktop or Laptop, etc), Asset Tag and Serial Number. If the asset tag or serial number is empty (Null) or contains the text 'No Asset Information' or 'Not Available' the results set will be blank.

SQL Query:

Select

SD.Name0 'Machine Name',

SD.User_Name0 'User Name',

SE.Manufacturer0 'Computer Manufacturer',

Case SE.ChassisTypes0

When 1 Then 'Other'

When 2 Then 'Unknown'

When 3 Then 'Desktop'

When 4 Then 'Low Profile Desktop'

When 5 Then 'PizzaBox'

When 6 Then 'Mini-Tower'

When 7 Then 'Tower'

When 8 Then 'Portable'

When 9 Then 'Laptop'

When 10 Then 'Notebook'

When 11 Then 'Handheld Device'

When 12 Then 'Docking Station'

When 13 Then 'All-In-One'

When 14 Then 'Sub-Notebook'

When 15 Then 'Space Saving'

When 16 Then 'Lunch Box'

When 17 Then 'Main System Chassis'

When 18 Then 'Expansion Chassis'

When 19 Then 'Sub-Chassis'

When 20 Then 'Bus Expansion Chassis'

When 21 Then 'Peripheral Chassis'

When 22 Then 'Storage Chassis'

When 23 Then 'Rack-Mount Chassis'

When 24 Then 'Sealed PC'

Else 'Unknown'

End 'Computer Type',

'Asset Tag' = Case

When SE.SmBiosAssetTag0 IS NULL Then ''

When SE.SmBiosAssetTag0 = 'No Asset Information' Then ''

Else SE.SmBiosAssetTag0

End,

'Serial Number' = Case

When SE.SerialNumber0 IS NULL Then ''

When SE.SerialNumber0 = 'Not Available' Then ''

Else SE.SerialNumber0

End

From v_Gs_System_Enclosure SE

Join v_R_System SD on SE.ResourceId = SD.ResourceId

Join v_RA_System_SMSAssignedSites SC on SD.ResourceID = SC.ResourceID

Where SD.Client0 = 1

And SC.SMS_Assigned_Sites0 = 'XXX'

Order By SD.Name0

SMS SQL Query To Use As A Web Report To Retrieve Machines Operating System Information

This SQL query can be used as a web report to allow you to view all the machines in the SMS database and their operating system information in a readable format where the Operating system (Caption) and Service Pack (SP) level (Csd Version) are shown as one string as shown below:

Machine Name	Operating System
Machine1	Microsoft Windows XP Professional Service Pack 1
Machine2	Microsoft Windows XP Professional Service Pack 2
Machine3	Microsoft(R) Windows(R) Server 2003, Standard Edition Service Pack 2
Machine4	Microsoft(R) Windows(R) Server 2003, Enterprise Edition Service Pack 2

SQL Query:

Select Distinct

Sys.Name0 'Machine Name',

Nos.Caption0 + Space(1) + Nos.CsdVersion0 'Operating System'

From v_R_System Sys

Join v_Gs_Operating_System Nos

On Sys.ResourceId = Nos.ResourceId

Order By 'Machine Name'

Component Status Information For A Specified Site

This SQL query much like its counterpart in the SMS console will provide you with the component status for you site.

SQL Query:

Select

Summarizer_Components.SiteCode Site,

Summarizer_Components.MachineName 'Server Name',

Summarizer_Components.ComponentName Component,

Case Summarizer_Components.Status

When 0 Then 'OK'

When 1 Then 'Stopped'

When 2 Then 'Critical'

When 3 Then 'Unknown'

Else 'Unknown'

End As 'Current Status',

Case Summarizer_Components.State

When 0 Then 'Stopped'

When 1 Then 'Started'

When 2 Then 'Paused'

When 3 Then 'Installing'

When 4 Then 'Re-Installing'

When 5 Then 'De-Installing'

Else 'Unknown'

End As 'Current State',

Case Summarizer_Components.Type

When 0 Then 'AutoStarting'

When 1 Then 'Scheduled'

When 2 Then 'Manual'

Else 'Unknown'

End As 'StartUp Type'

From summarizer_components

-- Change xxx to the Three letter site code you wish to query.

Where Summarizer_Components.SiteCode = 'XXX'

Inserting Excel Spreadsheet Data Into An Existing SQL Database Table

This SQL script will provide you with an example of how to copy data from an Excel spreadsheet to an existing database table that you have specified. It is important to note here that the script needs to be executed from within the database where the Table_Name exists.

Notes: Change the C:\File_Name.xls to the local drive letter on your SQL server or specify the Server\Share UNC path to the spreadsheet. If the data is not on sheet 1 change Sheet1$ to the worksheet name you wish to use.

SQL Script:

Insert into Table_Name

Select *

From OpenRowSet

('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);

DBQ=C:\File_Name.xls',

'Select * From [Sheet1$]')

Published Sunday, July 15, 2007 8:14 AM by dhite

Filed under: SQL Queries

Retrieving SMS Site Boundaries Or Roaming Boundaries IP Subnet Information

The SQL scripts that follow will allow you to read your SMS site(s) boundaries defined IP subnets or the roaming site boundaries defined IP subnets for the server from which they are executed on.

Note: If you execute these scripts from an SMS Primary Parent site sever there is no need to execute them on child sites as the Parent sites database holds the information for the entire site.

Site Boundaries SQL Script:

Select

SiteCode,

IpSubnet

From SiteBoundaryIpSubnet

Order by SiteCode

Roaming Boundaries SQL Script:

Select

SiteCode,

IpSubnet

From RoamingBoundaryIpSubnet

Order by SiteCode

Reading An Excel Spreadsheet In A SQL Query Window

This SQL script will provide you with an example of how to read data from an Excel spreadsheet from with the SQL query window.

Notes: Change the C:\File_Name.xls to the local drive letter on your SQL server or specify the Server\Share UNC path to the spreadsheet. If the data that yu want to read is not on sheet 1 change Sheet1$ to the worksheet name you wish to use.

SQL Script:

Select *

From OpenRowSet

('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);

DBQ=C:\File_Name.xls',

'Select * From [Sheet1$]')

SQL Query To Retrieve A Machines Site Code

This SQL query will return the site code of a specified machine name from the all systems collection.

SQL Query:

Select

SYS.Name0 'Machine Name',

COL.SiteCode 'Site Code'

From v_R_System SYS

Join v_FullCollectionMembership COL on SYS.ResourceId = COL.ResourceId

Where COL.CollectionID = 'SMS00001'

And SYS.Name0 = 'Machine_Name'

SQL Query To Count Computer System Manufacturer And Models

This SQL script will count the Manufacturer and Models for the resources in your SMS database for reporting purposes.

SQL Query:

Select

Manufacturer00 Manufacturer,

Model0 Model,

Count (Model0) Counts

From Computer_System_Data

Group By Model0, Manufacturer00

Order By Manufacturer00

SMS Site Boundary Subnet Queries

Here you will find queries that will list all of the resources that are currently listed on your SMS server's specified subnet. There are two ways in which you can retrieve this information. One is using the "Like" statement for the IP addresses first 3 octets from the IP addresses view. The other uses the IP subnet from the IP subnets view.

IP Addresses View:

Select

SYS.Name0 'Machine Name',

IP.Ip_Addresses0 'IP Address'

From v_R_System SYS

Inner Join v_Ra_System_IpAddresses IP

On SYS.ResourceId = IP.ResourceId

Where IP.Ip_Addresses0 Like '192.168.1.%'

IP Subnets View:

Select

SYS.Name0 'Machine Name',

SN.Ip_Subnets0

From v_R_System SYS

Inner Join v_Ra_System_IpSubnets SN

On SYS.ResourceId = SN.ResourceId

Where SN.Ip_Subnets0 = '192.168.1.0'

SQL Query To Locate The Machine Name For A Specified IP Address

This SQL query will allow you to enter an IP address and return the Machine name, last logged on user name and the machines IP address for the IP address specified.

Note: Change xxx.xxx.xxx.xxx To the IP address you wish to return the information from.

SQL Script:

Select

SD.Name0 'Machine Name',

SD.User_Name0 'User Name',

IP.IP_Addresses0 'IP Address'

From System_Disc SD

Join System_Ip_Address_Arr IP on SD.ItemKey = IP.ItemKey

Where IP.IP_Addresses0 = 'xxx.xxx.xxx.xxx'

SQL Query To Locate The IP Address For A Specified Machine Name

This SQL query will allow you to enter a machine name and return the IP address, last logged on user name and the machines name for the machine specified.

Note: Change xxx to the machine name you wish to return the information from.

SQL Script:

Select

IP.IP_Addresses0 'IP Address',

SD.User_Name0 'User Name',

SD.Name0 'Machine Name'

From System_Disc SD

Join System_Ip_Address_Arr IP on SD.ItemKey = IP.ItemKey

Where SD.Name0 = 'xxx'

SQL Query To Retrieve The Most Active SQL Tables

Use the SQL script below to find the most active SQL Tables by retrieving table changes.

SQL Query:

DBCC UpdateUsage(0)

With No_Infomsgs

Select SObject.Name as TableName,

SIndex.RowModCtr as TableUpdates

From SysIndexes SIndex

Join SysObjects SObject on SIndex.Id = SObject.Id

Where SObject.Type='U'

And RowModCtr Not Like '0'

And SIndex.IndId In (0,1)

Order by SIndex.RowModCtr Desc

Display Advertisement Package Status

This SQL server script will display the advertisement status for a particular advertisement for each machine contained within the advertised collection.

SQL Query:

Select Distinct

StatusMessages.MachineName 'Machine Name',

'Advertisement Status' = Case

When ID&0x0000FFFF = 3900 Then '<Distribution Server>'

When ID&0x0000FFFF = 30006 Then '<Console Advertised From>'

When ID&0x0000FFFF = 10000 Then 'Check OFR'

When ID&0x0000FFFF = 10001 Then 'Check PKG'

When ID&0x0000FFFF = 10002 Then 'Received'

When ID&0x0000FFFF = 10003 Then 'CmdLine Not Found'

When ID&0x0000FFFF = 10004 Then 'Invalid Exe Platform'

When ID&0x0000FFFF = 10005 Then 'Started'

When ID&0x0000FFFF = 10006 Then 'Failed'

When ID&0x0000FFFF = 10007 Then 'Failed'

When ID&0x0000FFFF = 10008 Then 'Complete'

When ID&0x0000FFFF = 10009 Then 'Successful'

When ID&0x0000FFFF = 10010 Then 'No Uninstall Found'

When ID&0x0000FFFF = 10011 Then 'Uninstall Exe Not Found'

When ID&0x0000FFFF = 10012 Then 'Uninstall Started'

When ID&0x0000FFFF = 10013 Then 'Uninstall Failed'

When ID&0x0000FFFF = 10014 Then 'Uninstall Failed'

When ID&0x0000FFFF = 10015 Then 'Uninstall Failed'

When ID&0x0000FFFF = 10016 Then 'Uninstall Success'

When ID&0x0000FFFF = 10017 Then 'Removal Key Not Found'

When ID&0x0000FFFF = 10018 Then 'Rejected By OS'

When ID&0x0000FFFF = 10019 Then 'Advertisement Expired'

When ID&0x0000FFFF = 10020 Then 'Slow Network'

When ID&0x0000FFFF = 10021 Then 'Machine Was Restarted'

Else '<No Status !>'

End

From StatusMessages

Join StatusMessageInsStrs on StatusMessages.RecordID = StatusMessageInsStrs.RecordID

Join StatusMessageAttributes on StatusMessages.RecordID = StatusMessageAttributes.RecordID

Where StatusMessageAttributes.AttributeID = 401

And StatusMessageAttributes.AttributeValue = 'XXX' -- Your Advertisement ID

And StatusMessages.SiteCode = 'XXX' -- Your Three Letter Site Code

Order by StatusMessages.MachineName Desc

Retrieving SMS Schema Information View Particulars

This query will show you all of the attributes for your SMS inventory groups.

Select ResourceType, GroupID, AttributeName, ColumnName, MaxColWidth, ValueType From v_GroupAttributeMap

This query will show you all of the Inventory groups by their architecture.

Select ResourceType, GroupID, DisplayName, InvClassName, InvHistoryClassName, MIFClass From v_GroupMap

This query will show you all of the classes and properties for the SMS report View

Select ViewName, ViewColumnName, IsStringType From v_ReportViewSchema

This query will show you all of the Attributes for each resource type.

Select ResourceType, PropertyDisplayName, ColumnName, ArrayTableName, MaxColWidth From v_ResourceAttributeMap

This query will show you all of the Resource Types available to the SMS database.

Select ResourceType, DisplayName, ResourceClassName From v_ResourceMap

This query will show you all of the Views contained in the SMS Schema.

Select Type, ViewName From v_SchemaViews

Machine ID To Machine Name

This query will return to you the NetBIOS name for each MachineID in your SMS site. This can be helpful when using the resource explorer to quickly and easily find the machines host name.

SQL Query:

Select

MachineID, Name as 'Machine Name'

From _Res_Coll_SMS00001

Where _Res_Coll_Sms00001.ArchitectureKey = 5

Order by Name ASC

Retrieve SQL Logins Information

Use the SQL script below to Return SQL Server SysLogins information from the Master table in a readable format for quick review.

SQL Query:

Select

LoginName 'Login ID Name',

DbName 'Default Database',

language 'Default Language',

'Account Type' = Case

When IsntName = 1 Then 'NT Account'

Else 'SQL Login'

End,

'Group Account' = Case

When IsntGroup = 1 Then 'Yes'

Else ' '

End,

'User Account' = Case

When IsntUser = 1 Then 'Yes'

Else ' '

End,

'SysAdmin Member' = Case

When SysAdmin = 1 Then 'X'

Else ' '

End,

'SecurityAdmin Member' = Case

When SecurityAdmin = 1 Then 'X'

Else ' '

End,

'ServerAdmin Member' = Case

When ServerAdmin = 1 Then 'X'

Else ' '

End,

'SetupAdmin Member' = Case

When SetupAdmin = 1 Then 'X'

Else ' '

End,

'ProcessAdmin Member' = Case

When ProcessAdmin = 1 Then 'X'

Else ' '

End,

'DiskAdmin Member' = Case

When DiskAdmin = 1 Then 'X'

Else ' '

End,

'DbCreator Member' = Case

When DbCreator = 1 Then 'X'

Else ' '

End

From Master..SysLogins

Query Name By Query ID View

This SQL server script will create a SQL view that lists all of the SMS administrator console queries that you have created. The view can serve as a quick reference for you when troubleshooting your SMS advertisements. The result set can also be saved to disk or printed to quickly and easily locate all of your console queries.

SQL Query:

If Exists (Select Table_Name From Information_Schema.Views

Where table_name = 'SMS_Query_Information')

Drop View SMS_Query_Information

Create View SMS_Query_Information As

Select

Convert(char(10),QueryKey) as 'Query ID',

Convert(char(75),Name) as 'Query Name',

Comments As Comments

From Queries

Where QueryType = 3

---Once you have the View created you can quickly view all of your created SMS ------------Administrator console queries by running the following from within the SQL query -----------------analyzer:

Select * From SMS_Query_Information

Converting SQL Table SMS Timestamps To Readable Formats For SQL Queries

This post will provide you with an example of how to convert SMS Coordinated Universal Time (UTC) time stamps to a readable format as opposed to the default UTC time output of yyyy-mm-dd hh:mm:ss.nnn.

The queries below will return the machine name, last hardware and last software inventory times for all of the resources in the System Discovery table. The Date SQL query will return just the Date stamp information and the Date-Time SQL query will return the Date and Time stamps in a 12 hour format to return AM or PM.

Note: For more information on the available Universal Time Conversion Codes for your SQL queries see the Universal Time Conversion Codes Chart at the end of this post.

Date SQL Query:

Select

SD.Name0 'Machine Name',

Convert(VarChar(10), WD.LastHwScan, 105) 'Last Hardware Scan Date',

Convert(VarChar(10), SIS.LastUpdateDate, 101) 'Last Software Scan Date'

From System_Disc SD

Join WorkstationStatus_Data WD on SD.ItemKey = WD.MachineID

Join SoftwareInventoryStatus SIS on SD.ItemKey = SIS.ClientId

Date-Time SQL Query:

Select

SD.Name0 'Machine Name',

Convert(VarChar(30), WD.LastHwScan, 109) 'Last Hardware Scan Date',

Convert(VarChar(30), SIS.LastUpdateDate, 131) 'Last Software Scan Date'

From System_Disc SD

Join WorkstationStatus_Data WD on SD.ItemKey = WD.MachineID

Join SoftwareInventoryStatus SIS on SD.ItemKey = SIS.ClientId

Universal Time Conversion Codes Chart

Code	Output
100	mon dd yyyy hh:mmAM (PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	mon dd, yy
108	hh:mm:ss
109	mon dd yyyy hh:mm:ss.nnnAM ( PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113	dd mon yyyy hh:mm:ss:nnn (24 hour)
114	hh:mm:ss:nnn (24 hour)
120	Yyyy-mm-dd hh:mm:ss (24 hour)
121	Yyyy-mm-dd hh:mm:ss.nnn (24 hour)
126	Yyyy-mm-ddThh:mm:ss.nnn (24 hour)
130	dd mon yyyy hh:mm:ss:mmmAM (PM)
131	dd/mm/yy hh:mm:ss:mmmAM (PM)

Tracking Advertisements That Have Been Received But Not Started

This SQL query will provide you with an example that uses a SubSelect method to let you monitor all of the machines that have received a specified advertisement but have not yet stated the installation. The value added by this query is that it is good for advertisement tracking purposes.

To use this query you must Replace xxx With The Advertisement that You Wish To Track From Advertisements > Advertisement ID in the SMS console.

SQL Query:

Declare @ProgId NVarChar(128)

Set @ProgId = 'XXX'

Select

MachineName 'Received, Not Started'

From vStatusMessages Astat

Join StatusMessageAttributes att1

on Astat.RecordID = att1.RecordID

Where AttributeValue = @ProgId

And MessageID = 10002 -- Received Status Code

And MessageID <> 10003 -- Failures Status Code

And MessageID <> 10004 -- Failures Status Code

And MessageID <> 10021 -- Failures Status Code

And ModuleName = 'Sms Client'

And MachineName not in

(Select all MachineName

From vStatusMessages Astat

Join StatusMessageAttributes As att1

On Astat.RecordID = att1.RecordID

Where AttributeValue = @ProgId

And MessageID = 10005 -- Started Status Code

And ModuleName = 'SMS Client')

List Excluded Servers From The Discovery Data Manager Registry Key

The SQL server script below will retrieve the machine names contained in the Hkey_Local_Machine\Software\Microsoft\SMS\Components\Sms_Discovery_Data_Manager\ExcludeServers registry key

SQL Query:

Use Master

-- ### Delete Temporary Table If It Already Exist ###

If Exists (Select * From dbo.SysObjects where id = Object_Id(N'[#ExcludedDevices]')

And ObjectProperty(Id, N'IsUserTable') = 1)

Drop Table [#ExcludedDevices]

-- ### Create Temporary Table ###

Create Table #ExcludedDevices

(Value Nvarchar(255),

Data Nvarchar(255))

-- ### Insert registry information into the Temporary Table ###

Insert #ExcludedDevices

-- ### Execute Xp_RegRead to read the registry ###

Exec Master.dbo.Xp_RegRead

-- ### Registry key to enumerate ###

'HKEY_LOCAL_MACHINE', -- Root Key

'SOFTWARE\Microsoft\SMS\Components\SMS_DISCOVERY_DATA_MANAGER\', -- Key Path

'ExcludeServers', -- Key Value

'Values'

-- ### Counts the number of Excluded Devices in the registry key ###

Select Count(Data) As 'Total Excluded Devices : ' From #ExcludedDevices

Print ''

-- ### Presents the regisrty key values (Machines)###

Select

Convert(Char(25), Data) As 'Excluded Device Names'

From #ExcludedDevices

Order By Data Asc

Set NoCount Off

-- ### Delete the Temporary Table ###

Drop Table #ExcludedDevices

Finding Machines Last Hardware Inventory Scan With Additional Machine Info

The SQL query below will return the Machine Name, Model, Serial Number, Number Of Processors, Operating System Name, OS Version and the Last Hardware Scan timestamp.

SQL Query:

Select

SD.Name0,

CS.Model0,

SE.SerialNumber00,

CS.NumberOfProcessors00,

SD.Operating_System_Name_and0,

OS.Version0,

WD.LastHwScan

From System_Disc SD

Join Operating_System_Data OS on OS.MachineID = SD.ItemKey

Join Computer_System_Data CS on CS.MachineID = SD.ItemKey

Join System_Enclosure_Data SE on SE.MachineID = SD.ItemKey

Join WorkstationStatus_Data WD on WD.MachineID = SD.ItemKey

Query SMS 2.0 And SMS 2003 Sites Current Service Pack Level

Use the SQL script below to find your SMS sites service pack version level for SMS 2.0 or SMS 2003 sites.

SQL Query:

Select

SiteCode,

SiteServer,

SiteName,

'Version' = Case

When Version between '2.00.1239.0000' And '2.00.1239.0999'

Then 'SMS Version 2.0 RTM'

When Version between '2.00.1380.1000' And '2.00.1380.1999'

Then 'SMS Version 2.0 Service Pack 1'

When Version between '2.00.1493.2000' And '2.00.1493.2999'

Then 'SMS Version 2.0 Service Pack 2'

When Version between '2.00.1493.3000' And '2.00.1493.3999'

Then 'SMS Version 2.0 Service Pack 3'

When Version between '2.00.1493.4000' And '2.00.1493.4999'

Then 'SMS Version 2.0 Service Pack 4'

When Version between '2.00.1493.5000' And '2.00.1493.5999'

Then 'SMS Version 2.0 Service Pack 5'

When Version = '2.50.2726.0018'

Then 'SMS 2003 RTM'

When Version = '2.50.3174.1018'

Then 'SMS 2003 SP 1'

When Version = '2.50.4160.2000'

Then 'SMS 2003 SP 2'

Else 'Unable To Determine Service Pack!'

End

From Sites

Using SQL To Generate Random Passwords

Here you will find two queries that will allow you to make or create passwords that can be used either for temporary assignment or even to create permanent passwords.

SQL Query #1

Select

lower(char(65 + Right(Convert(varchar,rand()),1) +

Right(Convert(varchar,rand()),1)) +

char(65 + Right(Convert(varchar,rand()),1) +

Right(Convert(varchar,rand()),1)) +

char(65 + Right(Convert(varchar,rand()),1) +

Right(Convert(varchar,rand()),1) +

Right(Convert(varchar,rand()),1)) +

Right(Convert(varchar,rand()),2) +

char(65 + Right(Convert(varchar,rand()),1)+

Right(Convert(varchar,rand()),1) +

Right(Convert(varchar,rand()),1)) +

char(65 + Right(Convert(varchar,rand()),1)+

Right(Convert(varchar,rand()),1) +

Right(Convert(varchar,rand()),1)) +

char(65 + Right(Convert(varchar,rand()),1)+

Right(Convert(varchar,rand()),1) +

Right(Convert(varchar,rand()),1))) as 'New Password'

SQL Query #2

Select

Case Floor(Rand()*3)

When 0 Then Char(48 + Floor(Rand()*10))

When 1 Then Char(65 + Floor(Rand()*26))