09 August 2008

ALL SQL Queries

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

 

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

 

SQL Query:

 

Select 

SD.Name0 'Machine Name',

SC.SMS_Assigned_Sites0 'Assigned Site',

SD.Client_Version0 Version

From v_R_System SD

 

Join v_RA_System_SmsAssignedSites SC on SD.ResourceID = SC.ResourceID

Join v_GS_Operating_System OS on SD.ResourceID = OS.ResourceID

 

Where SD.Client0 = 1

And SD.Client_Type0 = 1

 

Order By 'Machine Name'

 

SQL Query To Gather Video Card Memory Information

 

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

 

SQL Query:

 

Select    

SD.Name0 'Machine Name',

VC.Name0 'Video Card',

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

 

From v_R_System SD

Join v_Gs_Video_Controller VC on SD.ResourceID = VC.ResourceID

 

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

Order By SD.Name0

SQL Query To See When Collections Were Last Updated

 

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

 

SQL Query:

 

Select

CC.CollectionID,

CN.CollectionName,

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

 

From Collection_MemberChg_Notif CC

Join Collections CN on CC.CollectionID = CN.SiteID

 

Order By CollectionName

SQL Query To Get Members Of A Specified Collection

 

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

 

SQL Query:

 

Select

Members.Name

'Collection Members:'

 

From CollectionMembers Members

Join Collections Coll on Members.SiteID = Coll.SiteID

 

Where Coll.CollectionName = 'All Systems'

 

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

 

Select CollectionName From Collections

SQL Query Get User Information From A Specified OU

 

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

 

SQL Query:

 

Select Distinct

CS.Name0 'Machine Name',

CS.UserName0 'User Name',

RU.Full_User_Name0 'Full Name',

UOU.User_OU_Name0 'Users OU',

RA.IP_Subnets0  'Subnet'

 

From v_Gs_Computer_System CS 

Join v_RA_System_IPSubnets RA on RA.ResourceID = CS.ResourceID

Join v_R_User RU on RU.Unique_User_Name0 = CS.UserName0

Join v_RA_User_UserOUName UOU on UOU.ResourceID = RU.ResourceID

 

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

 

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

SQL Query For Windows Media Player Versions

 

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

 

SQL Query:

 

Select Distinct

v_R_System.Name0,

v_GS_SoftwareFile.FileName,

v_GS_SoftwareFile.FileDescription,

v_GS_SoftwareFile.FileVersion

 

From v_R_System

Join v_GS_SoftwareFile on v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID

 

Where FileName = 'wmplayer.exe'

SQL Query To Enumerate ConfigMgr 2007 Boundaries

 

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

 

SQL Query:

 

Select 

Value,

SiteCode,

 

'Boundary Type' = Case

When BoundaryType = 0 Then 'IP Subnet'

When BoundaryType = 1 Then 'Active Directory Site'

When BoundaryType = 2 Then 'IPv6 Prefix'

When BoundaryType = 3 Then 'IP Address Range'

Else 'UnKnown'

End,

 

DisplayName,

 

'Connection' = Case

When BoundaryFlags = 0 Then 'Fast'

When BoundaryFlags = 1 Then 'Slow'

Else 'UnKnown'

End,

 

'Protected' = Case

When Action = 0 Then 'No'

When Action = 1 Then 'Yes'

Else 'UnKnown'

End

 

From Boundary

SQL Query Get Machine and User Information From A Specified Collection

 

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

 

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

 

SQL Query:

 

Select

SD.Name0 'Machine Name',

SD.Resource_Domain_OR_Workgr0 'Resource Domain',

SD.User_Name0 'Login ID',

SD.User_Domain0 'Account Domain',

USR.Full_User_Name0 'Full Name',

PCB.SerialNumber0 'Serial Number',

CS.Manufacturer0 Manufacturer,

CS.Model0 Model,

SAS.SMS_Assigned_Sites0 'Assigned Site Code'

From v_R_System SD

 

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

Join v_R_User USR on SD.User_Name0 = USR.User_Name0

Join v_GS_PC_BIOS PCB on SD.ResourceID = PCB.ResourceID

Join v_GS_COMPUTER_SYSTEM CS on SD.ResourceID = CS.ResourceID

Join v_RA_System_SMSAssignedSites SAS on SD.ResourceID = SAS.ResourceID

 

