For more details please see: http://social.technet.microsoft.com/Forums/systemcenter/en-US/aea60e7b-a88b-48d8-8cc3-8675097abe62/trying-to-do-a-report-of-sup-failures-by-ou?forum=configmgrinventory SELECT OU.ou, COUNT(*) AS ‘COUNT’ FROM dbo.v_R_System SYS join ( select SOU.ResourceID, Max(SOU.System_OU_Name0) as ‘ou’ from dbo.v_RA_System_SystemOUName SOU Group by SOU.ResourceID) OU on SYS.ResourceID = OU.ResourceID /*WHERE SYS.Netbios_Name0 IN ( ‘PHO-PVR63186’, ‘WCO-PVP11579’, ‘PAL-PVI82336’, ‘LOM-PVI46044’, ‘LOM-PVI49102’, ‘LOM-PVR46148’, ‘PUG-PVI92643’, ‘RED-PVI54875’, ‘REN-PVR27553’, ‘WLA-PVR125834’)*/ Group by OU.ou ORDER BY OU.ou
Tag: SMS
Please see post http://social.technet.microsoft.com/Forums/systemcenter/en-US/aea60e7b-a88b-48d8-8cc3-8675097abe62/trying-to-do-a-report-of-sup-failures-by-ou?forum=configmgrinventory for full details SELECT Max(SOU.System_OU_Name0), COUNT(*) AS ‘COUNT’ FROM dbo.v_RA_System_SystemOUName SOU JOIN dbo.v_R_System SYS ON SYS.ResourceID = SOU.ResourceID /*WHERE SYS.Netbios_Name0 IN ( ‘PHO-PVR63186’, ‘WCO-PVP11579’, ‘PAL-PVI82336’, ‘LOM-PVI46044’, ‘LOM-PVI49102’, ‘LOM-PVR46148’, ‘PUG-PVI92643’, ‘RED-PVI54875’, ‘REN-PVR27553’, ‘WLA-PVR125834’)*/ Group by SOU.System_OU_Name0 ORDER BY SOU.System_OU_Name0
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 ‘ ‘ […]
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 […]
This WQL query will show you all PCs with a HW and SW Scan date of greater than 180 days. select SMS_R_System.Name, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId where […]
Use this query to find all PCs with MS Access in the All systems collection. select SMS_R_System.ResourceID, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_FullCollectionMembership on SMS_FullCollectionMembership.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = “msaccess.exe” and SMS_FullCollectionMembership.CollectionID = ‘SMS00001’
Use this query to join Users to PCs Select R.Name0, U.User_Name0, U.Full_User_Name0, U.Unique_User_Name0, U.Mail0 From dbo.v_R_System R INNER JOIN dbo.v_R_User U ON U.Unique_User_Name0 = R.User_Domain0 + ‘\’+ R.User_Name0 .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem […]
For full details please see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c42ba8cf-02a5-4e2e-8d20-39b05a4a5740 select distinct R.Netbios_Name0 as ‘Name’, R.User_Name0 as ‘User’, CS.Manufacturer0 as ‘Manufacturer’, CS.Model0 as ‘Model’, BIOS.SerialNumber0 as ‘Serial’, SD.Name0 as ‘Processor’, RAM.TotalPhysicalMemory0 as ‘Memory’, VC.Name0 as ‘Video Card’, VC.VCRam as ‘Video Card RAM’ from dbo.v_R_System R inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId inner join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID […]
For full details please see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c42ba8cf-02a5-4e2e-8d20-39b05a4a5740 select distinct R.Netbios_Name0 as ‘Name’, R.User_Name0 as ‘User’, CS.Manufacturer0 as ‘Manufacturer’, CS.Model0 as ‘Model’, BIOS.SerialNumber0 as ‘Serial’, SD.Name0 as ‘Processor’, RAM.TotalPhysicalMemory0 as ‘Memory’, VC.Name0 as ‘Video Card’, VC.VCRam as ‘Video Card RAM’, Sound.Description0 as ‘Sound card’ from dbo.v_R_System R inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId inner […]
Use this query to find all webcam – SQL reports select CS.Name0, SD.Name0, SD.DeviceID0 from dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_GS_SYSTEM_DEVICES SD on CS.ResourceID = SD.ResourceID Where SD.Name0 like ‘%Webcam%’
Recent Comments