SCCM can report software by user, but only if you structure the query around user-to-device relationships, because SCCM fundamentally inventories software per device, not per AD user. The trick is to join:

• AD user → SCCM user resource
• User resource → primary devices (User Device Affinity or logon history)
• Device → installed software inventory
• Device → AD attributes (department, organization, etc.)

Below is a clear breakdown of what works today, which built‑in reports help, and how to build a custom SQL report that filters by AD user, department, or organization.

✅ What SCCM Can Already Do (Built‑In Reports)

Two built‑in reports are directly relevant:

1. “Windows apps installed to the specified user”

Shows Windows Store apps installed in the user context.

2. “Users that recently used a specified executable program”

Shows which users have interacted with a specific application.

These are useful, but they do not give a full “all software installed for this AD user across all their devices” view.

✅ What You Actually Need: A Custom SCCM Report

To produce a report like:

“Show all software installed for AD user X, filtered by Organization/Department.”

You need to query:

• v_R_User → AD user attributes (department, org, etc.)
• v_UserMachineRelationship or v_GS_SYSTEM_CONSOLE_USAGE → user-to-device mapping
• v_R_System → device info
• v_Add_Remove_Programs or v_GS_INSTALLED_SOFTWARE → installed software inventory

🧩 SQL Query Template (You Can Drop Into SSRS)

This is the standard pattern SCCM admins use:

 

SELECT  
    usr.Full_User_Name0 AS [User Name],
    usr.Department0 AS [Department],
    sys.Name0 AS [Device Name],
    arp.DisplayName0 AS [Software Name],
    arp.Version0 AS [Version],
    arp.Publisher0 AS [Publisher]
FROM v_R_User usr
JOIN v_UserMachineRelationship umr
    ON usr.ResourceID = umr.UserResourceID
JOIN v_R_System sys
    ON umr.MachineResourceID = sys.ResourceID
JOIN v_Add_Remove_Programs arp
    ON sys.ResourceID = arp.ResourceID
WHERE usr.User_Name0 = @UserName
  AND usr.Department0 = @Department
ORDER BY usr.Full_User_Name0, sys.Name0, arp.DisplayName0

Parameters you can add:

• @UserName (AD user)
• @Department (AD attribute)
• @Organization (AD attribute)
• @SoftwareName (optional filter)

🧭 How to Use This in SCCM

1. Open SCCM Console → Monitoring → Reporting → Reports
2. Right‑click → Create Report
3. Choose SQL-based report
4. Paste the SQL above
5. Add parameters for user, department, etc.
6. Deploy to SSRS

🏢 Department / Organization Filtering

If your AD schema populates:

• department
• company
• physicalDeliveryOfficeName
• extensionAttributeX

…then SCCM syncs these into v_R_User automatically.

You can filter like:

 

WHERE usr.Department0 = @Department

🧠 Important Notes

• SCCM does not natively track “software installed by a user,” only “software installed on a device.”
• To approximate “user-installed software,” you must rely on:
  • User Device Affinity
  • Logon history
  • Per-user app inventory (Windows apps only)

 

 

SCCM stores AD user attributes (like Department) in v_R_User, but collections are device‑based. So you create a device collection whose membership rule joins:

• Device → Primary User (User Device Affinity)
• Primary User → AD Department attribute

Below is the clean, production‑ready query SCCM admins typically use.

🎯 Device Collection: Group Devices by User Department

1. Create a Device Collection

Assets and Compliance → Device Collections → Create Device Collection

Choose “Query Rule” and paste the SQL below.

✅ WQL Query for Devices by User Department

Replace “Finance” with the department you want.

select SMS_R_System.ResourceID,
       SMS_R_System.ResourceType,
       SMS_R_System.Name,
       SMS_R_System.SMSUniqueIdentifier,
       SMS_R_System.ResourceDomainORWorkgroup,
       SMS_R_System.Client
from SMS_R_System
join SMS_R_UserMachineRelationship as UMR
     on SMS_R_System.ResourceID = UMR.MachineResourceID
join SMS_R_User as U
     on UMR.UserResourceID = U.ResourceID
where U.Department = "Finance"
  and UMR.Types = 1

What this does:

• UMR.Types = 1 → Only primary devices (User Device Affinity)
• Filters by AD Department
• Returns all devices whose primary user belongs to that department

🧠 If You Want a Dynamic Collection for Every Department

You can create one collection per department by duplicating the rule and changing the department name.

Or…

I can generate a PowerShell script that automatically creates a collection for each department found in AD.

🔍 Optional: Use Logon History Instead of UDA

If your org doesn’t use User Device Affinity, use this version:

 

select distinct SMS_R_System.ResourceID,
       SMS_R_System.Name
from SMS_R_System
join SMS_G_System_SYSTEM_CONSOLE_USAGE as CU
     on SMS_R_System.ResourceID = CU.ResourceID
