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!
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!
First things first. In your CM database there is a table called:
In here there is all the information about your users requests include a column called:
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:
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.
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.
If you want to be notified when we post more quality guides like this one, sign up to our newsletter and you will receive an email when a new post is live.
No need to worry, we will not be filling your inbox with spam and you can unsubscribe anytime you like.