SCCM Query – Report on Distribution Point Statuses

sccm logo

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.

SQL Query

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

Feedback

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

SCCM – Report on Outlook Archive PST files on Devices

sccm logo 600x400

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:

SCCM - Report on Outlook Archive PST files on Devices 1

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

SCCM - Report on Outlook Archive PST files on Devices 2
You should then click on the add button and add the following:

 *.pst  

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   
      Sys.Name0,  
      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  
 FCM.ResourceID=sys.ResourceID  
      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!

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.

Ultimate Settings Panel Released

USP Home

Please note that version 2.6 is now available by clicking the download button below. 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. DOWNLOAD You can … Read more

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

sql2016

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.

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_COMPUTER_SYSTEM
       on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
    where SMS_G_System_COMPUTER_SYSTEM.Name in
       (select distinct
        SMS_G_System_COMPUTER_SYSTEM.Name
    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

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.

Windows 7 Lock Screen Changer v1.2 Released

w7lscscreenshot

Please note that version 1.5 of Windows 7 Lock Screen Changer is now available using the link below:

Windows 7 Lock Screen Changer version 1.5

We are pleased to announce the updated release of our popular utility – Windows 7 Lock Screen Changer.

You can download the latest version by clicking the download button below:

DOWNLOAD

Windows 7 Lock Screen Changer v1.2 Released 12

INSTRUCTIONS

The latest version has a few changes to it taking it to version 1.2. Although the layout is slightly different in v1.2, the functionality is exactly the same as version 1.1 with full instructions on how to use it below:

VERSION CHANGES

The changes included in this release are:

  • Revert functionality added – allowing you to revert back to the original Windows default lock screen
  • Layout change – slightly changed layout and we have changed the menu to a button based menu
  • Bug fixes – minor bugs in the code have been fixed
  • Code tidy – boring back end stuff making it easier for us to update for future releases

SCREENSHOT

The new screen shot of the new version is below:

w7lscscreenshot
As you can see from the screen shot, the status text is now at the top, our logo has been added and background colour slightly changed to make it less boring looking and then menu buttons are at the bottom with the newly added function for Revert.

COMMENTS

If you have any questions, bug reporting or suggestions for future releases, then please leave your comments below using our comments system.

Adobe Flash Player 14.0.0.176 Released – MSI Download

Adobe Flash Player MSI Package

Adobe have released another update to their Adobe Flash Player software, this time taking it up to version 14.0.0.146.

As always, you can download the executable file or get the MSI file so that you can load it into your Configuration Manager Software Catalog.

The update is also now available to import through System Center Update Publisher (SCUP) so you can inject it into your WSUS or Configuration Manager systems.

You can the download in all formats for all browsers for Windows and Mac directly from Adobe by clicking the download button below.

DOWNLOAD

Adobe Flash Player 14.0.0.176 Released - MSI Download 13

COMMENTS

If you have any comments or questions, please use our comments system below.

Java MSI Download Files for Internet Explorer and Google Chrome

Java-MSI-Installers

We have had a lot of people asking us about how to get the MSI files for the Java Clients and we have written a few popular guides on how to do this, however there are still a large number of people of there that just want the files to download with having to mess about extracting it themselves.

So, just for you, we have downloaded, extracted and reuploaded to our Java microsite, the following Java Client MSI packs:

  • Java Client for Internet Explorer x86 (Windows)
  • Java Client for Internet Explorer x64 (Windows)
  • Java Client for Google Chrome (Windows)

Feel free to download the pack using the button below:

DOWNLOAD

Java MSI Download Files for Internet Explorer and Google Chrome 14

UPDATE

Update added 26th April 2015 – Please be aware that it seems that the MSI extraction does not always work with the newer versions of Java, so please see the guide below which shows you how to setup a silent install using the standard offline installer:

Java Silent Installation Guide

COMMENTS

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

Viewing WEBP images in Windows Photo Viewer

microsoft black logo

You may have started to come across image files that are in WEBP forma on the internet recently. These are usually opened using Google Chrome but this can be very annoying if you have saved a number of these images and want to browse through them using Windows Photo Viewer instead of having to open them one by one.

Well, there is a solution. You can download the WEBP Codecs which will then allow you to associate WEBP files with Windows Photo Viewer and use them just like JPG’s.

You can download the WEBP Codecs from our SourceForge site by clicking the link below:

Downloads

Comments

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

VLC Player – 2.1.5 Released

VLC Player 2.1.5 has been released and is probably the best media player on the internet.

VLC Player is a free and open source cross-platform multimedia player and framework that plays most multimedia files as well as DVDs, Audio CDs, VCDs, and various streaming protocols. This is basically your all in one media centre and will work on almost every operating system available.

DOWNLOAD

You can download VLC Player 2.1.5 by clicking the Download button below:

VLC Player - 2.1.5 Released 15

FEATURES

Simple, fast and powerful media player.

Plays everything: Files, Discs, Webcams, Devices and Streams.

Plays most codecs with no codec packs needed: MPEG-2, DivX, H.264, MKV, WebM, WMV, MP3…

Runs on all platforms: Windows, Linux, Mac OS X, Unix…

Completely Free, no spyware, no ads and no user tracking.

Can do media conversion and streaming.

FileZilla 3.9.0.2 – Packaged MSI Installer Released

FileZilla FTP

We use FileZilla a lot and we wanted an MSI installer for it. However, we could not seem to find one anywhere and FileZilla themselves do not supply one. So, we decided to create one ourselves. Depending on how popular this proves, if it is worth our time we will keep up to date with … Read more

Configuration Manager 2012 – How to Capture Task Sequence Error Logs to a Network Share

sccm logo 600x400

We have recently been doing some big OSD projects and with testing for such big projects comes the point where some of your testing will fail, so to find out why a test sequence fails, the first thing you should look at is your log files.

However, ConfigMgr makes this an annoying process as the log files move about during different points in the task sequence. So, when it does fail, you have to look in different areas, then pull out the log files and then go through them to find the error.

Well, we knew that we could capture the log files upon failure during the task sequence and copy them to a network share so that we had one location to aim at. To do this, we found an excellent walk through guide over at System Center Guru so we thought that we would share this with you.

So, click the link below and see how you can capture your log files as part of a task sequence when you get a failure.

How to Capture Task Sequence Error Logs to a Network Share

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

Java 7 Update 67 Released – MSI and Offline Installer

Java have now released update 67 of version 7 of their Java Client software.

The update is available as an offline installer and using our guide below, you can extract the MSI from it and use it for deployment by using Windows Updates to update current machines or add to your Configuration Manager Application Catalog or even deploy using Group Policy.

You can download the Offline Installer from the Java website using the link below:

DOWNLOAD

Java 7 Update 67 Released - MSI and Offline Installer 16

INSTRUCTIONS

By following the guide below, you can extract the MSI installer to use in deployment and Windows Updates to automatically update machines using WSUS.

How to extract Java MSI File from the Offline Installer

UPDATE

Update added 26th April 2015 – Please be aware that it seems that the MSI extraction does not always work with the newer versions of Java, so please see the guide below which shows you how to setup a silent install using the standard offline installer:

Java Silent Installation Guide

COMMENTS

If you have any questions about this or wish to leave a comment, please use our comments system below and we will get back to you as soon as we can.