Monitoring Microsoft Forefront Client Security

We recently deployed Microsoft Forefront Client Security (FCS) (replacing F-Secure, but that is an other story).

We use Munin for monitoring a lot of other services, both for Linux and Windows. And I wanted to monitor Forefront in Munin as well. FCS has quite good reports using Microsoft SQL Reporting Services. We could use these reports (and will continue to use them), but personally I have to many websites for monitoring stuff, and would like to gather them on one place.

FCS uses MOM 2005, and stores all relevant status in the OnePoint database. That seemed like a good starting point. After some peeking at the OnePoint tables, I was easily able to locate where to find the information needed.

My plan was first to gather number of computers (containing both approved and unapproved clients) and if possible, some kind of "inactive" clients (not reporting for 24 hours seems like a good starting point). Using SQL Server Management Studio, I was able to make three simple SQL queries that gave me that information:
SELECT COUNT(*) FROM Computer WHERE PendingAction = 0 AND
LastHeartbeat < '2009-02-02 11:00:00'
SELECT COUNT(*) FROM Computer WHERE PendingAction = 0 AND
LastHeartbeat >= '2009-02-02 11:00:00'
SELECT COUNT(*) FROM Computer WHERE PendingAction <> 0
That seemed simple, and I should be able to put that in an Perl script and connect to the OnePoint database using ODBC. At least that was my idea. I was able to connect to the SQL Server using FreeTDS (relevant entries from my /etc/freetds/freetds.conf):
host = MyHost.domain.tld
port = 1433
tds version = 7.0
I configured ODBC (I will not explain how to do this and the reason is that I ended up not using ODBC) and used DBD::ODBC, and was happily playing with my new found toy for Munin plugins.

I made a plugin and started fetching data (I did not add the inactive computers at first, but found that useful after about a day of gathering of data). I used the AREA/STACK draw like the memory usage plugin.

The next one, was to do a gathering of MOM alerts (as FCS reports warnings and errors and such there). I quickly wrote this query to use i the config of the plugin:
SELECT Level, Name FROM AlertLevel ORDER BY Level
This gives the following output:
Level       Name
----------- --------------------
10 Success
20 Information
30 Warning
40 Error
50 Critical Error
60 Security Issue
70 Service Unavailable

That was just what I needed. The next was a quite simple query as well:

SELECT al.Level, COUNT(a.AlertLevel) AS cnt
FROM Alert a, AlertLevel al
WHERE a.AlertLevel = al.Level AND a.ResolutionState <> 255
GROUP BY al.Level
This gives me something like this:
Level       cnt
----------- -----------
40 4

I decided to make this graph a percentage graph, and that might not be the best way to represent this information (it is not accurate as well; I calculate the percentage against the total number of computers, but one computer might have more than one alert). I will change this to a plain counter type graph later before submitting it to MuninExchange.

The last thing I wanted to gather, was the deployment status of management policies. And here the fun started. I was not able to find a database table that seemed to contain this information. I was able to find a table named "fcs_Profiles" that seemed to contain the policies:
SELECT Id, Name, LatestInstanceID FROM fcs_Profiles
This give something like this:
Id                                   Name    LatestInstanceID
------------------------------------ ------- ------------------------------------
53C2E807-F9E5-4EF2-A70A-229212E27DBA Default 647A4986-C80F-48BA-A40D-C64F6A591EFB

The Id is an unique identifier for a policy and LatestInstanceID is, as the name of the field says, the latest instance of this policy (will change when you edit and deploy a policy).

But where was the status for the clients? The answer was in the three tables ClassDefinition, ClassAttribute and Attributes. In ClassDefinition, I located rows that had a Name field containing Microsoft Forefront Client Security Agent:

SELECT ClassID, Name, Description
FROM ClassDefinition
WHERE Name = 'Microsoft Forefront Client Security Agent'
ClassID Name Description
------------------------------------ ----------------------------------------- ---------------------
AD3D3E91-336F-4B49-A1A8-EC42CE3F5970 Microsoft Forefront Client Security Agent Client Security Agent

This ClassID is the found in the ClassAttribute table:

SELECT ClassAttributeID, ClassAttributeName, Description
FROM ClassAttribute
WHERE ClassID = 'AD3D3E91-336F-4B49-A1A8-EC42CE3F5970'

ClassAttributeID ClassAttributeName Description
------------------------------------ ------------------------- ----------------------------
48038713-3083-48F9-9664-10DA82739E43 AM last scan time AM last scan time
7DECE2E5-5E7B-4240-A71E-1CDF09F520A6 VA Engine version VA Engine version
3FCAA357-7F6E-469F-A6BD-224012B85BF5 AM Agent VersionAM Agent Version
89AA7658-EB5A-40EE-ACD5-24BDCC2087D9 IP Address IP Address
706C42B2-E9BF-4717-A993-3D4885E21976 Computer Name Computer Name
5A3F899B-1A4B-45D1-A51D-651B28B4C38E AM (AV) Signature Version Signature Version
F01A7C7B-5BD3-4108-BEC6-70AFCD7F5B82 Alert Level Alert Level
77F83DC4-15C2-486E-B7F5-779353D42085 FQDNs Fully qualified domain names
5C916EB0-4D89-4708-A659-8AA472E3B1B8 OS Version OS Version
6FD9202A-AA2B-4923-85DC-94404BF52996 Profile ID Profile ID
0C4C3EB6-8175-4830-925A-96C2DE748589 VA Manifest version VA Manifest version
49CA01C6-79D0-4616-ADE8-A5D6B1B79BDD MAC Address MAC Address
55ADA8AF-A9EA-49A5-805A-AE17D6BB0B53 AM Engine Version Engine Version
F14C3652-A8ED-43AA-A55D-BC1AE4AA77AA VA Agent VersionVA Agent Version
82EA78F2-CFD3-4B6E-9F09-BE0F95A1F0D4 OU OU
765522BC-20C5-4F51-A2AF-CC9A3179979A VA last scan time VA last scan time
74955FE5-114A-43B5-9F4D-EE7084CABB2E AM (AS) Signature VersionSignature Version
430EDF65-EBD8-4526-B396-F53E38903DD6 Profile Instance ID Profile Instance ID

