Custom query on Microsoft SMS Server

Here is the SQL server custom query that I use to list UserName, Model, Manufacturer, Serial Number, Mac Address and IP_Address of all workstations (that have the sms client installed) :

SELECT     SIS.ResourceID, SYS.User_Name0, SYS.Name0, CS.Model0, CS.Manufacturer0, PB.SerialNumber0, SIS.SMS_Installed_Sites0, MAC.MAC_Addresses0, IP.IP_Addresses0
FROM         dbo.v_RA_System_SMSInstalledSites AS SIS INNER JOIN
                      dbo.v_R_System AS SYS ON SIS.ResourceID = SYS.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID = SIS.ResourceID INNER JOIN
                      dbo.v_GS_PC_BIOS AS PB ON PB.ResourceID = SIS.ResourceID INNER JOIN
                      dbo.v_RA_System_IPAddresses AS IP ON IP.ResourceID = SIS.ResourceID INNER JOIN
                      dbo.v_RA_System_MACAddresses AS MAC ON SIS.ResourceID = MAC.ResourceID
WHERE     (SYS.Client_Type0 = 1) AND (SYS.Active0 = 1) AND (NOT (IP.IP_Addresses0 LIKE ‘192.168%’)) AND (NOT (IP.IP_Addresses0 LIKE ‘0.0%’))

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s