Where COL.Name = 'All Systems'

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

 

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

 

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

 

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

 

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

 

Name: Get Machine and User Information From Specified Collection

Category: Users

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

 

Select "Edit SQL Statement" and add the following:

 

Select

SD.Name0 'Machine Name',

SD.Resource_Domain_OR_Workgr0 'Resource Domain',

SD.User_Name0 'Login ID',

SD.User_Domain0 'Account Domain',

USR.Full_User_Name0 'Full Name',

PCB.SerialNumber0 'Serial Number',

CS.Manufacturer0 Manufacturer,

CS.Model0 Model,

SAS.SMS_Assigned_Sites0 'Assigned Site Code'

From v_R_System SD

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

Join v_R_User USR on SD.User_Name0 = USR.User_Name0

Join v_GS_PC_BIOS PCB on SD.ResourceID = PCB.ResourceID

Join v_GS_COMPUTER_SYSTEM CS on SD.ResourceID = CS.ResourceID

Join v_RA_System_SMSAssignedSites SAS on SD.ResourceID = SAS.ResourceID

Where COL.Name =  @ID

 

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

 

Name: ID

Prompt Text: Specify a collection Name

 

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

 

Begin

If (@__filterwildcard = '')

Select Distinct Name From v_Collection

Order by Name

Else Select Distinct Name From v_Collection

Where Name like @__filterwildcard

Order By  Name

End

 

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

SQL Query To Retrieve Clients Last Boot up Date

 

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

 

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

 

Select 

SD.Name0 'Machine Name',

SD.User_Name0 'Last Logged on User Name',

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

 

From v_R_System SD

Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID

 

Order By 'Machine Name'

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

 

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

 

SQL Query:

 

Select

SD.Name0 'Machine Name',

SD.Operating_System_Name_and0 NOS,

SS.Name0 'Service Name',

SS.DisplayName0 'Display Name',

SS.StartMode0 'Start Type',

SS.Started0 Started,

SS.State0 State,

SS.Status0 Status

 

From System_DISC SD

Join Services_DATA SS

on SS.MachineID = SD.ItemKey

 

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

Order By 'Machine Name'

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

 

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

 

SQL Query:

 

Select

Count(SD.Name0) Counts,

PF.DisplayName0,

PF.Version0

 

From v_Add_Remove_Programs PF

Join v_R_System SD on PF.ResourceID = SD.ResourceID

Where PF.DisplayName0 = 'McAfee VirusScan Enterprise'

 

Group By PF.DisplayName0, PF.Version0

Order By Counts, PF.Version0

 

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

 

Select

SD.Name0,

PF.DisplayName0,

PF.Version0

 

From v_Add_Remove_Programs PF

Join v_R_System SD on PF.ResourceID = SD.ResourceID

Where PF.DisplayName0 = 'McAfee VirusScan Enterprise'

 

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

Order By SD.Name0

 

Advertisement Status For Multiple Advertisement IDs

 

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

 

SQL Query:

 

Select

OfferName Name,

Recieved,

Failures,

ProgramsStarted Started,

ProgramsFailure Errors,

ProgramsSuccess Success,

OfferID 'Advertisement ID'

 

From vOfferStatusSummarizerRoot

Where ScheduleToken = '0001128000080008'

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

 

Order by OfferName

SQL Query To List Roaming Boundary IP Subnets

 

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

 

SQL Query:

 

Select IPSubnet

From RoamingBoundaryIPSubnet

 

Where SiteCode = 'XXX'

Order by SiteCode

SQL Query To Locate Full User Names From A Specified Collection

 

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

 

SQL Query:

 

Select  

SD.Name0 'Machine Name',

SD.User_Name0 'Login ID',

USR.Full_User_Name0 'Full Name'

From v_R_System SD

 

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

Join v_R_User USR on SD.User_Name0 = USR.User_Name0

 

Where COL.Name = 'All Systems'

 

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

 

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

 

SQL Query:

 

Select

SD.Name0 'Machine Name',

SD.Resource_Domain_OR_Workgr0 'Resource Domain',

SD.User_Name0 'Login ID',

SD.User_Domain0 'Account Domain',

USR.Full_User_Name0 'Full Name',

ARP.DisplayName0 'Display Name'

