Report count of computers in certain Organizational units

[Tuomas järvinen]

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

[GarthMJ]

Try this

http://tinyurl.com/lqnlbzx

[Tuomas järvinen]

hey!,

 

Tanks a Lot!