on facebook

https://www.facebook.com/pages/System-Center-2012/233309080195571?fref=ts

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 :


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

SMS Web Report To Get Current Site System Status

SQL :

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

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

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’

Count Number Of Machines With Specified Application Installed

SQL  :
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 = '14'

SQL Query Add And Removed Programs For A Collection

SQL :

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