From these ClassAttributeIDs, the one named Profile ID and Profile Instance ID is the one that gives us information about the deployment status.

I put all this together in an query:

SELECT COUNT(*), a1.Value AS Profile, a2.Value AS Instance
FROM Attribute a1, Attribute a2, ClassDefinition cd,
ClassAttribute ca1, ClassAttribute ca2
WHERE ca2.ClassID = cd.ClassID
AND cd.Name = 'Microsoft Forefront Client Security Agent'
AND ca1.ClassAttributeName = 'Profile ID'
AND ca2.ClassAttributeName = 'Profile Instance ID'
AND a1.ClassAttributeID = ca1.ClassAttributeID
AND a2.ClassAttributeID = ca2.ClassAttributeID
AND a1.InstanceID = a2.InstanceID
GROUP BY a1.Value, a2.Value
ORDER BY Profile

Profile Instance
--- ------------------------------------ ------------------------------------
2 53c2e807-f9e5-4ef2-a70a-229212e27dba NULL
377 53c2e807-f9e5-4ef2-a70a-229212e27dba 647a4986-c80f-48ba-a40d-c64f6a591efb
2 53c2e807-f9e5-4ef2-a70a-229212e27dba 74e480b0-c0d8-4e2a-b7cc-2e80b60ed093
3 53c2e807-f9e5-4ef2-a70a-229212e27dba 7a62c7e0-f72e-487b-a344-1b5b295e2759
134 53c2e807-f9e5-4ef2-a70a-229212e27dba dd6f137e-7383-42d0-9200-fa301b01e4a6
11 53c2e807-f9e5-4ef2-a70a-229212e27dba e31f1095-9432-42f1-8c97-0b86c0ef5056
3 D3B75BE9-7125-4DB1-8B24-93004BD9D88E NULL
46 D3B75BE9-7125-4DB1-8B24-93004BD9D88E N/A

So, how to understand this. Well, the first thing, you might notice, is that there is a Policy that was not in the result from the fcs_Profiles query. I have two separate installations for different AD domains, and I found D3B7 5BE9-7125-4DB1-8B24-93004BD9D88E both places. This Profile ID is the "No Policy" profile. Clients that have not yet got their OU/Group/GPO policy will report this policy back to MOM. At initiation of a new client, this Attribute will be initiated with the Value "00000000-0000-0000-0000-000000000000" and this should be treated as an "Unknown Policy". The Instance gives the current version and all other values than the one from the fsc_Profiles query is regarded as old.

I now had all the queries I needed to be able to make a Munin graph, but alas, now the strange things started to happen. The ID fields in the three tables has the data type uniqueidentifier and this seems to be able to hold a very large value. It will in fact be to large for the character data type in DBD::ODBC and will be truncated. DBD::ODBC had two attributes for handling this truncation; LongTruncOk and LongReadLen,
but these seems not to ble implemented in the ODBC/FreeTDS world. This made me change to DBD::Sybase (as this will work with SQL Server using FreeTDS) as this library does hande these long character values correct. No further change was made to the code to make it work.

After a long trip into the OnePoint database, I was finally able to get all the information I needed to graph all I wanted (for this time).

To use this plugin, download it from MuninExchange and copy it to /usr/share/munin/plugins
Install all needed plugins. These packages is needed on Ubuntu:
sudo aptitude install tdsodbc libdbd-sybase-perl
Configure your /etc/freetds/freetds.conf

Run the munin-node-configure command:
$ sudo munin-node-configure --shell
ln -s /usr/share/munin/plugins/forefront_ /etc/munin/plugins/forefront_MyHost.domain.tld_computers
ln -s /usr/share/munin/plugins/forefront_ /etc/munin/plugins/forefront_MyHost
ln -s /usr/share/munin/plugins/forefront_ /etc/munin/plugins/forefront_MyHost
The command might output many other suggestions, you should only run the one you find interesting ("sudo ln -s ....."). Edit your /etc/munin/plugin-conf.d/munin-node:

env.dsn MyHost
env.dbuser <DOMAIN\user>
env.dbpass <password>
The env.dsn is only needed if your freetds.conf has [<dsn>] different than the host definition. User and password can (and should be) a domain user, granted select right for the OnePoint database.

Restart your munin-node.

If the munin-node-configure does not give you any suggestions, you don't have a parsable /etc/freetds/freetds.conf. Make your own symbolic links and restart.

On your munin server, edit your /etc/munin/munin.conf:
address <ip>
use_node_name no

No comments:

Post a Comment