SCCM report Computer information with Boundary added

Full Details : http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/188bb324-9217-4fa6-b814-06a3d353bf52

Boundary information (AD Site) added .

select distinct v_GS_SYSTEM.Name0 as 'Computer Name',A.AD_Site_Name0, substring(AccountName0,charindex('Domain=',Accountname0)+8,(charindex('Name=',Accountname0)-charindex('Domain=',Accountname0)-10)) as 'Domain Name', substring(AccountName0,len(AccountName0)-charindex('"',reverse(AccountName0),2)+2,charindex('"',reverse(AccountName0),2)-2) as 'User Name'
from v_GS_SYSTEM
INNER JOIN v_GS_LocalAdmins ON v_GS_SYSTEM.ResourceID = v_GS_LocalAdmins.ResourceID
INNER JOIN v_R_System A ON v_GS_SYSTEM.ResourceID=A.ResourceID

JOIN v_FullCollectionMembership FCM on v_gs_system.ResourceID = FCM.ResourceID
where (AccountName0 not like '%Administrator%' AND AccountName0 not like '%Domain Admins%')

and
FCM.CollectionID='SMSC1003'

2 Responses to "SCCM report Computer information with Boundary added"

  1. Hi,
    I'm trying to gather a list of OSes in a particular AD boundary.

    I found this query from the canned reports:

    SELECT OPSYS.Caption0 as C054, COUNT(*) AS 'Count'
    FROM v_GS_OPERATING_SYSTEM OPSYS
    inner join v_R_System sys on OPSYS.ResourceID=sys.ResourceID
    GROUP BY OPSYS.Caption0
    ORDER BY OPSYS.Caption0

    Wondering how to add AD boundary into there.

    Thanks again...Your blogs have been tremendously helpful.

    Reply
    1. You can try this by proving AD site information if you are using AD site as Boundary :

      SELECT OPSYS.Caption0 as C054, COUNT(*) AS 'Count'
      FROM v_GS_OPERATING_SYSTEM OPSYS
      inner join v_R_System sys on OPSYS.ResourceID=sys.ResourceID
      where sys.AD_Site_Name0='AD-Sitename'
      GROUP BY OPSYS.Caption0
      ORDER BY OPSYS.Caption0

      Reply

Leave a Reply