Views: counter
Tuesday, 2 September 2014

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

If you are a Configuration Manager 2012 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:

 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.


If you have any questions or comments on this, please feel free to use our Disqus system below.

Views: counter
Tuesday, 26 August 2014

Configuration Manager - Report on Distribution Point Statuses

In Configuration Manager 2012, there is a section under the Monitoring ribbon called Distribution Status. This will give you a lot of information about your distributions and your distribution points.

If you look at Distribution Point Configuration Status, this gives you a good understanding of whether your drivers, packages, applications and images have deployed correctly to your local or remote distribution points.

This is great information - if you have the console, however, what if you want your administrators to monitor this for you but they do not have access to the console, or if you simply do not want to keep having to go into the console? Well, you can create a report to replicate this information. This way, you can give anyone access to the report and they can see all this information through a web browser.

To do this, you will need a script to create your report. You can modify this script if you like for your own needs or just use as is:

 select UPPER  
 (SUBSTRING(PSD.ServerNALPath,13,CHARINDEX('.', PSd.ServerNALPath) -13)) AS [DP Name],  
 count(*) [Targeted] ,  
 count(CASE when PSD.State='0' then '*' END) AS 'Installed',  
 count(CASE when PSD.State not in ('0') then '*' END) AS 'Not Installed',  
 round((CAST(SUM (CASE WHEN PSD.State='0' THEN 1 ELSE 0 END) as float)/COUNT(psd.PackageID ) )*100,2) as 'Success%',  
 psd.SiteCode [Reporting Site]  
 From v_PackageStatusDistPointsSumm psd,SMSPackages P  
 where p.PackageType!=4   
 and (p.PkgID=psd.PackageID)  
 group by PSd.ServerNALPath,psd.SiteCode  


If you have any questions or comments on this, please feel free to use our Disqus system below.

Views: counter
Monday, 25 August 2014

Configuration Manager - Report on Outlook Archive PST files on Devices

If you want to report on machines that have Outlook archive (PST's) on them, where they are and how big they are, then you can use this guide to create a report that will do this for you using Configuration Manager.

First of all, you will need to tell your Software Inventory to look for PST files on machines. To do this, go into your Configuration Manager Console and browse to Administration > Client Settings and then edit your Device Settings for where you want to collect the PST information from.

When you are in the edit screen for your machine settings, on the left hand side, select Software Inventory:

Then on the right side, click the Set Types button next to the Inventory these file types option:

You should then click on the add button and add the following:


You can set your own location settings depending on your own environment.

Once you have done this, you can move onto creating the report using SSRS, however, please remember that you will not see any information in your report until your clients have got the new client settings policy that you have just amended and reported back to your management point to populate your Configuration Manager database. How quickly this happens depends on the frequency that you have your Software Inventory setup as.

Moving onto the report, the query script that you should use is below:

 select distinct   
      SF.FileName AS [Filename],  
      SF.ModifiedDate AS [Last Modified Date],  
      SF.Filepath AS [Local Filepath],  
      SF.FileSize/1024 as [Size (MB)],  
      SF.FileSize/1024000000 as [Size (GB)]  
 from v_R_System Sys   
      INNER JOIN v_GS_SoftwareFile SF on  
 Sys.ResourceID = SF.ResourceID  
      INNER JOIN v_FullCollectionMembership FCM on  
      where SF.FileName like '%'+'.pst'  
      Order by SF.FileName  

Once you have created your report and added the Software Inventory to search for PST files, you should start to see data pouring into your report giving you all the information you require about PST files on users devices!


If you have any comments or questions about this guide, please feel free to use our Disqus system below.

Views: counter
Sunday, 24 August 2014

Ultimate Settings Panel Released

We are pleased to announce the release of our new utility, Ultimate Settings Panel.

We have designed it to be an all in one settings solution for a multitude of configuration options in Windows, Office and Configuration Manager.


You can download Ultimate Settings Panel by clicking the SourceForge button below:


This is a first release and we plan to add many more features to it in future releases, but for now, we have covered the following systems:
  • Windows XP, Vista, 7 & 8
  • Outlook 2010
  • Configuration Manager 2007
  • Configuration Manager 2012
As it is a first release, there may be some bugs in it or some things that may not quite be perfect - the only way we will fix these bugs and add the extras that you want is if you tell us - so please feel free to contact us or use our Disqus system at the bottom of this post. If you have any good ideas for what we can do to this utility in future releases then we will take them on board and add them if possible.


Ultimate Settings Panel is a tab based utility giving you the option of selecting from a wide range of different configuration options as you can see from the screen shots below:

So all you have to do is select a tab and click the button for the configuration item that you wish to use. This program also includes Windows God Mode built into it for extra configuration items.


Download PAD File Here


If you have any questions, comments, suggestions or bug reports, please use our Disqus system below.

Views: counter
Saturday, 23 August 2014

WQL Query for Devices with Configuration Manager Client Installed and no System Center Endpoint Protection Installed

We have had a report that one of our WQL queries has not been working correctly, this is the one for devices that have Configuration Manager client installed but do not have System Center Endpoint Protection installed on them.

The original post is here:

WQL Query for Devices without Endpoint Installed

So, we have looked into this and found the fault, we have now updated the original post, but in case you want quick access to it, below is the updated and confirmed working WQL query.

 from SMS_R_System
    inner join SMS_G_System_COMPUTER_SYSTEM
       on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
    where SMS_G_System_COMPUTER_SYSTEM.Name in
       (select distinct
    from  SMS_R_System
        inner join SMS_G_System_COMPUTER_SYSTEM
    on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
        inner join SMS_G_System_ADD_REMOVE_PROGRAMS
    on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
        where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "System Center 2012 Endpoint Protection" )
    and SMS_R_System.Client = 1 

This query can now be used to create a collection then you can deploy System Center Endpoint Protection to this collection to ensure all devices within the collection then get Endpoint installed.

To find out how to add System Center Endpoint Protection as an application to your Configuration Manager so that you can deploy it to a collection, then please see the post below:

How to add System Center Endpoint Protection as an Application in Configuration Manager


If you have any comments or questions or if you do find a problem with this query, please feel free to leave a comment below using our Disqus system and we will get back to you as soon as we can.