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, Category From v_Report   Where Category = 'Asset Intelligence' Order By Name   Use this SQL Query to locate all of the available Report Categories for use with the query above:   Select Distinct Category From V_Report Order By Category

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 AuthoritySystem) 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', 'SoftwareMicrosoftSmsComponentsSms_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', 'SoftwareMicrosoftSmsComponentsSms_Discovery_Data_Manager', 'ExcludeServers', 'MachineOne'   Exec Master..Xp_RegRead 'HKEY_LOCAL_MACHINE', 'SoftwareMicrosoftSmsComponentsSms_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.   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.   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 ServerShare 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 ServerShare 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 Go   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 Go   —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_MachineSoftwareMicrosoftSMSComponentsSms_Discovery_Data_ManagerExcludeServers registry key   SQL Query:   Use Master Go   — ### 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] Go   — ### 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 'SOFTWAREMicrosoftSMSComponentsSMS_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 '' Go   — ### 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 Go

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()),1)) + Right(Convert(varchar,rand()),2) + char(65 + Right(Convert(varchar,rand()),1)+ 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) + 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()),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)) Else Char(97 + Floor(Rand()*26)) End + Case Floor(Rand()*3) When 0 Then Char(48 + Floor(Rand()*10)) When 1 Then Char(65 + Floor(Rand()*26)) Else Char(97 + Floor(Rand()*26)) End +   Case Floor(Rand()*3) When 0 Then Char(48 + Floor(Rand()*10)) When 1 Then Char(65 + Floor(Rand()*26)) Else Char(97 + Floor(Rand()*26)) End +   Case Floor(Rand()*3) When 0 Then Char(48 + Floor(Rand()*10)) When 1 Then Char(65 + Floor(Rand()*26)) Else Char(97 + Floor(Rand()*26)) End +   Case Floor(Rand()*3) When 0 Then Char(48 + Floor(Rand()*10)) When 1 Then Char(65 + Floor(Rand()*26)) Else Char(97 + Floor(Rand()*26)) End +   Case Floor(Rand()*3) When 0 Then Char(48 + Floor(Rand()*10)) When 1 Then Char(65 + Floor(Rand()*26)) Else Char(97 + Floor(Rand()*26)) End As 'New Password'

Query SMS Client Machines and Return Their Client Type And Client Version

  This SQL query will enumerate all of your SMS client machines and return their client type and client version.   To see all of the resources remark the following line out Where Client0 = 1 as:   –Where Client0 = 1   This will return all of the SMS discovered resources to allow you to address those machines marked as Device Client(s) or do not have the client installed or even have a version that is not recognized.   SQL Query:   Select Name0 'Machine Name',   'Client Type' = Case When Client_Type0 = 0 Then 'Legacy' When Client_Type0 = 1 Then 'Advanced' Else 'Device Client' End,   'Client Version' = Case   — SMS 2.0 Client Versions When Client_Version0 between '2.00.1239.0000' And '2.00.1239.0999' Then 'SMS 2.0 RTM' When Client_Version0 between '2.00.1380.1000' And '2.00.1380.1999' Then 'SMS 2.0 Service Pack 1' When Client_Version0 between '2.00.1493.2000' And '2.00.1493.2999' Then 'SMS 2.0 Service Pack 2' When Client_Version0 between '2.00.1493.3000' And '2.00.1493.3999' Then 'SMS 2.0 Service Pack 3' When Client_Version0 between '2.00.1493.4000' And '2.00.1493.4999' Then 'SMS 2.0 Service Pack 4' When Client_Version0 between '2.00.1493.5000' And '2.00.1493.5999' Then 'SMS 2.0 Service Pack 5'   — SMS 2003 Client Versions When Client_Version0 = '2.50.2726.0018' Then 'SMS 2003 RTM' When Client_Version0 = '2.50.3174.1018' Then 'SMS 2003 Service Pack 1' When Client_Version0 = '2.50.4160.2000' Then 'SMS 2003 Service Pack 2' Else 'Unable To Determine Service Pack!' End   From System_Disc Where Client0 = 1 Order by Client_Version0, Name0 Asc

