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.ResourceIDJOIN 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 Comments
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.
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