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.
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’
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.
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.
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
Hi,
check that all characters are copied correctly when you copy the queries. Otherwise they should work fine.
/Jörgen
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
The wizard complains about a syntax error. I tried the all here. Have mentioned the corect clooection ID as well.. Any thoughts
what would be the query to deploy (pending, failed, deployed, removed, up date)?
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’