How to Automatically Remove Spotify using SCCM [2019 Updated]

How to Automatically Remove Spotify using SCCM

Introduction

I have recently been asked to report on Spotify installations within our SCCM environment. The reason is that the IT policies state that users should not be installing Spotify on company machines but a number of people have been caught with it installed.

So, the business managers want to know exactly who has it installed so that the end users can be spoken to and asked to remove the software.

Also, having spent some time Googling a query for Spotify, we found that there were not many solutions for reporting on this software so thought we would share our solution here.

For some reason, Spotify does not seem to present itself within the ARP data – we may be wrong but in our database there was now data in the ARP for Spotify.

Guide

The following guide will walk you through how to monitor the spotify.exe file using SCCM software inventory and then how to query that data to produce a Power BI report.

I will also go through how to create a SCCM Collection for all installations of Spotify in your environment and then how to deploy a required uninstall to that collection.

Software Inventory

The first thing that will need to be set up is the collection of the spotify.exe file information. You can do this by carrying out the following steps:

  1. Open the SCCM Console
  2. Go to Administration > Overview > Client Settings
  3. Edit Your Default Device Settings
  4. Go to Software Inventory
  5. Click Set Types button
  6. Click the yellow sun (new) icon
  7. Type in spotify.exe
  8. Configure the other settings how you want or leave as default
  9. Click OK and exit the Settings

Dependant on your Software Inventory timing settings, this new configuration will be pushed out to your end users machines and then they will start to report back to the SCCM database.

Like everything with SCCM, it takes time so give it at least a few days before you start seeing some decent data coming through.

SQL Data and Query

Now we are collecting the spotify.exe file information within our software inventory, we can make use of the SoftwareFile and SoftwareInventory tables in the SCCM SQL database.

The SQL query you can use is as below:

SELECT
     *
FROM v_GS_SYSTEM SYS
     INNER JOIN SoftwareInventory SWI
          on SYS.ResourceID = SWI.ClientId
     INNER JOIN SoftwareFile SWF
          on SWF.ProductId = SWI.ProductId
     WHERE SWF.FileName = 'spotify.exe'

Although this will bring back all of the columns, I suggest that you change the * for just the columns that you actually require. Once you are happy with your query in SQL, we can move onto Power BI.

Power BI Report

Advanced SQL Query

You will probably want to know who has Spotify installed and on what machine, so when moving onto the Power BI report, you can use a query like this:

SELECT DISTINCT
     COMP.UserName0 AS [User],
     COMP.Manufacturer0 AS [Make],
     COMP.Model0 AS [Model],
     COMP.Name0 AS [Computer],
     SYS.SystemType0 AS [Architecture],
     SWI.ModifiedDate AS [Install Date],
     SWF.FileName AS [Filename],
     SWF.FileDescription AS [Product],
     SWF.FileVersion AS [Version]
FROM v_GS_SYSTEM SYS
     INNER JOIN SoftwareInventory SWI
          on SYS.ResourceID = SWI.ClientId
     INNER JOIN SoftwareFile SWF
          on SWF.ProductId = SWI.ProductId
     INNER JOIN v_GS_COMPUTER_SYSTEM COMP
	  on SYS.ResourceID = COMP.ResourceID
     WHERE SWF.FileName = 'spotify.exe'

Power BI Get Data

You can now open up Power BI Desktop Client and click on the Get Data icon and select SQL Server:

Power BI Get Data SQL Data
Power BI Get Data SQL Data

This will bring up a box where you should enter your own SCCM SQL database server and SCCM database name:

SCCM SQL Database Settings
SCCM SQL Database Settings

Then select the DirectQuery radio button and copy and paste the SQL query from earlier in this guide into the box and click the OK button:

Power BI Get Data SQL Query DirectQuery
Power BI Get Data SQL Query DirectQuery