join SMS_R_User as U
     on CU.TopConsoleUser = U.UniqueUserName
where U.Department = "Finance"

This uses Top Console User instead of UDA.

 

 

SELECT  
    usr.Unique_User_Name0 AS UserName,
    usr.Full_User_Name0 AS FullName,
    usr.Department0 AS Department,
    sys.Name0 AS DeviceName,

    -- Installed Software (detailed inventory)
    soft.ProductName0 AS InstalledSoftwareName,
    soft.ProductVersion0 AS InstalledSoftwareVersion,
    soft.Publisher0 AS InstalledSoftwarePublisher,
    soft.InstallDate0 AS InstalledSoftwareInstallDate,

    -- Add/Remove Programs (traditional inventory)
    arp.DisplayName0 AS ARPName,
    arp.Version0 AS ARPVersion,
    arp.Publisher0 AS ARPPublisher,
    arp.InstallDate0 AS ARPInstallDate

FROM v_R_User usr
JOIN v_UserMachineRelationship umr
       ON usr.ResourceID = umr.UserResourceID
       AND umr.Types = 1   -- Primary User (UDA)

JOIN v_R_System sys
       ON sys.ResourceID = umr.MachineResourceID

LEFT JOIN v_GS_INSTALLED_SOFTWARE soft
       ON sys.ResourceID = soft.ResourceID
       AND soft.ProductName0 LIKE '%' + @SoftwareName + '%'

LEFT JOIN v_Add_Remove_Programs arp
       ON sys.ResourceID = arp.ResourceID
       AND arp.DisplayName0 LIKE '%' + @SoftwareName + '%'

WHERE usr.Department0 = @Department
ORDER BY usr.Full_User_Name0, sys.Name0, soft.ProductName0, arp.DisplayName0;

🔧 Parameters You Provide

@Department

Examples:

• "Finance"
• "IT"
• "Human Resources"

@SoftwareName

Examples:

• "Chrome"
• "Adobe"
• "Zoom"
• "Microsoft Edge"

🧠 What This Query Gives You

✔ Primary user only (UDA)

Accurate user-to-device mapping.

✔ Department filter

Only users in the department you specify.

✔ Specific software filter

Matches both Installed Software and ARP.

✔ Full software coverage

• MSI
• Non‑MSI
• Drivers
• Middleware
• Traditional ARP entries

✔ Device + user + software in one result set

Perfect for audits, licensing checks, and compliance reporting.

 

CREATE VIEW v_DeviceUserSoftwareFull AS
SELECT  
    sys.ResourceID AS DeviceResourceID,
    sys.Name0 AS DeviceName,
    sys.User_Domain0 AS DeviceDomain,
    sys.Client0 AS ClientStatus,

    usr.ResourceID AS UserResourceID,
    usr.Unique_User_Name0 AS UserName,
    usr.Full_User_Name0 AS FullName,
    usr.Department0 AS Department,
    usr.Company0 AS Company,
    usr.Title0 AS Title,

    -- Installed Software (detailed inventory)
    soft.ProductName0 AS InstalledSoftwareName,
    soft.ProductVersion0 AS InstalledSoftwareVersion,
    soft.Publisher0 AS InstalledSoftwarePublisher,
    soft.InstallDate0 AS InstalledSoftwareInstallDate,

    -- Add/Remove Programs (traditional inventory)
    arp.DisplayName0 AS ARPName,
    arp.Version0 AS ARPVersion,
    arp.Publisher0 AS ARPPublisher,
    arp.InstallDate0 AS ARPInstallDate

FROM v_R_System sys

LEFT JOIN v_UserMachineRelationship umr
       ON sys.ResourceID = umr.MachineResourceID
       AND umr.Types = 1   -- Primary User (UDA)

LEFT JOIN v_R_User usr
       ON usr.ResourceID = umr.UserResourceID

LEFT JOIN v_GS_INSTALLED_SOFTWARE soft
       ON sys.ResourceID = soft.ResourceID

LEFT JOIN v_Add_Remove_Programs arp
       ON sys.ResourceID = arp.ResourceID;

🎯 What This View Gives You

✔ Full software coverage

• MSI
• Non‑MSI
• Drivers
• Middleware
• Traditional ARP entries

✔ Accurate user-to-device mapping

• Uses User Device Affinity (UDA)
• Ensures software is tied to the user’s actual primary device(s)

✔ AD user attributes included

• Department
• Company
• Title

✔ Device metadata

• Device name
• Domain
• Client status

✔ Perfect for SSRS, PowerBI, SQL queries, automation

 

🧪 Example Queries Using This View

1. Show specific software for a department

 

SELECT *
FROM v_DeviceUserSoftwareFull
WHERE Department = 'Finance'
  AND (InstalledSoftwareName LIKE '%Adobe%' 
       OR ARPName LIKE '%Adobe%');

2. Show all software for a specific user

 

