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.
Update 2/1/2010 Adding another interesting one. This one returns a list of computer names as well as the time that their last AV scan finished. Unfortunately it does not differentiate between Quick vs Full scan for this value that is collected but you could probably figure it out based on what your policy is supposed to be for those systems.. IE if your policy says full scan at 1am and you see scan finished at 1:44am this is probably your full scan finishing.
use OnePoint
Select Name, DATEADD(day, -2, CAST( CAST (Value as float) AS datetime)) as TimeLastScanFinished
from Attribute INNER JOIN Computer ON DISCOVERYCOMPUTERID = IDCOMPUTER
WHERE ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'AM last scan time')
AND IDComputer IN (Select DiscoveryComputerID from Attribute WHERE
ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'AM last scan time'))
order by value, name
Update 2/1/2010 Realized had a customer question below as well from a while back I never addressed. Below query should return a list of all computers and their AM signature versions.
use OnePoint
Select Name, Substring(Value,20,20) as AMSignatureVersion
from Attribute INNER JOIN Computer ON DISCOVERYCOMPUTERID = IDCOMPUTER
WHERE ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'AM (AV) Signature Version')
AND IDComputer IN (Select DiscoveryComputerID from Attribute WHERE
ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'AM (AV) Signature Version'))
order by value, name
Update 9/22/2010 few more queries the first is for AM Engine versions
Select Name, Value
from Attribute INNER JOIN Computer ON DISCOVERYCOMPUTERID = IDCOMPUTER
WHERE ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'AM Engine Version')
AND IDComputer IN (Select DiscoveryComputerID from Attribute WHERE
ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'AM Engine Version'))
order by value, name
This second one is for AM Client versions
Select Name, Value
from Attribute INNER JOIN Computer ON DISCOVERYCOMPUTERID = IDCOMPUTER
WHERE ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'AM Agent Version')
AND IDComputer IN (Select DiscoveryComputerID from Attribute WHERE
ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'AM Agent Version'))
order by value, name
Disclaimer…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.