From v_R_System SD

 

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection CN on FCM.CollectionID = CN.CollectionID

Join v_R_User USR on SD.User_Name0 = USR.User_Name0

Join v_Add_Remove_Programs ARP on SD.ResourceID = ARP.ResourceID

 

Where CN.Name = 'All Systems'

And ARP.DisplayName0 Like '%VPN%'

 

SQL Query To View Web Reports By Category

 

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

 

SQL Query:

 

Select

Name,

Comment,

ReportId,

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 Authority\System) accounts.

 

SQL Query:

 

Use Master

 

Select 

SP.SpId 'Session ID',

SP.Nt_UserName UserName,

SD.Name DatbaseName,

SP.LogiName LoginId,

SP.Program_Name App

 

From Sys.SysProcesses SP

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

 

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

And (SP.loginame <> 'sa')

 

Order By SpId

SQL Query To Retrieve The Distribution Points For A Site Server

 

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

 

Other Available Roles:

 

SMS Distribution Point

SMS Management Point

SMS Site Server

SMS Site System

SMS SQL Server

SMS Component Server

SMS Reporting Point

SMS Server Locator Point

 

SQL Query:

 

Select

ServerName 'Site Server Name',

SiteCode 'Site Code',

RoleName 'Role'

From v_SystemResourceList

Where RoleName = 'SMS Reporting Point'

SQL Query Example To Show Row Numbers When Executing A Query

 

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

 

SQL Query:

 

Select Row_Number()

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

Name0

From v_R_System

Order by Name0

 

 

 

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

 

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

 

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

 

SmBIOS Serial Number

The Machine's System Identifier (SID) 

Any one of 10 different Hardware ID Properties

 

Additional Microsoft Information:

 

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

 

SQL Query:

 

Select Distinct

 

SYS.Netbios_Name0 'Machine Name',

Convert(VarChar(10),

SYS.SMS_UUID_Change_Date0, 101)  'Change Generated',

SYS.ResourceID ID

 

From v_R_System SYS

JOIN v_R_System NEW on SYS.SMS_Unique_Identifier0 = NEW.Previous_SMS_UUID0

 

Order By 'Machine Name'

List All Tables In A Specified Database

 

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

 

SQL Query:

 

Declare @DatabaseName SysName

Declare @SqlQuery VarChar(200)

 

-- Change SMS_XXX To The Database you wish to query

Set @DatabaseName='SMS_XXX'

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

 

