Search the Community

Hi, I'm attempting to construct a report, which counts computers by Active directory Ou in sccm 2012sp1. So far I have found only http://www.systemcentercentral.com/sccm-reportingadding-the-system-ou-as-a-column/ as a reference, but for some reason the following query SELECT TOP (100) PERCENT COUNT(sys.Netbios_Name0) AS Computers, (SELECT TOP (1) System_OU_Name0 FROM dbo.v_RA_System_SystemOUName AS ou2 WHERE (ou.ResourceID = ResourceID) AND (LEN(System_OU_Name0) = MAX(LEN(ou.System_OU_Name0)))) AS [Org.unit] FROM dbo.v_R_System_Valid AS sys INNER JOIN dbo.v_RA_System_SystemOUName AS ou ON sys.ResourceID = ou.ResourceID GROUP BY ou.ResourceID HAVING ((SELECT TOP (1) System_OU_Name0 FROM dbo.v_RA_System_SystemOUName AS ou2 WHERE (ou.ResourceID = ResourceID) AND (LEN(System_OU_Name0) = MAX(LEN(ou.System_OU_Name0)))) = N'domain.local/computers/staff/office-x/room-y') ORDER BY Computers calculates only 6, when such OU ( domain.local/comptuers/staff/office-x/room-y) has 14 computer objects. While SELECT distinct count ( v_R_System.Name0 ) as count_of_computers ,v_RA_System_SystemOUName.System_OU_Name0 as Ouname FROM v_R_System INNER JOIN v_RA_System_SystemOUName ON v_R_System.ResourceID = v_RA_System_SystemOUName.ResourceID Group by v_RA_system_systemouname.System_ou_name0, v_r_system.name0 HAVING v_RA_System_SystemOUName.System_OU_Name0 LIKE N'domain.local/computers/staff/office-x/room-y' shows even less number of computers in Ou, but if I omit distinct, the number of rows matches the computers... :/ I know, there IS easier way to accomplish the results, but this seems to be hard... I'm also aware, that changes made in AD are not immediately efective in SCCM database reports. This is not a problem. In our scenario, structure for computers is computers/type-of-comptuer/office/room/spec and I'd like to know How many computers I do have in room ou. Thanks for advices. ~T