Count SMS Installed Resources

  This SQL query will return the count of SMS resources installed to your site server(s). It will also provide you with the Site server name, its site code, parent site and SMS site version.   SQL Query:   Select  SS.ServerName 'Site Server', SS.SiteCode 'Site Code', SS.ReportingSiteCode Parent, SS.Version Version, Count(SC.SMS_Installed_Sites0) Assigned From v_Site SS Join v_RA_System_SMSInstalledSites SC on SS.SiteCode = SC.SMS_Installed_Sites0 Group by SS.SiteCode, SS.ServerName, SS.ReportingSiteCode, SS.Version, SC.SMS_Installed_Sites0 Order by SS.SiteCode

SQL Query To Get A Specified Machines Manufacturer, Model And Processor Count

  This rather simple SQL query will allow you to gather the manufacturer, model and Processor Count for a specified machine.   SQL Query:   Select Name0 'Machine Name', Manufacturer00 Manufacturer, Model0 Model, NumberOfProcessors00 Processors From Computer_System_Data Where Name0 = 'MachineName'  

SQL Query To Get Client Machines Hardware Scan Age In Days

  This SQL query will return the last hardware scan in days for all machines in the SMS database.   SQL Query:   Select   SYS.Name0 'Machine Name', DateDiff(D, WKS.LastHwScan, GetDate()) 'Last Hardware Scan Age' From v_Gs_Workstation_Status WKS Join v_R_System SYS on WKS.ResourceId = SYS.ResourceId  

SQL Query To Retrieve Machines With A Specified Application Version

  This SQL query will search Add and Remove Programs and return the machines and last logged on user name that has a specified application (Display Name) and Version installed.   SQL Query:   Select Distinct v_R_System.Name0 'Machine Name', v_R_System.User_Name0 'User Name', v_Gs_Add_Remove_Programs.DisplayName0 'Display Name', v_Gs_Add_Remove_Programs.Version0 Version   From v_R_System Join v_Gs_Add_Remove_Programs on v_R_System.ResourceID = v_GS_Add_Remove_Programs.ResourceID   Where v_Gs_Add_Remove_Programs.DisplayName0 Like 'Microsoft .NET Framework 2.0' Order By Name0     Note: You can also change the Where statement to use a variable that will allow you to use the query as a web report. This can be accomplished by changing the line to:   Where v_Gs_Add_Remove_Programs.DisplayName0 Like @DisplayName  

SQL Query To Find Machines With USB Disk Drives

  This SQL query will return all of the machines that have USB Disk Drives along with the last logged on user name.   SQL Query:   Select   CS.Name0, CS.UserName0, HD.DeviceID0, HD.InterfaceType0 From v_Gs_Computer_System CS Join v_Gs_Disk HD on CS.ResourceID = HD.ResourceID Where HD.InterfaceType0 = 'USB' Order By HD.DeviceID0   If you have no need for the last logged on user name you can use this slightly simplified version:   Select SystemName0, DeviceID0, InterfaceType0 From v_Gs_Disk HD Where InterfaceType0 = 'USB' Order By DeviceID0

Locating A Machine Name And Logon User Name For A Specified Serial Number

  The query below will return the machine name and the last logged on user name for a specified hardware serial number.   Note: Replace XYZ123ABC with the serial number that you wish to search for.   SQL Query:   Select  SD.Name0, SD.User_Name0, PB.SerialNumber00 From System_DISC SD Join Pc_Bios_Data PB on PB.MachineId = SD.ItemKey Where PB.SerialNumber00 = 'XYZ1234ABC'  

Advertisement Status SQL Query

  When you select “Advertisement Status” from the “System Status” leaf of the SMS console you are presented with an overview of all the current advertisements for your site(s). This SQL query will provide you with the same results as shown in the advertisement status leaf.   Note: The ScheduleToken is set to “Since Advertised”.   SQL Query:   Select OfferName Name, Recieved, Failures, ProgramsStarted 'Programs Started', ProgramsFailure 'Program Errors', ProgramsSuccess 'Program Success', ProgramsFailureMIF 'Program Errors', ProgramsSuccessMIF 'Program Success (MIF)', PkgName Package, PkgProgram Program, CollectionName 'Target Collection', PresentTime 'Available After', ExpirationTime 'Expires After', OfferID 'Advertisement ID'   From vOfferStatusSummarizerRoot Where ScheduleToken = '0001128000080008' Order by OfferName

