Month – January 2014

PCs with Either of two applications installed.

Use this query to determine if a PC has either or both applications installed. For more details, please see http://social.technet.microsoft.com/Forums/systemcenter/en-US/6c4650c7-3246-4e13-a78d-05691c13c89d/duplicate-rows-when-finding-pcs-with-installed-software?forum=configmgrreporting   SELECT DISTINCT R.Netbios_Name0, R.User_Domain0+’\’+ R.User_Name0 as ‘User Name’, OS.Caption0 AS ‘Operating System Caption’, CASE isnull(WIR.ResourceID,0 ) When 0 THEN ‘ ‘ ELSE ‘*’ END AS ‘Application1’, CASE isnull(MIR.ResourceID,0 ) When 0 THEN ‘ ‘ […]

Limiting a report to a Collection

For more details please see http://www.windows-noob.com/forums/index.php?/topic/9766-sccm-report-on-collection/     SELECT DISTINCT CS.Name0, R.User_Name0, MAX(SOU.System_OU_Name0) AS Expr1, CS.Description0, CS.Manufacturer0, CS.Model0, BIOS.SerialNumber0 FROM dbo.v_Collection Coll join dbo.v_FullCollectionMembership FCM on Coll.CollectionID = FCM.CollectionID and Coll.Name = ‘All systems’ join dbo.v_GS_COMPUTER_SYSTEM CS on FCM.ResourceID = CS.ResourceID INNER JOIN dbo.v_R_System R ON CS.ResourceID = R.ResourceID INNER JOIN dbo.v_RA_System_SystemOUName SOU ON CS.ResourceID […]

Report to show major Internet Explorer version

For more details see. http://social.technet.microsoft.com/Forums/systemcenter/en-US/14bb9caa-5f0b-418e-88b5-e359ef6d116b/report-to-show-major-internet-explorer-version?forum=configmgrreporting select SF.FileName, OS.Caption0, replace(left(SF.FileVersion,2), ‘.’,”) as ‘IE Version’, Count (Distinct SF.ResourceID) as ‘Total’ From dbo.v_GS_SoftwareFile SF JOIN v_FullCollectionMembership fcm on SF.ResourceID=fcm.ResourceID JOIN dbo.v_GS_OPERATING_SYSTEM OS ON SF.ResourceID = OS.ResourceID join dbo.v_GS_SYSTEM S on SF.ResourceID = S.ResourceID Where SF.FileName = ‘iexplore.exe’ and SF.FilePath like ‘%Internet Explorer%’ and S.SystemRole0 = ‘Workstation’ Group by […]