Skip to content

User Profile Report

Featured Replies

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

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

  • Author

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.

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...

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.