Menu
CCMEXEC.COM – Enterprise Mobility
  • Home
  • General
  • Configuration Manager
  • Windows 10
  • Intune
  • GitHub
  • Windows 11
  • About the author
CCMEXEC.COM – Enterprise Mobility

Using SCCM Dashboard to display Forefront Endpoint Protection info

Posted on February 13, 2011 by Jörgen Nilsson

I got an idea to use the SCCM Dashboard to present the status from the Forefront Endpoint Protection 2010 . The FEP Dashboard in the SCCM console is based on Collection membership so I opened SQL Management Studio to get the SQL queries for the Collection Membership and use them i the SCCM Dashboard.

However the queries are really complex so instead of using them I got the idea to write a really simple SQL query just to pull the members from different Collections and present the collection membership in the SCCM Dashboard.(Thanks my colleague Ola for being a SQL “WIZ”)
This can be used by any administrator as it is much easier to create a collection query rule using the console than writing a query in the Dashboard GUI.
The Dashboards below reflect the Collection membership for the collections used by FEP.

fepdash1fepdash2

The SQL queries used for the Policy Distribution Status, replace the collections ID with the collections ID from you environment and just simply create a chart for each query:

Policy Distribution Status:

SELECT ‘Distributed’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000020’
UNION
SELECT ‘Pending’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000022’
UNION
SELECT ‘Failed’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000021’

Definition Status:

SELECT ‘Up To Date’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000024’
UNION
SELECT ‘Up To 3 Days Old’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000025’
UNION
SELECT ‘Up To 7 Days Old ‘ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000026’
UNION
SELECT ‘Older Than 1 Week’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000027’

Security Status:

SELECT ‘Infected’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000029’
UNION
SELECT ‘Restart Required’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘0200002A’
UNION
SELECT ‘Full Scan Required’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘0200002B’
UNION
SELECT ‘Recent Malware Activity’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘0200002C’

Protection Status:

SELECT ‘Healthy’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘02000030’
UNION
SELECT ‘Not Reporting’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘0200002F’
UNION
SELECT ‘Protection Status Off’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘0200002E’

For the recent Malware Activity create a data grid.

Recent Malware Activity:

SELECT Count(ResourceID) AS ‘Count’
from v_FullCollectionMembership where CollectionID = ‘0200002C’

8 thoughts on “Using SCCM Dashboard to display Forefront Endpoint Protection info”

  1. Ron says:
    September 21, 2011 at 10:48 pm

    This seems like a very simple solution. However you negelected to include the SQL behind the view v_FullCollectionMembership. To even test this solution it would be necessary.

    Reply
  2. Ron says:
    September 22, 2011 at 2:34 pm

    Resolved my delima: My ah-ha moment came while doing more research and reading a different blog. These queries run well you just need to in the SMS database.

    Reply
  3. Dana says:
    October 3, 2011 at 9:33 pm

    Hello, very interesting read, I have tried several custom Queries I have found online which have some great ideas, unfortunately with my SCCM 2007 R3 none work. With yours here, i went to queries, new, System resource and I pasted in your SQL. The wizard complains about a syntax error. I tried the first 2 here. If you have any guidance, I would appreciate it.
    Regards
    Dana

    Reply
    1. Jörgen Nilsson says:
      October 4, 2011 at 10:10 am

      Hi,
      check that all characters are copied correctly when you copy the queries. Otherwise they should work fine.
      /Jörgen

      Reply
  4. Dana says:
    October 4, 2011 at 5:03 pm

    Jorgen, I am creating these in Queries and not elsewhere correct? You have edited the post to Ron’s comment above as well? IS there some place i can send a screen shot if the error persists?

    Thanks
    Dana

    Reply
  5. Tony says:
    January 18, 2012 at 1:18 pm

    The wizard complains about a syntax error. I tried the all here. Have mentioned the corect clooection ID as well.. Any thoughts

    Reply
  6. Julio Soto says:
    January 23, 2012 at 9:14 pm

    what would be the query to deploy (pending, failed, deployed, removed, up date)?

    Reply
  7. Mike says:
    February 23, 2012 at 5:35 pm

    Guys, try the SQL query in management studio, more than likley the cut and paste has bad single quote characters. Also the deployment status query looks like this:

    SELECT ‘Locally Removed’ AS [Deployment status], Count(ResourceID) AS [# of systems] from v_FullCollectionMembership where CollectionID = ‘COLLID’
    UNION
    SELECT ‘Failed’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘COLLID’
    UNION
    SELECT ‘Pending’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘COLLID’
    UNION
    SELECT ‘Out of Date’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘COLLID’
    UNION
    SELECT ‘Deployed’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘COLLID’
    UNION
    SELECT ‘Not Targeted’ AS [Value Name], Count(ResourceID) AS [Value] from v_FullCollectionMembership where CollectionID = ‘COLLID’

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

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

My name is Jörgen Nilsson and I work as a Senior Consultant at Onevinn in Malmö, Sweden. This is my blog where I will share tips and stuff for my own and everyone elses use on Enterprise Mobility and Windows related topics.
All code is provided "AS-IS" with no warranties.

Tweets by ccmexec

Recent Posts

  • Windows Servicing, Personal Teams and Success.cmd
  • Windows MDM Security Baseline – Settings Catalog
  • Configuring MS Edge Security Baseline v107 using Settings Catalog
  • Configuring Desktop App Installer using CSP and script?!
  • Customizing Taskbar and Start in Windows 11 22h2 with PowerShell

©2023 CCMEXEC.COM – Enterprise Mobility | WordPress Theme by Superb Themes
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept Reject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT