Devices Per OU With Friendly Name

[pvr02]

I'm looking to get a report that will show me how many devices we have in each location. The best way to do this would be by using OU information.

 

I located the script on this page that will show me each OU but would like to modify it so that it so it shows a more descriptive name instead of the OU. Is this something easy to adjust?

select
    ou.ou,
    count(*) as 'total'
from
    (SELECT sys.ResourceID, max(OU.System_OU_Name0) AS 'OU' FROM dbo.v_R_System AS sys INNER JOIN dbo.v_RA_System_SystemOUName AS ou ON sys.ResourceID = ou.ResourceID GROUP BY sys.ResourceID) ou
group by 
    ou.ou
Order by 
    ou.ou

[GarthMJ]

What more descriptive name do you want? Where would you find these details in CM07/CM12?

[pvr02]

Instead of showing 'domain\usa\california\sacramento\desktops' to be able to show 'Sacramento Desktops'.

[GarthMJ]

So how exactly would you determine that that OU is called Sacramento Desktops?

 

It sound like a manual process to me. As such you can use a monster "Case" statement to convert each OU into a better description.

[pvr02]

I checked into the case statements and was able to figure it out.

 

Thanks for your help.

select
    ou.ou,
    count(*) as 'total'
from
    (SELECT sys.ResourceID, case 
when max(OU.System_OU_Name0) like 'domain/usa/california/sacramento/desktop%' THEN 'Sacramento Desktops' 
when max(OU.System_OU_Name0) like 'domain/usa/california/sacramento/laptop%' THEN 'Sacramento Laptops' 
ELSE max(OU.System_OU_Name0) END 
AS 'OU' FROM dbo.v_R_System AS sys INNER JOIN dbo.v_RA_System_SystemOUName AS ou ON sys.ResourceID = ou.ResourceID GROUP BY sys.ResourceID) ou
group by 
    ou.ou
Order by 
    ou.ou

[GarthMJ]

:-) I'm glad you got it figure out.