Finding programs and applications for users in an SCCM collection can be a challenge since much of the out of box SCCM reporting focuses on devices, not the users. However, with this simple PowerShell script, the headache can be alleviated.
The script does a few tasks to report back accurate data.
- Gets all users from the specified user collection
- Gets all devices that which the user is listed as the primary owner
- Checks the device for the software that was specified
- If the device has software that matches the name given, the user, device and software are outputted into a text file
The script below must be used on the primary SCCM server and it is always safer to run the PowerShell session as Administrator. Just fill in the variables at the beginning of the script to fit your environment and needs.
- $SiteCode – Enter your SCCM site code
- $Software – This is the name of the software as listed in Software Inventory. This will be used in a SQL query, so the rules of query language apply. For example, use % as a wildcard.
- $Collection – This is the name of the user collection that you want to check the users for
- $SQLServer = This is the name of the SQL Server that the SCCM database is stored on
- $DBName = This is the name of your SCCM database. Usually it is CM_<site code>
- $FilePath – This is the path of the output file that the report will put data in. It is best to save this as a text file.
For example, if you need to know who in the All Users collection has any version of Microsoft Word, just change the $Collection variable to “All Users” and for $Software, type in “%Microsoft Word%”.
Once the script is run and there are any users that have the software specified, a text file will be created listing all the users and the devices that they have the software on. The software that matches will be listed with its version.