Jump to content


Tuomas järvinen

Report count of computers in certain Organizational units

Recommended Posts

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

Share this post


Link to post
Share on other sites


Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...