Using SCCM Dashboard to display Forefront Endpoint Protection info

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 Comments

Add a Comment

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