User Profile Report

[craigster]

I'm trying to create a custom report which will give me an idea of how many local profiles are each machine. So far, I've been able to come up with a report which will tell me this but it lists the each instance of a profile with the workstation it's on. So, for example, if Bob1 and Bob2 have a profile on PC1 with will list this as two entries in the PC column. What I want to do is end up with a reports that will count the profiles and list those with the most in either descending order or over a certain amount (think I know how this is done).

Here's what I have already:

SELECT        v_GS_SYSTEM.Name0,
                v_GS_USER_PROFILE.localPath0
FROM            v_GS_USER_PROFILE INNER JOIN
                         v_GS_SYSTEM ON v_GS_USER_PROFILE.ResourceID = v_GS_SYSTEM.ResourceID
WHERE        (v_GS_USER_PROFILE.localPath0 IS NOT NULL)
GROUP BY v_GS_SYSTEM.ResourceID, v_GS_SYSTEM.Name0, v_GS_USER_PROFILE.localPath0
ORDER BY v_GS_SYSTEM.Name0

[GarthMJ]

you need to use the count command. 

SELECT        
    RV.Netbios_Name0 as 'Computer',
    Count(UP.localPath0) as '# of Profiles'
FROM
    dbo.v_R_System_Valid RV
    INNER JOIN dbo.v_GS_USER_PROFILE UP ON UP.ResourceID = RV.ResourceID
WHERE
    UP.localPath0 IS NOT NULL
GROUP BY 
    RV.Netbios_Name0
ORDER BY 
    RV.Netbios_Name0

[craigster]

Thanks Garth,

 A colleague helped me to come up with a solution as well:

SELECT        v_GS_SYSTEM.Name0, count(*) As "Profile Count"

FROM            v_GS_USER_PROFILE INNER JOIN
                         v_GS_SYSTEM ON v_GS_USER_PROFILE.ResourceID = v_GS_SYSTEM.ResourceID
WHERE        (v_GS_USER_PROFILE.localPath0 IS NOT NULL)
GROUP BY  v_GS_SYSTEM.Name0
ORDER BY count(*) desc

Which also works.

[GarthMJ]

You should always use v_r_System_Valid. Otherwise you could bu looking at deleted computers too. Take a look at this blog, it also applies to CMCB too. 

https://www.enhansoft.com/get-the-most-accurate-and-up-to-date-data-using-the-v_r_system_valid-sql-query-in-configuration-manager-2012/

 

 

[craigster]

Thanks again Gareth, I've update my query.