Load the acquired data into the Power BI and you can then start to design how you want your report to look using the different tool sets available to you in Power BI. You should then end up with something like this (depending how many installations you have in your environment.

Power BI Spotify Report
Power BI Spotify Report

SCCM Automatic Uninstall

Now that you have your SCCM system monitoring which computers have Spotify installed on them and you can now see that information in a tidy Power BI report, you may want to act on getting Spotify removed.

But, you don’t have to do this manually like the old days. We can now setup a collection in SCCM using a WQL variant of our original SQL query as the direct query for our collection. This will then populate, automatically and in the future, the collection with any Spotify installations that are found.

We then deploy the Spotify uninstall command to that collection. This means that even if someone installs or reinstalls Spotify in the future, SCCM will just uninstall it again!

Spotify SCCM Collection Setup

To create a new SCCM collection, go to the following location:

Assets and Compliance > Overview > Device Collections

Right click on Device Collections and select Create Device Collection:

SCCM Create new Device Collection
SCCM Create new Device Collection

The Create Device Collection Wizard will pop up and you should give your new collection an appropriate name. Also set the limiting collection, which, for this guide purpose I’ll set to All Systems:

SCCM new device collection name and limiting collection
SCCM new device collection name and limiting collection

Then click the Next button to go to the Membership Rules page.

Click on the Add Rule button and from the drop down select Query Rule:

SCCM Add Rule Query Rule
SCCM Add Rule Query Rule

This will pop up the Query Rule Properties box. Give it a name and then click on the Edit Query Statement button:

SCCM Query Rule
SCCM Query Rule

Then click on the Show Query Language button:

SCCM Query Statement Properties
SCCM Query Statement Properties

WQL Query

This is where you now need to copy and paste the following WQL Query:

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 inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "spotify.exe"

Click on the OK buttonand progress to the next page in the wizard.

If you now update your newly created collection, you should see the same amount of devices in the as in your Power BI report:

SCCM Spotify Uninstall Collection
SCCM Spotify Uninstall Collection

Spotify Uninstall Package

Now that we have our SCCM collection automatically updating itself based on the spotify.exe being on an end users device, we now want to uninstall it automatically.

To do this, we will create a package in SCCM and using the standard Spotify uninstall command, push the package to the collection as a required uninstallation.

Create the SCCM Package

Head back to your SCCM console and go to here:

Software Library > Overview > Application Management > Packages

Right click on Packages and click Create Package:

SCCM Create Package
SCCM Create Package

This will bring up the Create Package and Program Wizard. Give your new package and appropriate name and fill out the other details. No need to enter a source at the moment, then click on the Next button:

SCCM Create Package and Program Wizard
SCCM Create Package and Program Wizard

Now on to the next page in the wizard, you should click on the Do not create a program radio button and click Next:

SCCM Package Do Not Create a Program
SCCM Package Do Not Create a Program

Then click through the rest of the wizard and click the Close button at the summary page.

Now open a text editor (like Notepad or Notepad++) and copy and paste the content below:

c:
cd\
cd %appdata%
cd spotify
spotify.exe /UNINSTALL /SILENT

Now save the text file as spotify-uninstall.bat – ensuring that the bat file does not have the .txt file extension at the end.

Now take the spotify-uninstall.bat file and put it in your SCCM package sources location.

Back in your SCCM console, right click on your package and select Create Program:

SCCM Create Package Program
SCCM Create Package Program

This will bring up the wizard page again – click on the Standard program radio button and click the Next button:

SCCM Package Standard Program
SCCM Package Standard Program

Now on the program details, you need to give the program a name, browse to the source of your .bat file for the command line and ensure that it is Run with user’s rights:

SCCM Create Package Program Commands
SCCM Create Package Program Commands

Click the Next button to go to the next page where you can set requirements if you want to, but for the purposes of this guide, I’ll leave the settings as default and move on.

You can then click through and complete the wizard.

The final part is to set the source path, distribute and deploy the newly created package. Right click on the package and select Properties. Then from the tabs at the top of the pop up, select Data Source and tick the box This package contains source files and click the Set button and browse to the location of your .bat file:

SCCM Package Source Information
SCCM Package Source Information

Once you are happy with all your settings, you can click the OK button and then distribute the source package to your distribution points.

Deploy to Collection

Now right click on the package and click Deploy:

Deploy SCCM Package
Deploy SCCM Package

The wizard will appear again and now click the Browse next to the Collection and select the Spotify Uninstall collection you created at the start of this guide:

SCCM Deploy Package to Collection
SCCM Deploy Package to Collection

Then on the Deployment Settings page, ensure that the Purpose drop down is set to Required:

SCCM Deploy Required Program
SCCM Deploy Required Program

You can then go through and complete the wizard.

Conclusion

That is the end of the guide! SCCM should now automatically take care of uninstalling any Spotify installations that your end users put on to their corporate devices!

The software inventory will gather the data if Spotify gets installed at the end users machine. The Power BI report will show you who has installed it. The device will be added to the uninstall collection automatically and then the package deployment will uninstall Spotify from the users machine!

Everything automatted so you don’t have to worry about it any more!

This process can obviously be used for any other programs that you may want to automatically uninstall like iTunes, Dropbox etc.

More Queries

Our full range of SQL and WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.

About A.J. Armstrong

Founder of TechyGeeksHome and Head Editor for over eight years! IT expert in multiple areas for over 21 years. Sharing experience and knowledge whenever possible! Making IT Happen.

View all posts by A.J. Armstrong

Leave us a message...

This site uses Akismet to reduce spam. Learn how your comment data is processed.