SCCM SQL query to find total number of user profiles on workstation devices

  • SCCM SQL query to find total number of user profiles on workstation devices

    Posted by Roddy Gelberty on 21 July 2020 at 8:50 am

    How do I create an SQL query to find total number of user profiles on workstation devices.
    We’ve got a lot of devices that have multiple profiles that take up a lot of local storage space.
    This leads to other issues with Software Update installation failures.

    Roddy Gelberty replied 3 years, 8 months ago 2 Members · 2 Replies
  • 2 Replies
  • Topaz George

    Organizer
    21 July 2020 at 9:14 am

    Does the below SQL query help?

    select v_R_System.Name0, Count(LocalPath0) as profileTotal from v_GS_USER_PROFILE
    left join v_R_System ON v_R_System.ResourceID = v_GS_USER_PROFILE.ResourceID
    where LocalPath0 LIKE '%\users\%' and v_R_System.Operating_System_Name_and0 LIKE '%worksta%'
    group by v_R_System.Name0 order by profileTotal desc
  • Roddy Gelberty

    Member
    21 July 2020 at 11:54 am

    That works as treat. I’ll just copy and paste and create a table in SQL server reporting services report. Thank you for your help.

Log in to reply.

DCOM hardening issue.

This application fails to authenticate with WMI on the SCCM server because Microsoft has not yet hardened DCOM on their Windows Preinstallation Environment. We are working on a different approach, but it will only be released during the first quarter of 2024. But until that time, the only workaround will be to uninstall the update corresponding to KB5004442.