Tag: ssrs

Count of PCs by OU v2

  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

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 ‘ ‘ […]

Video Card Details v2

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 […]

Video card with Sound card.

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 […]

MAC Address report

For full details please see: http://social.technet.microsoft.com/Forums/en-ca/configmgrreporting/thread/19e0771c-eddc-43f7-956c-a5c9f0110e59   SELECT     A.Name0,     B.SerialNumber0 ,     A.Manufacturer0,     A.Model0,     C.Name0 ,     D.TotalPhysicalMemory0 ,     sum(E.Size0) ,     F.MACAddress0 ,     F.IPAddress0 ,     G.AD_Site_Name0 ,     A.UserName0 ,     H.Caption0 ,     H.CSDVersion0,     G.Creation_Date0 ,     I.LastHWScan–, —    BL.DriveLetter0, —    BL.ProtectionStatus0, —    BL.PersistentVolumeID0, —    […]

List Collection Membership

For full details, please see http://www.windows-noob.com/forums/index.php?/topic/7308-collection-membership/#entry27677 select C.Name from dbo.v_FullCollectionMembership FCM join dbo.v_Collection C on C.CollectionID = FCM.CollectionID Where FCM.Name = ‘Gartek-DC’

PC Serial Number by OU.

For full details, please see http://www.myitforum.com/forums/Active-Directory-Discovery-additional-Attributes-and-broken-reports-and-queries-m238462.aspx select distinct R.ResourceID, R.Name0, R.Client0, BIOS.SerialNumber0 from dbo.v_R_System AS R LEFT OUTER JOIN dbo.v_RA_System_SystemOUName AS OU ON R.ResourceID = OU.ResourceID INNER JOIN dbo.v_GS_PC_BIOS AS BIOS ON R.ResourceID = Bios.ResourceID where OU.System_OU_Name0 = N’GARTEK.TST/DOMAIN CONTROLLERS’

Finding all PCs without Symantec AV installed

For Full detail see http://social.technet.microsoft.com/Forums/en-US/configmgradminconsole/thread/547016e0-9f15-4611-aac9-24f582aa0614   select R.netbios_name0, R.user_name0, OS.Caption0 AS ‘Operating System’, ES.SerialNumber0 AS ‘Serial Number’ from dbo.v_R_System R join dbo.v_GS_OPERATING_SYSTEM OS on OS.ResourceID = R.ResourceID JOIN dbo.v_GS_System_Enclosure ES on ES.ResourceID = R.ResourceID where R.ResourceID not in ( select distinct ARP.ResourceId From dbo.v_ADD_REMOVE_PROGRAMS ARP join dbo.v_GS_System S on ARP.ResourceID = S.ResourceId where ARP.DisplayName0 in […]