Locating Machines That Performed A Hardware Scan Within The Last Week

  This SQL query will locate machines in your SMS inventory that have reported a hardware scan in the last week or 7 days.   Sql Query:   Select     CS.Name0 'Machine Name', WS.LastHwScan 'Inventory TimeStamp', DateDiff(D, WS.LastHwScan, GetDate()) 'Inventory Age In Days:)' From WorkstationStatus_Data WS Join System_Disc CS on WS.MachineId = CS.ItemKey Where (DateDiff(D, WS.LastHwScan, GetDate()) >= 7)

Decommissioned DDR Processing Query

  SMS 2003 SP2 adds the ability to send notification of client record deletion to the parent site and then propagate that notification up the hierarchy.   You can use the SQL query below to locate or find machines in your SMS database that have their Decomissioned DDR set.   SQL Script:   Select   SD.Netbios_Name0 Name, SD.User_Name0 'Last Logon User Name',   'Decommissioned DDR Set' = Case When SD.Decommissioned0 = 1 Then 'YES' Else 'NO' End   From System_Disc SD   Where SD.Decommissioned0 = 1  

Return Machine Information Where A Particular Application Is Installed

This SQL query will return the following information Machine Name, Model, Last Hardware Scan, Operating System Name, OS Version, Number of Installed Processors, Software File Name and File Version where a specified application is installed.   SQL Query:   Select SD.Name0, CS.Model0, WD.LastHwScan, SD.Operating_System_Name_and0, OS.Version0, CS.NumberOfProcessors00, SF.FileName, SF.FileVersion From SoftwareInventory   Join SoftwareFile SF on SoftwareInventory.ProductId = SF.ProductId Join Computer_System_Data CS Join System_Disc SD on CS.MachineID = SD.ItemKey Join WorkstationStatus_Data WD on SD.ItemKey = WD.MachineID Join Operating_System_Data OS on CS.MachineID = OS.MachineID On SoftwareInventory.ClientId = SD.ItemKey   Where SF.FileName = 'FileName.Exe'   Group by SD.Name0, CS.Model0, WD.LastHwScan, SD.Operating_System_Name_and0, OS.Version0, CS.NumberOfProcessors00, SF.FileName, SF.FileVersion

Locating Machines Not Reporting Heartbeat Discovery In The Last Week

  This SQL query will allow you to find or locate machines in your SMS database that have not reported a Heartbeat discovery record in the last week or seven days.   SQL Query:   Select SD.Netbios_Name0, A.AgentName, DI.AgentTime   From System_Disc SD   Inner Join DiscItemAgents DI on SD.DiscArchKey = DI.DiscArchKey Cross Join Agents A   Where A.AgentName = 'Heartbeat Discovery' And DatePart (D,DI.AgentTime) >= 7

SQL Query To Find Obsolete Machines

Use this SQL query to find machines in your SMS database that are Obsolete along with information about the obsolete client machine(s)   SQL Query:   Select SD.Netbios_Name0 Name,   'Obsolete' = Case When SD.Obsolete0 = 1 Then 'YES' Else 'NO' End,   'Active' = Case When SD.Active0 = 1 Then 'YES' Else 'NO' End,   'Client' = Case When SD.Client0 = 1 Then 'YES' Else 'NO' End,   'Client Type' = Case When SD.Client_Type0 = 0 Then 'Legacy Client' When SD.Client_Type0 = 1 Then 'Advanced Client' Else 'Device Client' End,   SD.Client_Version0 'Client Version', 'Decommissioned DDR Set' = Case When SD.Decommissioned0 = 1 Then 'YES' Else 'NO' End,   SD.Hardware_ID0 'Hardware ID', SD.User_Domain0 'Account Domain', SD.User_Name0 'Last Logon User Name', SD.Operating_System_Name_and0 NOS, SD.Resource_Domain_Or_Workgr0 'Resource Domain', SD.Sms_Unique_Identifier0 'SMAS Unique Identifier', Convert(Char(101), WD.LastHwScan) 'Last Hardware Scan Date', Convert(Char(101), CS.TimeKey)  'Last Computer Time Stamp'   From System_Disc SD Join WorkstationStatus_Data WD On WD.MachineID = SD.ItemKey  Join Computer_System_Data CS On CS.MachineID = SD.ItemKey     Where (SD.Obsolete0 = 1 AND SD.Active0 = 0)

