Configuration Manager – Change the Approve/Deny Status of an Application Catalog Request

TGH Profile Image 512x512

If you are a Configuration Manager administrator, the chances are that you are using the Application Catalog to allow users to install their own software.

A great feature of this is that you can put in specialist or licensed software and add a “Request” button. This means that your end users will only get the software if you approve it.
To begin with we found a number of bits missing for this though, things like email notification – how are you supposed to know when a user requests some software – how is the user supposed to know when you have approved it?
Those issues are easily solved using Powershell and Orchestrator Runbooks but that is for another time….
In this post, we are going to show you how to do something very dangerous indeed – if you use this method and screw up your Configuration Manager database – DO NOT BLAME US!

Scenario

We came across an issue where a helpdesk technician had approved and denied software incorrectly. By default in the Configuration Manager Console, once you have approved or denied a request – that’s it, you cannot change your mind by simply right clicking and amending your selection. So, how to do work around it? The answer is very simple – you manually amend your SQL database.
Now, as stated above, this is extremely stupid and we are not recommending that you do it, but if you are desperate and an absolute expert in SQL and are confident in your own ability – then why not!

Instructions

First things first. In your CM database there is a table called:
 UserApplicationRequests  

In here there is all the information about your users requests include a column called:

 CurrentState  

There are four numbers that make up the current state of a user application request – they are:

  • 1 – Requested
  • 2 – Cancelled
  • 3 – Denied
  • 4 – Approved

So, you can now run a query on your CM database like this to show you all Denied user approval requests:

 SELECT * FROM UserApplicationRequests WHERE CurrentState = 3  
 ORDER BY LastChanged  

In our example, we are going to use an extreme – we want to change ALL of our Denied CurrentStates back to Requested – then we can go into the Configuration Manager Console and manually approve or deny them.

To this, you can run the following query on your CM database:

 UPDATE UserApplicationRequests  
 SET CurrentState= 1  
 WHERE CurrentState = 3  

What this query does is updates the UserApplicationRequests table to make CurrentState = 1 where the CurrentState is = 3.

If you now go into your Configuration Manager Console, you should see that all your Denied requests are now back into a Requested state.

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.


You might also like...

Leave us a message...

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