SELECT *
FROM v_DeviceUserSoftwareFull
WHERE UserName = 'DOMAIN\\jdoe';

3. Show all devices with no primary user

 

SELECT DISTINCT DeviceName
FROM v_DeviceUserSoftwareFull
WHERE UserName IS NULL;

4. Count installs of a specific product

 

SELECT InstalledSoftwareName, COUNT(*) AS InstallCount
FROM v_DeviceUserSoftwareFull
WHERE InstalledSoftwareName LIKE '%Chrome%'
GROUP BY InstalledSoftwareName;

You only need to update: $SqlServer, $Database, $Department, $SoftwareName, $ReportPath, $SmtpServer, $From, $To

# ============================================
#  SCCM Software Audit Script (Full Automation)
#  - Query SCCM DB
#  - Export to CSV (timestamped)
#  - Export to Excel (timestamped)
#  - Send HTML email with embedded table
# ============================================

# -------- CONFIGURATION --------
$SqlServer    = "YourSQLServerName"
$Database     = "CM_ABC"

$Department   = "Finance"
$SoftwareName = "Adobe"

$ReportPath   = "C:\Reports"
$Timestamp    = (Get-Date -Format "yyyyMMdd_HHmmss")

$CsvPath      = "$ReportPath\Dept-Software-$Department-$SoftwareName-$Timestamp.csv"
$ExcelPath    = "$ReportPath\Dept-Software-$Department-$SoftwareName-$Timestamp.xlsx"

$SmtpServer   = "smtp.yourdomain.com"
$From         = "sccm-reports@yourdomain.com"
$To           = "it-audit@yourdomain.com"
$Subject      = "Software Report ($Department) - $SoftwareName - $Timestamp"

# -------- SQL QUERY --------
$query = @"
SELECT  
    usr.Unique_User_Name0 AS UserName,
    usr.Full_User_Name0 AS FullName,
    usr.Department0 AS Department,
    sys.Name0 AS DeviceName,

    soft.ProductName0 AS InstalledSoftwareName,
    soft.ProductVersion0 AS InstalledSoftwareVersion,
    soft.Publisher0 AS InstalledSoftwarePublisher,
    soft.InstallDate0 AS InstalledSoftwareInstallDate,

    arp.DisplayName0 AS ARPName,
    arp.Version0 AS ARPVersion,
    arp.Publisher0 AS ARPPublisher,
    arp.InstallDate0 AS ARPInstallDate

FROM v_R_System sys
LEFT JOIN v_UserMachineRelationship umr
       ON sys.ResourceID = umr.MachineResourceID
       AND umr.Types = 1   -- Primary User (UDA)

LEFT JOIN v_R_User usr
       ON usr.ResourceID = umr.UserResourceID

LEFT JOIN v_GS_INSTALLED_SOFTWARE soft
       ON sys.ResourceID = soft.ResourceID
       AND soft.ProductName0 LIKE '%' + '$SoftwareName' + '%'

LEFT JOIN v_Add_Remove_Programs arp
       ON sys.ResourceID = arp.ResourceID
       AND arp.DisplayName0 LIKE '%' + '$SoftwareName' + '%'

WHERE usr.Department0 = '$Department'
ORDER BY usr.Full_User_Name0, sys.Name0, soft.ProductName0, arp.DisplayName0;
"@

# -------- RUN SQL QUERY --------
$connectionString = "Server=$SqlServer;Database=$Database;Integrated Security=SSPI;"
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = $query

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$table = New-Object System.Data.DataTable
$adapter.Fill($table) | Out-Null

$connection.Close()

# -------- EXPORT TO CSV --------
$table | Export-Csv -Path $CsvPath -NoTypeInformation -Encoding UTF8

# -------- EXPORT TO EXCEL --------
# Requires: Install-Module ImportExcel
$table | Export-Excel -Path $ExcelPath -AutoSize -BoldTopRow

# -------- BUILD HTML TABLE --------
$HtmlTable = $table | ConvertTo-Html -Fragment

$HtmlBody = @"
<html>
<head>
<style>
table { border-collapse: collapse; width: 100%; font-family: Arial; font-size: 12px; }
th { background-color: #003366; color: white; padding: 6px; border: 1px solid #ddd; }
td { padding: 6px; border: 1px solid #ddd; }
tr:nth-child(even) { background-color: #f2f2f2; }
</style>
</head>
<body>
<h2>Software Report for Department: $Department</h2>
<h3>Software Filter: $SoftwareName</h3>
<p>Generated: $(Get-Date)</p>
$HtmlTable
</body>
</html>
"@

# -------- SEND EMAIL WITH EMBEDDED HTML TABLE --------
Send-MailMessage `
    -From $From `
    -To $To `
    -Subject $Subject `
    -Body $HtmlBody `
    -BodyAsHtml `
    -SmtpServer $SmtpServer `
    -Attachments @($CsvPath, $ExcelPath)

Write-Host "HTML email sent and files exported successfully."