Don Hite at myITforum.com : ConfigMgr Reports

Configuration Manager Report To Retrieve Installed Software For A Specified Machine

dhite — Fri, 08 Oct 2010 15:04:26 GMT

This SQL query was developed to be used as a Report to display the installed software inventory for a specified machine. This SQL Query and Report requires that you have Asset Intelligence Reports installed.

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

Name: Installed Software On A Specified Machine

Category: Installed Software Products

Comment: This Report Will Display The Installed Software Inventory For A Specified Machine - Requires Asset Intelligence Reports Is Installed.

Prompt Properties:

Name: Variable

Prompt Text: Machine Name

Check “Provide a SQL statement” and enter the following:

Select Netbios_Name0

From v_R_System_Valid

SQL Query:

Select vRSV.Netbios_Name0 'Machine Name',

'Display Name' = Case

When vGIS.ARPDisplayName0 Is NULL Then ''

Else vGIS.ARPDisplayName0

End,

vGIS.ProductName0 'Product Name',

'Product Version' = Case

When vGIS.ProductVersion0 Is NULL Then ''

Else vGIS.ProductVersion0

End,

'Publisher' = Case

When vGIS.Publisher0 Is NULL Then ''

Else vGIS.Publisher0

End,

'Install Date' = Case

When vGIS.InstallDate0 Is NULL Then ''

Else Convert(VarChar(10), vGIS.InstallDate0, 101)

End

From v_R_System_Valid vRSV

Join v_GS_Installed_Software vGIS On vRSV.ResourceID = vGIS.ResourceID

Where vRSV.Netbios_Name0 like @Variable

Order By 'Display Name'

Configuration Manager Report To Display Package Information For A Specified Package Server

dhite — Fri, 08 Oct 2010 15:02:01 GMT

This SQL query was developed to display package information for a specified package server.

Name: Display Package Information For A Specified Package Server

Category: Software Distribution - Packages

Comment: This Report Will Display Package Information For A Specified Package Server.

Prompt Properties:

Name: Variable

Prompt Text: Package Server Name

Check “Provide a SQL statement” and enter the following:

Select Distinct

PkgServer

From v_PackageStatus

Order By PkgServer

Else

Select Distinct

PkgServer

From v_PackageStatus

Where PkgServer like @__filterwildcard

Order By PkgServer

End

SQL Query:

Select

vPS.PkgServer Server,

PKG.PackageId ID,

PKG.Name Name,

PKG.Version,

PKG.Manufacturer,

PKG.Description,

PKG.PkgSourcePath Path

From v_Package PKG

Join v_PackageStatus vPS On PKG.PackageID = vPS.PackageID

Where vPS.PkgServer like @Variable

Configuration Manager Report To List Collections A Specified Machine Is A Member Of

dhite — Fri, 08 Oct 2010 14:56:36 GMT

This SQL query was developed to prompt you for a machine name and will display all of the collections that the specified machine is a member of.

Name: List Collections A Specified Machine Is A Member Of

Category: Software Distribution - Collections

Comment: This Report Will Prompt You For A Machine Name And Will Display The Collections The Machine Is A Member Of.

Prompt Properties:

Name: Variable

Prompt Text: Machine Name

Check “Provide a SQL statement” and enter the following:

Begin If (@__FilterWildcard = '')

Select Distinct Sys.Name0

From v_R_System Sys

Order By Sys.Name0

Else

Select Distinct Sys.Name0

From v_R_System Sys

Where Sys.Name0 Like @__FilterWildcard

Order By Sys.Name0

End

SQL Query:

Select

vC.Name 'Collection Name',

vC.CollectionID 'Collection ID'

From v_FullCollectionMembership vF,

v_Collection vC

Where vC.CollectionID = vF.CollectionID

And vF.Name = @Variable

Configuration Manager Report To Prompt For Version And Find Machines With Adobe Flash Player Installed

dhite — Fri, 08 Oct 2010 14:50:28 GMT

This SQL query was developed to prompt you to select an Adobe Flash Player version number and will in turn enumerate or list any machines with thge specified Adobe Flash Player version installed.

Name: Find Machines With Adobe Flash Player - Prompts For Version

Category: Software - Files

Comment: This Report Will Prompt You To Select An Adobe Flash Player Version Number And Will Enumerate Machines With Adobe Flash Player installed.

Prompt Properties:

Name: Variable

Prompt Text: Select Adobe Flash Player Version Number

Check “Provide a SQL statement” and enter the following:

Select Distinct Version0

From v_Add_Remove_Programs

Where DisplayName0 Like 'Adobe Flash Player%'

SQL Query:

Select

vRS.Name0 'Machine Name',

vApps.DisplayName0,

vApps.Publisher0,

vApps.Version0

