Some Interesting FCS SQL Queries

With a recent case I have an issue where the client count of managed computers in MOM admin console was quite different then that in the FCS console so I was trying to find out exactly which computers were not in FCS so I could troubleshoot some of those more effectively.  The first thing I checked was looking at Agent-Managed Systems in MOM admin just to make sure these were not computers that had not been checking in for a while (they weren’t all had heartbeats within last day or two) as I know we drop off computers from our total count in the FCS console that have not checked in in 30 days.  Since this was not the case I was pointed out that it may be an issue with clients having the MOM agent but not actually having SSA or AM installed on them or corrupted in some way.  Unfortunately I didn’t know a good way to check on this in MOM so I had to do some digging around to create some queries to make this happen,  below are the results I have:

This one is the easiest but just dumps a list of all computers in MOM, make sure you select to use the OnePoint db when you start a New Query in SQL Server Management Studio

Select Name from Computer

This next one selects all the computers that have FCS installed on them

select Computer as FCSCLIENTS from computertocomputerruleview where [Rule] like '%Microsoft Forefront Client Security Agents%' order by FCSCLIENTS

This one does a subquery of total computers against computers that have FCS on them to give us a list of computers that for some reason do not have FCS on them.

select distinct Name as MISSINGFCS from Computer where Name not in (Select Computer from computertocomputerruleview where [Rule] like '%Microsoft Forefront Client Security Agents%')

Hopefully this helps someone else as well.. I’ll hopefully add to this over time if I find anything more that’s useful.

Update 7/10/09 Adding some more based on a request from a blog reader.  Customer looking for a way to list all detection events. You could actually play around with this some  as this View does have events from other sources as well as FCSAM.

SELECT [LoggedOn]
      ,[LoggedOnDomain]
      ,[Source]
      ,[Evtime]
      ,[Eventno]
      ,[Evttext]
  FROM [OnePoint].[dbo].[EventView] where source like '%FCSAM%'
and ([Eventno]='1006' or [Eventno]='1007' or [Eventno]='3004' or [Eventno]='3005') order by [Evtime]

You could also do some filtering by date by adding an extra “and ([Evtime]>='2009-06-10' and [Evtime]<='2009-06-13')” statement to this before the order by statement. 

Just as an FYI tables/views/db schema of FCS/MOM are not documented/supported in any way so if any of these break with some update :) :) that’s life.