SMS Infrastructure Server Information Query

  This SQL query will allow you to find information about your Windows 2000 and Windows 2003 SMS infrastructure servers and return the specified information about each.   SQL Query:   Select Distinct   SD.Netbios_Name0 'Machine Name',   'SMS Client' = Case When SD.Client0 = 1 Then 'YES' Else 'NO' End,   'Client Type' = Case When SD.Client_Type0 = 0 Then 'Legacy' When SD.Client_Type0 = 1 Then 'Advanced' Else 'Device Client' End,   SD.Client_Version0 'Client Version', SD.Resource_Domain_OR_Workgr0 'Domain',   'Operating System Version' = Case When SD.Operating_System_Name_and0 Like '%5.2%' Then 'Microsoft Windows 2003' When SD.Operating_System_Name_and0 Like '%5.1%' Then 'Microsoft Windows 2000' Else 'Microsoft Windows NT' End,   OS.BuildNumber0 'Build Number', OS.CSDVersion0 'Service Pack', IP.IP_Addresses0 'IP Address', MA.MAC_Addresses0 'MAC Address', CS.Manufacturer00 Manufacturer, CS.Model0 Model, SE.SerialNumber00 'Serial Number', SE.SMBIOSAssetTag00 'Asset Tag'   From System_Disc SD Join Operating_System_Data OS on SD.ItemKey = OS.MachineID Join System_Ip_Address_Arr IP on SD.ItemKey = IP.ItemKey Join System_Mac_Addres_Arr MA on SD.ItemKey = MA.ItemKey Join System_System_Rol_Arr SR on SD.ItemKey = SR.ItemKey Join Computer_System_Data CS on OS.MachineID = CS.MachineID Join System_Enclosure_Data SE on OS.MachineID = SE.MachineID   Where SR.System_Roles0 Like 'SMS%'

Inactive And Obsolete Machines Query

  Active This data type is initially set to 1 (Yes) and is set to 0 (No) when the client health tools determines that the client failed its checks and finds that the client is either not in a healthy state or is no longer on the network.   Obsolete This data type is initially set to 0 (No) and is set to 1 (Yes) when the site server determines that the client hardware ID has been updated or superseded by another record for the machine. If multiple records are in place having the same hardware ID value for the machine then the older records are then marked as obsolete.   PreviousSMSUUID When the computer determines that the machines hardware has changed dramatically a new GUID is created for the computer and it marks the old record and the old GUID as obsolete.   SQL Query:   Select  SD.Netbios_Name0, SD.User_Name0, SD.User_Domain0,   'Obsolete' = Case When SD.Obsolete0 = 0 Then 'NO' When SD.Obsolete0 = 1 Then 'YES' Else ' ' End,   'Active' = Case When SD.Active0 = 0 Then 'NO' When SD.Active0 = 1 Then 'YES' Else ' ' End,   SD.Client0, SD.Client_Type0, SD.Client_Version0, SD.Hardware_ID0, SD.Creation_Date0, SD.SMS_Unique_Identifier0, SD.Previous_SMS_UUID0, SD.SMS_UUID_Change_Date0, AN.AgentName, DI.AgentSite   From System_DISC SD Join DiscItemAgents DI On SD.ItemKey = DI.ItemKey Join Agents AN On DI.AgentID = AN.AgentID   Where SD.Obsolete0 = 1 And SD.Active0 = 0

Finding Machines Without A Specific Application And Version Installed

  By request script to find machines that do not have McAfee VirusScan Enterprise version 8 installed.   To find machines that do not have a specified application and a specific version installed you need to use the sub-select query with the “Not In” Keywords.   For example if you want to find all of the machines in your SMS site that do not have your latest antivirus application installed you might use the following query that is intended to find the machines that do not have McAfee VirusScan Enterprise version 8 installed.   Select SD.Name0, SD.User_Name0 From System_Disc SD   Where SD.Name0 Not In   (Select SD.Name0 From Add_Remove_Programs_Data Where DisplayName00 = 'McAfee VirusScan Enterprise' And Version00 = '8.0.0')