From v_R_System vRS

Join v_Add_Remove_Programs vApps On vRS.ResourceID = vApps.ResourceID

Where vApps.DisplayName0 Like 'Adobe Flash Player%'

And vApps.Version0 = @Variable

Configuration Manager Report To Display Failed Advertisements From The Last Thirty Days

dhite — Fri, 08 Oct 2010 14:48:53 GMT

This SQL query was developed to report and display all of the failed advertisements from the last thirty days.

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

Name: Display Failed Advertisements From The Last Thirty Days

Category: Software Distribution - Advertisements

Comment: This Report Will Display The Failed Advertisements From The Last Thirty Days.

SQL Query:

Select

Count(*) 'Advertisement Count',

vA.AdvertisementName 'Advertisement Name',

vCAS.AdvertisementID 'Advertisement ID',

vCAS.LastStatusTime Updated

From v_ClientAdvertisementStatus vCAS

Join v_Advertisement vA On vA.AdvertisementID = vCAS.AdvertisementID

Where vCAS.LastStateName = 'Failed'

And (vCAS.LastStatusTime > DateAdd(D, - 30, GetDate()))

Group By vA.AdvertisementName, vCAS.AdvertisementID, vCAS.LastStatusTime

Configuration Manager Report To Retrieve The Last Logon User Name And Full Name For A Specified Machine

dhite — Fri, 08 Oct 2010 14:47:29 GMT

This SQL query was developed as a Report to retrieve the last logon user name and full name for a specified machine.

Name: Last Logon User Information For A Specified Machine

Category: Users

Comment: This Report Will Retrieve The Last Logon User Name And Full Name For A Specified Machine.

Prompt Properties:

Name: Variable

Prompt Text: Select Machine Name

Check “Provide a SQL statement” and enter the following:

Select Netbios_Name0

From v_R_System

SQL Query:

Select

vRS.Name0 'Machine Name',

vRS.User_Name0 'Login Name',

vRU.Full_User_Name0 'Full Name'

From v_R_System vRS

Join v_R_User vRU On vRS.User_Name0 = vRU.User_Name0

Where vRS.Name0 like @Variable

Configuration Manager Report To Review Successfully Completed Advertisements From The Last Thirty Days

dhite — Fri, 08 Oct 2010 14:45:42 GMT

This SQL query was developed to display the successfully completed advertisements from the last thirty days.

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

Name: Successfully Completed Advertisements From The Last Thirty Days

Category: Software Distribution - Advertisements

Comment: This Report Will Display The Successfully Completed Advertisements From The Last Thirty Days.

SQL Query:

Select

Count(*) 'Advertisement Count',

vA.AdvertisementName 'Advertisement Name',

vCAS.AdvertisementID 'Advertisement ID',

vCAS.LastStatusTime Updated

From v_ClientAdvertisementStatus vCAS

Join v_Advertisement vA On vA.AdvertisementID = vCAS.AdvertisementID

Where vCAS.LastStateName = 'Succeeded'

And vCAS.LastStatusMessageIdName = 'Program Completed With Success'

And (vCAS.LastStatusTime > DateAdd(D, - 30, GetDate()))

Group By vA.AdvertisementName, vCAS.AdvertisementID, vCAS.LastStatusTime

Configuration Manager Report To Count And Display All Network Operating Systems Installed

dhite — Fri, 08 Oct 2010 14:43:50 GMT

This SQL query was developed without any prompts to report, count and display all of the Network Operating Systems (NOS) installed including any Service Pack (SP) that may be installed.

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

Name: Count Network Operating Systems (NOS)

Category: Operating System

Comment: This Report Without Prompts Will Count And Display All The Network Operating Systems (NOS) Installed Including Any Service Packs.

SQL Query:

Select

Count(Caption0) 'Count',

Caption0 as 'Network Operating System (NOS)',

'Service Pack' = Case

When CSDVersion0 Is Null Then ''

Else CSDVersion0

End

From v_GS_Operating_System

Group By Caption0, CSDVersion0

Order By 'Count'

Configuration Manager Report To Display Software Distribution Collections With No Members

dhite — Fri, 08 Oct 2010 14:40:19 GMT

This SQL query was developed to be used as a Report to provide administrators with a list of collections that have no resources or members in them.

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

Name: Software Distribution Collections With No Members

Category: Software Distribution – Collections

Comment: This Report Will Display A List of Collections That Do Not Have Any System Resources Or Members In Them And Is Useful For Software Distribution Trueups.

SQL Query:

Select

CollectionID 'Collection ID',

Name 'Collection Name',

Comment,

LastRefreshTime 'Last Refresh Update'

From v_Collection

Where (CollectionID

Not In

(Select CollectionID

From v_FullCollectionMembership))