Jump to content


Recommended Posts

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

Share this post


Link to post
Share on other sites


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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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/

 

 

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