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 […]
Use this query if you want to locate PCs by that last logon user but prompt for the user name each time. select SMS_R_System.Name from SMS_R_System where SMS_R_System.LastLogonUserName = ##PRM:SMS_R_System.LastLogonUserName## order by SMS_R_System.Name
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’, Convert(VarChar, VC.AdapterRam0 / 1024) + ‘ MB’ as ‘Video Card RAM’ from dbo.v_R_System R inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.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%’
Use this WQL query to find all webcam http://social.technet.microsoft.com/Forums/en-US/configmgrai/thread/fa5fcfe2-69ef-4482-adfc-de25d6d19669/?prof=required select SMS_R_System.Name, SMS_G_System_SYSTEM_DEVICES.Name, SMS_G_System_SYSTEM_DEVICES.DeviceID from SMS_R_System inner join SMS_G_System_SYSTEM_DEVICES on SMS_G_System_SYSTEM_DEVICES.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_DEVICES.Name like “%webcam%” .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 { color: […]
If you need to find all PC within an AD container (not an OU). Use this WQL query to find them. For more details about this request, see the full forum post. http://social.technet.microsoft.com/Forums/en-US/configmgrsetup/thread/9358cee7-75a5-4595-80a3-4016ea572f1b select SMS_R_System.Name, SMS_R_System.LastLogonUserName from SMS_R_System where SMS_R_System.SystemContainerName = “ENHANSOFT\\COMPUTERS”
For full details see: http://www.myitforum.com/forums/tm.aspx?high=&m=238953&mpage=1#239186 SELECT distinct sys1.Name0, csys.Model0, csys.Manufacturer0, os.Caption0, os.CSDVersion0, BIOS.SerialNumber0, sys1.description0, REPLACE((SELECT IPAddress0 + ‘, ‘ FROM v_GS_NETWORK_ADAPTER_CONFIGUR WHERE resourceID = sys1.resourceID AND ipenabled0 = 1 FOR XML PATH(”) )+’..’,’, ..’,”) AS [IP Addresses] FROM v_r_SYSTEM sys1 join v_GS_COMPUTER_SYSTEM csys on csys.resourceid=sys1.resourceid join v_GS_OPERATING_SYSTEM os on os.resourceid=sys1.resourceid join v_GS_PC_BIOS bios on bios.resourceid=sys1.resourceid […]
Recent Comments