Deleting Machines Directly From The SMS Database

  To delete an individual machine from the SMS database using the SMS console it is necessary to create a collection using the direct membership rule wizard or base your new collection on a newly created Query. The first method is slow and can be time consuming, the second option just doubles your work and it the least desired method for most people.   The other automatic means in which you can remove or otherwise delete the machine(s) from the SMS database is to let the SMS_SQL_MONITOR service automatically remove the machine after it has reached its predefined Site maintenance task ‘Delete Aged Discovery Data’ and ‘Delete Aged Inventory History ‘ specifications if you have left it enabled. It is enabled by default and is set to 90 days. You can however change this to a more reasonable time period appropriate for your sites hierarchy if needed.   Below you will find a SQL query that will allow you to delete machines from the SMS database as the task “Delete Special” performs the removal process. It is important to note here that when you delete machines from a collection within the SMS console the machine is deleted from the database however the History table is not purged or removed. When you use the task Delete Special the machine is not only removed from the SMS database but the history for the machine is also deleted.   NOTE: You must uninstall the SMS client software to ensure that it does not report back in. If you are simply removing the machine name(s) from the SMS database because your site support staff has informed you that the machines were retired or re-imaged then this is not necessary. It is important to also note here that this is not a Microsoft supported means for deleting machines from the SMS database and should be used in a non production environment.   When you are done use the query in my earlier post entitled: ‘Searching Your SQL Database For A Specified Column String’ and search for the machine(s) that you just deleted to ensure that they have in fact been purged.   To delete multiple machines using the query that follows you can simply change the line that reads: Where Name0 = 'Machine_Name'  To the following: Where Name0 in ('Machine_One', 'Machine_Two') as in the Deleting Multiple Machines SQL Query found at the end of this post.    

• Deleting An Individual Machine SQL Query:

    Insert DeletedMachines (SmsId)   Select IsNull(Sms_Unique_Identifier0,'') From System_Disc   Where Name0 = 'Machine_Name' And Sms_Unique_Identifier0 is not null   Delete System_Disc from System_Disc Where Name0 = 'Machine_Name'   Delete System_Data from System_Data Where Name0 = 'Machine_Name'    

• Deleting Multiple Machines SQL Query:

    Insert DeletedMachines (SmsId)   Select IsNull(Sms_Unique_Identifier0,'') From System_Disc   Where Name0 in ('Machine_One', 'Machine_Two') And Sms_Unique_Identifier0 is not null   Delete System_Disc from System_Disc Where Name0 in ('Machine_One', 'Machine_Two')     Delete System_Data from System_Data Where Name0 in ('Machine_One', 'Machine_Two')