Where Table_Type = ''Base Table'''

Exec (@SqlQuery)

 

 

Using A Select Case Statement To Substitute Existing Null Values

 

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

 

Replaces Null with Unknown:

 

Select

Name0,

Case

When User_Name0 is Null

Then 'Unknown'

Else User_Name0

End User_Name0

From V_R_System

 

Replaces Null With An Empty Field:

 

Select

Name0,

Case

When User_Name0 is Null

Then ''

Else User_Name0

End User_Name0

From V_R_System

SQL Query To Retrieve ConFigMgr Client Registration And Heartbeat Discovery Times

 

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

 

SQL Query:

 

Select

CS.Name0 'Machine Name', 

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

AD.AgentName

From v_Gs_Computer_System CS

Join v_AgentDiscoveries AD on CS.ResourceID = AD.ResourceId

Where AD.AgentName In

('MP_ClientRegistration', 'Heartbeat Discovery')

 

SQL Query Get Server Name And Date Stamp

 

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

 

SQL Query:

 

Select

@@ServerName 'Server Name',

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

SQL Query To Count Operating Systems In A Specified Collection

 

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

 

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

 

SQL Query:

 

Select

SD.Operating_System_Name_and0 NOS,

Count (SD.Operating_System_Name_and0) 'Total',

COL.CollectionId,

COL.Name

From v_R_System SD

 

Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID

Join v_Collection COL on FCM.CollectionID = COL.CollectionID

 

Where Col.Name = 'All Windows NT Systems'

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

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

 

List Tables With Primary Keys

 

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

 

SQL Query:

 

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

From SysIndexes

 

Where (Status&2048)<>0

Order by Object_Name(id)

 

SQL Query To List SMS Resources From A Specified Subnet

 

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

 

SQL Query:

 

Select

Netbios_Name0 'Machine Name',

IP_Addresses0 'Last IP Address'

 

From System_IP_Address_ARR

Join System_Disc MachineName

on System_IP_Address_ARR.ItemKey = MachineName.ItemKey

 

Where IP_Addresses0 Like '192.168.1.%'

And Netbios_Name0 is not Null

 

Order by Name0 Asc

SQL Query To Gather ConfigMgr Or SMS Site Server Information

 

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

 

SQL Query:

 

Select 

ServerName,

'Site Type' = Case

When Type = 1 then 'Secondary'

Else 'Primary'

End,

SiteCode,

SiteName,

ReportingSiteCode,

InstallDir

From v_Site

Order By ReportingSiteCode

 

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

 

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

 

SQL Query:

 

Select Distinct

SD.Name0,

SP.ProductName

From System_Disc SD

Join vSMS_G_System_SoftwareProduct SP

On SP.ClientId = SD.ItemKey  

Where SP.ProductName = 'Microsoft SQL Server'

Or SP.ProductName

like 'Microsoft SQL 2005 Server%'

 

Count Number Of Machines With Specified Application Installed

 

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

 

SQL Query:

 

Select Distinct

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

From System_Disc

 

Join vSms_G_System_SoftwareFile as SoftwareFile

On SoftwareFile.ClientId = System_Disc.ItemKey

 

Where SoftwareFile.FileName = 'WinWord.Exe'

And SoftwareFile.FileVersion = 'X.X'

 

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

 

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

 

SQL Query:

 

Select Original_Login() 'Logon Name'

 

--or

SELECT USER as [User],

      SESSION_USER as [Session_User],

      CURRENT_USER as [Current_User],

      SYSTEM_USER as [System_User],

      ORIGINAL_LOGIN() as [Original_Login()]

SQL Query To Find Table Dependencies

 

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

 

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

 

C = Check Vonstraint

D = Default or Default Constraint

F = Foreign Key Constraint

L = Log

Fn = Scalar Function

If = Inlined Table Function

P = Stored Procedure

Pk = Primary Key Constraint

Rf = Replication Filter Stored Procedure

S = System Table

Tf = Table Function

Tr = Trigger

U = User Table

Uq = Unique Constraint

V = View

 

SQL Query:

 

Select Distinct

SysObjects.Name 'Table Name',

Procedures.Name 'Stored Procedure'

 

From SysObjects

Join (SysObjects Procedures

Join SysDepends

on Procedures.Id = SysDepends.Id)

On SysDepends.DepId = SysObjects.Id

 

Where SysObjects.XType = 'U'

 

-- Change XType Values here using chart above

And Procedures.XType = 'P'

 

Group by SysObjects.Name,

SysObjects.Id,

Procedures.Name

 

Order by SysObjects.Name Asc

SQL Query To List Excluded Servers With RegRead

 

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

 

SQL Query:

 

Exec Master..Xp_RegRead

'HKEY_LOCAL_MACHINE',

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

'ExcludeServers'

SQL Query To Find Specified Table Column Names

 

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

 

SQL Query:

 

Set NoCount On

Declare @ColVar VarChar(25)

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

 

Select

SysObjects.Name 'Table Names:',

SysColumns.Name 'Column Names:'

 

From SysObjects, SysColumns, SysTypes

 

Where SysObjects.ID = SysColumns.ID

And SysColumns.xType = SysTypes.xType

And SysColumns.Name like @ColVar

 

Order by SysObjects.Name Asc

Set NoCount Off

 

Usage Examples:

 

Pattern Match

Set @ColVar = '%Last%'

Set @ColVar = '%last%time%'

 

Exact Match

Set @ColumnNames = 'Last'

 

 

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

 

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

 

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

 

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

 

SQL Query:

 

Exec Master..Xp_RegAddMultiString

'HKEY_LOCAL_MACHINE',

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

'ExcludeServers',

'MachineOne'

 

Exec Master..Xp_RegRead

'HKEY_LOCAL_MACHINE',

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

'ExcludeServers'

 

RegAddMultiString Syntax

 

The RegAddMultiString syntax is shown below:

 

Exec Master..Xp_RegAddMultiString

'Root Key',   

'Key',

'Value Name',

'New String Value'

Retrieving All SMS Server Components

 

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

 

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

 

Declare @SiteCode NVarChar(128)

Set @SiteCode = 'XXX'

 

Select

RoleName,

Servername

 

From SysResList

Where SiteCode = @SiteCode

And ResourceType = 'Windows NT Server'

 

Order by RoleName

SQL Query To Gather Linked Servers Information

 

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

 

SQL Query:

 

Use Master

 

Select

SrvName 'Servers Name',

SrvProduct 'Product Name',

DataSource 'OLE Data Source Value'

From SysServers

Where ProviderName = 'Sqloledb'

And IsRemote = 1

 

Order by SrvName Desc

 

Find Last Database Backup Time Stamp

 

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

 

SQL Query:

 

Use Master

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

Backup_Finish_Date 'Last Backup Time Stamp'

From Msdb.Dbo.Backupset

 

Retrieve SQL Server Days On Line Uptime

 

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

 

SQL Query:

 

Select

Filename,

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

From SysDatabases

Where Name = 'TempDb'

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

 

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

 

SQL Script:

 

Select

SD.Name0 'Machine Name',

SD.User_Name0 'User Name',

LD.FreeSpace0 'Free Space'

From v_R_System SD

Join v_Gs_Logical_Disk LD on SD.ResourceId = LD.ResourceId

Where LD.DeviceId0 = 'C:'

And LD.FreeSpace0 < 300

And SD.Client0 = 1

Query To Report Table Owners

 

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

 

SQL Script:

 

Select

Table_Name as 'Table Name',

Table_Schema as 'Database Owner'

 

From Information_Schema.Tables

Where Table_Type = 'Base Table'

 

order by Table_Name Asc

 

Query Client Status And GUID Information

 

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

 

SQL Query:

 

Select

Name 'Machine Name',

Domain 'Domain Name',

Sitecode 'Site Code',

 

'Client' = Case

When IsClient = 1 Then 'YES'

Else '<N0>'

End,

 

'Assigned' = Case

When IsAssigned = 1 Then 'YES'

Else '<NO> '

End,

 

SmsId

From _Res_Coll_Sms00001 -- All Systems Collection

 

Where IsClient = 1

SQL Query To Find Views To Use In SMS Web Reports

 

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

 

SQL Query:

 

Select

Name 'Procedure Name',

Id 'Procedure ID',

CrDate 'Creation Date'

From SysObjects

Where Name like '%v%'

And XType = 'V'

 

Find And Add To Query Template

 

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

 

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

 

SQL Query:

 

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

From Information_Schema.Columns

Where Column_Name like '%Advertisement%'

SQL Query To Find Machines Within Specified IP Subnet Range

 

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

 

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

 

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

 

SQL Query:

 

Select   

SD.Name0 'Machine Name',

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

NIC.IpAddress0 'IP Address'

 

From v_R_System SD

Join v_Gs_Network_Adapter_Configur NIC on SD.ResourceId = NIC.ResourceId

 

Where SD.Client0 = 1

And SD.Decommissioned0 = 0

And SD.Obsolete0 = 0

 

And NIC.IPAddress0 Is Not NULL

And NIC.IPAddress0  <> '0.0.0.0'

And NIC.IPAddress0 Like '192.168.1.%'

Or NIC.IPAddress0 Like '192.168.2.%'

Or NIC.IPAddress0 Like '192.168.3.%'

Or NIC.IPAddress0 Like '192.168.4.%'

Or NIC.IPAddress0 Like '192.168.5.%'

 

Order By SD.Name0 , NIC.IPAddress0

 

Display Machines With A Specified Percentage Of Free Local Disk Space

 

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

 

Select

SD.Name0 'Machine Name',

LD.DeviceID0 Drive,

LD.FileSystem0 'File System',

LD.Size0 'Total Drive Size',

LD.FreeSpace0 'Total Free Space',

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

 

From System_Disc SD, Logical_Disk_Data LD

Where SD.ItemKey = LD.MachineId

 

And LD.Description0 = 'Local Fixed Disk'

 

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

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

 

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

Order By SD.Name0 Asc

SMS Web Report To Get Current Site System Status

 

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

 

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

 

Name: Get Current Site System Status

 

Category: SMS Site – General

 

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

 

SQL Statement:

 

Select Distinct

SiteCode,

Role,

'Status' = Case

When Status = 0 Then 'OK'

When Status = 1 Then 'Warning'

When Status = 2 Then 'Critical'

Else ' '

End

From v_SiteSystemSummarizer

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

 

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

 

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

 

Microsoft Family Name: Office Professional     

Licensed Product Name: Office Professional 2003       

Version: 2003  

License Type: Microsoft Volume License         

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

 

SQL Query:

 

Select Distinct

LIC.MlsFamilyName 'Microsoft  Family Name',

LIC.MlsProductName 'Licensed Product Name',

LIC.VersionCode Version,

LIC.LicenseTypeName 'License Type',

LIC.LastUpdated 'Last Update'

 

From  v_R_System SD

Join v_Gs_Installed_Software_Ms SW On SD.ResourceID = SW.ResourceID

Join v_Lu_MsProd LIC On SW.MPC0 = LIC.MPC

Where SD.Name0 = 'Machine_Name'

 

Group By LIC.MlsFamilyName, LIC.MlsProductName,

LIC.VersionCode, LIC.LicenseTypeName, LIC.LastUpdated

Order By LIC.MlsFamilyName

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

 

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

 

SQL Query:

 

Select Distinct

SD.Name0,

SR.RoleName,

LD.DeviceID0,

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

 

From v_R_System SD

Join v_Gs_Logical_Disk LD on SD.ResourceId = LD.ResourceId

Join v_SystemResourceList SR on SD.Netbios_Name0 = SR.ServerName

 

Where LD.DriveType0 = 3

and SR.RoleName = 'SMS Distribution Point'

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

 

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

 

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 Server\Share UNC path to the spreadsheet. If the data is not on sheet 1 change Sheet1$ to the worksheet name you wish to use.

 

SQL Script:

 

Insert into Table_Name

Select *

From OpenRowSet

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

DBQ=C:\File_Name.xls',

'Select * From [Sheet1$]')

 

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

Filed under: SQL Queries

Retrieving SMS Site Boundaries Or Roaming Boundaries IP Subnet Information

 

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

 

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

 

Site Boundaries SQL Script:

 

Select

SiteCode,

IpSubnet

From SiteBoundaryIpSubnet

Order by SiteCode

 

Roaming Boundaries SQL Script:

 

Select

SiteCode,

IpSubnet

From RoamingBoundaryIpSubnet

Order by SiteCode

Reading An Excel Spreadsheet In A SQL Query Window

 

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

 

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

 

SQL Script:

 

Select *

From OpenRowSet

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

DBQ=C:\File_Name.xls',

'Select * From [Sheet1$]')

 

SQL Query To Retrieve A Machines Site Code

 

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

 

SQL Query:

 

Select 

SYS.Name0 'Machine Name',

COL.SiteCode 'Site Code'

From v_R_System SYS

Join v_FullCollectionMembership COL on SYS.ResourceId = COL.ResourceId

Where COL.CollectionID = 'SMS00001'

And SYS.Name0 = 'Machine_Name'

SQL Query To Count Computer System Manufacturer And Models

 

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

 

SQL Query:

 

Select 

Manufacturer00 Manufacturer,

Model0 Model,

Count (Model0) Counts

From Computer_System_Data

Group By Model0, Manufacturer00

Order By Manufacturer00

SMS Site Boundary Subnet Queries

 

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

 

IP Addresses View:

 

Select

SYS.Name0 'Machine Name',

IP.Ip_Addresses0 'IP Address'

From v_R_System SYS

Inner Join v_Ra_System_IpAddresses IP

On SYS.ResourceId = IP.ResourceId

Where  IP.Ip_Addresses0  Like '192.168.1.%'

 

IP Subnets View:

 

Select

SYS.Name0 'Machine Name',

SN.Ip_Subnets0

From v_R_System SYS

Inner Join v_Ra_System_IpSubnets SN

On SYS.ResourceId = SN.ResourceId

Where SN.Ip_Subnets0 = '192.168.1.0'

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

 

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

 

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

 

SQL Script:

 

Select

SD.Name0 'Machine Name',

SD.User_Name0 'User Name',

IP.IP_Addresses0 'IP Address'

 

From System_Disc SD

Join System_Ip_Address_Arr IP on SD.ItemKey = IP.ItemKey  

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

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

 

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

 

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

 

SQL Script:

 

Select

IP.IP_Addresses0 'IP Address',

SD.User_Name0 'User Name',

SD.Name0 'Machine Name'

 

From System_Disc SD

Join System_Ip_Address_Arr IP on SD.ItemKey = IP.ItemKey  

Where SD.Name0 = 'xxx'

SQL Query To Retrieve The Most Active SQL Tables

 

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

 

SQL Query:

 

DBCC UpdateUsage(0)

With No_Infomsgs

 

Select SObject.Name as TableName,

SIndex.RowModCtr as TableUpdates

From SysIndexes SIndex

 

Join SysObjects SObject on SIndex.Id = SObject.Id

Where SObject.Type='U'

And RowModCtr Not Like '0'

And SIndex.IndId In (0,1)

 

Order by SIndex.RowModCtr Desc

 

Display Advertisement Package Status

 

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

 

SQL Query:

 

Select Distinct

StatusMessages.MachineName 'Machine Name',

 

'Advertisement Status' = Case

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

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

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

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

When ID&0x0000FFFF = 10002 Then 'Received'

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

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

When ID&0x0000FFFF = 10005 Then 'Started'

When ID&0x0000FFFF = 10006 Then 'Failed'

When ID&0x0000FFFF = 10007 Then 'Failed'

When ID&0x0000FFFF = 10008 Then 'Complete'

When ID&0x0000FFFF = 10009 Then 'Successful'

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

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

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

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

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

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

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

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

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

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

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

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

Else '<No Status !>'

End

 

From StatusMessages

Join StatusMessageInsStrs on StatusMessages.RecordID = StatusMessageInsStrs.RecordID

Join StatusMessageAttributes on StatusMessages.RecordID = StatusMessageAttributes.RecordID

 

Where StatusMessageAttributes.AttributeID = 401

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

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

 

Order by StatusMessages.MachineName Desc

Retrieving SMS Schema Information View Particulars

 

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

 

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

 

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

 

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

 

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

 

Select ViewName, ViewColumnName, IsStringType From v_ReportViewSchema

 

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

 

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

 

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

 

Select ResourceType, DisplayName, ResourceClassName From v_ResourceMap

 

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

 

Select Type, ViewName From v_SchemaViews

Machine ID To Machine Name

 

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

 

SQL Query:

 

Select

MachineID, Name as 'Machine Name'

From _Res_Coll_SMS00001

 

Where _Res_Coll_Sms00001.ArchitectureKey = 5

Order by Name ASC

 

Retrieve SQL Logins Information

 

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

 

SQL Query:

 

Select

LoginName 'Login ID Name',

DbName 'Default Database',

language 'Default Language',

 

'Account Type' = Case

When IsntName = 1 Then 'NT Account'

Else 'SQL Login'

End,

 

'Group Account' = Case

When IsntGroup = 1 Then 'Yes'

Else ' '

End,

 

'User Account' = Case

When IsntUser = 1 Then 'Yes'

Else ' '

End,

 

'SysAdmin Member' = Case

When SysAdmin = 1 Then 'X'

Else ' '

End,

 

'SecurityAdmin Member' = Case

When SecurityAdmin = 1 Then 'X'

Else ' '

End,

 

'ServerAdmin Member' = Case

When ServerAdmin = 1 Then 'X'

Else ' '

End,

 

'SetupAdmin Member' = Case

When SetupAdmin = 1 Then 'X'

Else ' '

End,

 

'ProcessAdmin Member' = Case

When ProcessAdmin = 1 Then 'X'

Else ' '

End,

 

'DiskAdmin Member' = Case

When DiskAdmin = 1 Then 'X'

Else ' '

End,

 

'DbCreator Member' = Case

When DbCreator = 1 Then 'X'

Else ' '

End

 

From Master..SysLogins

Query Name By Query ID View

 

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

 

SQL Query:

 

If Exists (Select Table_Name From Information_Schema.Views

Where table_name = 'SMS_Query_Information')

Drop View SMS_Query_Information

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_Machine\Software\Microsoft\SMS\Components\Sms_Discovery_Data_Manager\ExcludeServers 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

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

'ExcludeServers', -- Key Value

'Values'

 

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

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

Print ''

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

 
I took the sourse of these SQL Qury's from the next link and Just kept in one Page....These SQL Querys are taken from the following link.... thanks to  author
 
Enjoy,
Paddy
 

 

 

No comments: