How to Find Stale User Profiles on a Windows Machine Using ConfigMgr and SQL

When managing user profiles on a Windows machine, it’s important to identify "stale" profiles—those that haven't been used for a long time. Stale profiles can take up unnecessary disk space, lead to system clutter, and cause security concerns. In this article, we will guide you through how to identify stale user profiles using System Center Configuration Manager (ConfigMgr) (also known as SCCM) and SQL.

By leveraging the v_GS_CCM_RECENTLY_USED_APPS view, you can quickly pinpoint inactive profiles, helping to optimize your system's performance and security.

What is ConfigMgr (SCCM)?

System Center Configuration Manager (SCCM), now known as Microsoft Endpoint Configuration Manager, is a robust tool for managing a wide range of IT operations across enterprise environments. SCCM allows you to deploy software, manage devices, and monitor system health. One of the key features of SCCM is the ability to track user activities on Windows machines, which includes details about applications installed and used by users.

Why Identify Stale User Profiles?

Stale user profiles can lead to several problems:

• Wasted disk space: Unused profiles take up unnecessary storage, especially in environments with limited resources.
• Security risks: Unused profiles may be a target for unauthorized access if left unchecked.
• System performance: Over time, user profiles that are no longer needed can slow down system performance and clutter up resources.

By identifying these profiles, you can take action to clean up and optimize your system, ensuring better performance and security.

Using SQL to Find Stale User Profiles with ConfigMgr

SCCM collects a wealth of information about user activities, including which applications have been used. One useful view is v_GS_CCM_RECENTLY_USED_APPS, which stores data about applications launched by users. By querying this view, you can find out which user profiles are no longer active or have not been used recently.

Here’s a step-by-step guide to help you use SQL to find stale profiles.

Step 1: Querying the v_GS_CCM_RECENTLY_USED_APPS View

The v_GS_CCM_RECENTLY_USED_APPS view holds information about user activities, including the last time an application was used by a particular user. To identify stale user profiles, you can filter users who have not launched any applications within a specific time period.

Here’s an SQL query to help you find stale user profiles:

SELECT DISTINCT
    UserName,
    LastUsedTime
FROM
    v_GS_CCM_RECENTLY_USED_APPS
WHERE
    LastUsedTime < DATEADD(DAY, -30, GETDATE())
ORDER BY
    LastUsedTime ASC;

Explanation of the Query:

• UserName: Displays the name of the user who launched the application.
• LastUsedTime: Provides the timestamp when the user last launched an application.
• DATEADD(DAY, -30, GETDATE()): Filters out users who haven’t used any applications in the last 30 days. You can modify the -30 to adjust the threshold for what you consider "stale."
• DISTINCT: Ensures that only unique users are displayed, even if multiple applications were used by the same user.

Step 2: Join with Other Tables for More Insights

You may want to gather more information, such as the machine name or user login details, to enrich your findings. To do so, you can join the v_GS_CCM_RECENTLY_USED_APPS table with other SCCM tables, such as v_R_System (which contains system details).

Here’s an example of how to do that:

SELECT DISTINCT
    rs.Name0 AS MachineName,
    ua.UserName,
    ra.LastUsedTime
FROM
    v_GS_CCM_RECENTLY_USED_APPS ra
JOIN
    v_R_System rs ON ra.ResourceID = rs.ResourceID
WHERE
    ra.LastUsedTime < DATEADD(DAY, -30, GETDATE())
ORDER BY
    ra.LastUsedTime ASC;

Explanation of the Enhanced Query:

• MachineName: Includes the machine name from the v_R_System table, helping you to track which machine the user profile is associated with.
• UserName and LastUsedTime: Continue to provide user details and the timestamp of their last app use.
• JOIN: Combines the data from v_GS_CCM_RECENTLY_USED_APPS and v_R_System based on the ResourceID, which is a unique identifier for each machine in SCCM.

Step 3: Analyze the Results

Once the SQL query runs, you’ll get a list of user profiles who haven’t used any applications in the last 30 days (or your defined threshold). From here, you can:

• Verify the Profiles: Check if the users are still part of the active workforce or if their accounts can be archived or deleted.
• Take Action: Depending on your organization’s policy, you can either delete the stale profiles or archive them for future reference.

Best Practices for Managing Stale Profiles

1. Regular Audits: Set up a periodic task (e.g., monthly or quarterly) to run the SQL query and review stale profiles.
2. Define a Threshold: Determine what constitutes a “stale” profile based on your organization’s needs. Some businesses may consider profiles inactive after 30 days, while others may need a longer period.
3. Automate Cleanup: For large environments, consider automating the cleanup of stale profiles based on your findings, either by using SCCM’s maintenance features or by scripting actions for archiving/deleting user data.

Conclusion

Using ConfigMgr (SCCM) and SQL to identify stale user profiles on Windows machines is an effective way to maintain a clean, optimized system. By regularly reviewing which profiles haven’t been used recently, you can free up resources, improve security, and ensure that your system is performing at its best.

By leveraging SCCM’s v_GS_CCM_RECENTLY_USED_APPS view, you gain valuable insights into user activity, helping you identify and address inactive profiles with ease. Whether you're performing a routine audit or responding to a specific issue, this method provides an efficient solution for managing stale profiles across your network.