Searching Your SQL Database For A Specified Column String

  In response to my post “Searching Your SQL Database for A Specified Column Name” many people have asked me if it is possible to go further into the SQL database to find not just a specified column header but actual SMS captured data such as an individual user or computer name.   To answer that question yes it is possible but it can become very complicated. We have to make use of Cursors to loop through tables and create temporary SQL tables to place all of the results into an array of sorts and then in turn enumerate that array to get the desired result set back from the query parser.   Note: In SQL server a Cursor, whether it is cursed or blessed by SQL DBA’s, is simply a result set that allows you to recursively loop through elements in a row by row operation on a returned result set. Cursors must adhere to specific rules and must include the following flow: First you must declare or set the Cursor, then open the cursor to gather information, then fetch or grab information from it and then finally close or deallocate the cursor to terminate it.   To use the SQL query below change the line Set @Locator = '%Don Hite%' from %Don Hite% to the string that you want to search for. As was mentioned in my original post the percent symbols are set to function as wildcard placeholders. You can either retain the percent symbols or remove one or both of them as you wish.   To run or execute the SQL query below simply copy and paste the SQL query below into the SQL query analyzer (Isqlw.Exe from the start > run line) use the database dropdown arrow to select your SMS database change the Set @Locator =  to the keyword that you wish to locate and press the F5 keyboard shortcut to start the query parser.   SQL Query:   Declare @Locator VarChar(50) Declare @Object Int Declare @Column VarChar(50) Declare @Qry NVarChar(500)   Set @Locator = '%Don Hite%' Create Table #TableColumns ("Table Name" VarChar(50), "Column Name" VarChar(50))   Declare Table_Cursor Cursor For Select Id From SysObjects Where XType = 'U'   Open Table_Cursor Fetch Next From Table_Cursor Into @Object   While @@Fetch_Status = 0 Begin   Declare Column_Cursor Cursor For Select SysColumns.Name From SysColumns,SysTypes Where SysColumns.Id = @Object And SysColumns.XType = Systypes.XType And Systypes.Name In ('char', 'nchar', 'nvarchar', 'varchar')   Open Column_Cursor Fetch Next From Column_Cursor Into @Column   While @@Fetch_Status = 0 Begin Set @Qry = 'Insert Into #TableColumns ' + 'Select Distinct ''' + Object_Name(@Object) + ''', ' + '''' + @Column + ''' ' + 'From [' + Object_Name(@Object) + '] ' + 'Where Exists (Select 1 From [' + Object_Name(@Object) + '] ' + 'Where [' + @column + '] Like ''%' + @Locator + '%'') ' Exec Sp_ExecuteSql @Qry Fetch Next From Column_Cursor Into @Column End   Close Column_Cursor DeAllocate Column_Cursor Fetch Next From Table_Cursor Into @Object End   Close Table_Cursor DeAllocate Table_Cursor   Select "Table Name", "Column Name" From #TableColumns Drop Table #TableColumns  

Quick SMS Component Status Check

For those people that have only one SMS primary site server to manage opening the SMS administrations console and looking at the System Status leaf is usually not much of a time consuming chore. However if you have a parent site with several sites below it that task can start to become a taxing operation very rapidly.   The following SQL query will allow you to quickly look at your SMS sites component status messages and will allow you to see when a site has reached the Warning or Critical stage. This in turn will allow you to save some time performing your daily management tasks.   The Summarizer_Components Status SQL table has the Status column name set as a 4 byte integer where 0 is OK, 1 is Warning and 2 represents Critical. The tally interval is currently set to 0001128000100008 which is Since 12:00 AM. Below you will find the currently available Tally Intervals for the Summarizer_ComponentTallys.TallyInterval.     Since 12:00 AM 0001128000100008   Since 04:00 AM                                                       0081128000100008   Since 08:00 AM                                                        0101128000100008   Since 12:00 PM                                                       0181128000100008   Since 04:00 PM                                                       0201128000100008   Since 08:00 PM                                                       0281128000100008   Since Sunday                                                           0001128000192000   Since Monday                                                           00011280001A2000   Since Tuesday                                                           00011280001B2000   Since Wednesday                                                     00011280001C2000   Since Thursday                                                         00011280001D2000   Since Friday                                                              00011280001E2000   Since Saturday                                                          00011280001F2000   Since 1st of month                                                     000A470000284400   Since 15th of month                                                   000A4700002BC400   Since site installation                                                 0001128000080008     You can also modify the SQL query below to flag only those sites where the Summarizer_Components Status is either OK, in a Warning or in a Critical state by changing the line  And SC.Status >= 1 to And SC.Status = 0, 1 or 2. The SQL query can also be modified further to see only those sites that have a specified state by changing the line And SC.Status to And SC.State and adding the State numeric value that you want to query for.     SQL Query:   Select    SC.SiteCode, SC.MachineName, SC.ComponentName,   'SC.Status' = Case When SC.Status = 0 Then 'OK' When SC.Status = 1 Then 'Warning' When SC.Status = 2 Then 'Critical' Else ' ' End,   'SC.State' = Case When SC.State = 0 Then 'Stopped' When SC.State = 1 Then 'Started' When SC.State = 2 Then 'Paused' When SC.State = 3 Then 'Installing' When SC.State = 4 Then 'Re-Installing' When SC.State = 5 Then 'De-Installing' Else ' ' End,   ST.Errors, ST.Infos, ST.Warnings   From Summarizer_Components SC Join Summarizer_ComponentTallys ST On SC.ComponentDataID = ST.ComponentDataID   Where ST.TallyInterval = '0001128000100008' And SC.Status >= 1   Order by ST.Errors