The problem being that that key doesn't exist in all user tables :-)

Also, that approach wouldn't seem to pull up the users who didn't log in through the agent.  

Regards,

Alex Lanstein
Network/Systems Architect
FireEye, Inc.
The Network Malware Control Experts
http://www.fireeye.com
[log in to unmask]




-----Original Message-----
From: Cisco Clean Access Users and Administrators on behalf of Osborne, Bruce W. (NS)
Sent: Mon 9/3/2007 3:31 PM
To: [log in to unmask]
Subject: Re: os count redux
 
Alex,

A computer (laptop) can, and usually does have more that one mac
address. I believe that you can use the "User Key" generated by the
Agent as a value unique to the client computer.

Bruce Osborne
Liberty University


-----Original Message-----
From: Cisco Clean Access Users and Administrators
[mailto:[log in to unmask]] On Behalf Of Lanstein, Alex C
Sent: Monday, September 03, 2007 4:51 PM
To: [log in to unmask]
Subject: [CLEANACCESS] os count redux

I recently had a chance to sit down at a CAM console (well, by SSH) and
do a little testing on the OS counting.  My old establishment now has
two CASes - one OOB and on IB - and wanted to get a proper count of
OSes, taking into account all the special cases I outlined earlier, all
in one query.  Here's the solution I came up with to get as accurate a
count as possible.  It only counts unique mac address/operating system
pairs, and only each mac once.  Sample output is at the bottom.

CREATE TEMPORARY TABLE oscount (mac CHARACTER VARYING(64), os CHARACTER
VARYING(64)); INSERT INTO oscount SELECT DISTINCT user_mac,os_name FROM
user_info WHERE login_time > (SELECT EXTRACT(epoch from
current_timestamp) * 1000 - (60 * 60 * 24 * 30 * 2)); INSERT INTO
oscount SELECT DISTINCT dm_mac,dm_os FROM dm_report WHERE
current_timestamp - report_time < INTERVAL '2 months' AND dm_mac NOT IN
(SELECT mac FROM oscount); INSERT INTO oscount SELECT DISTINCT
mac,os_name FROM oob_user_info WHERE current_timestamp - login_time <
INTERVAL '2 months' AND mac NOT IN (SELECT mac FROM oscount); SELECT
os,count(*) FROM oscount GROUP BY os ORDER BY os; DROP TABLE oscount;

I'm using the user_info, oob_user_info, and dm_report tables to grab the
information.

Notes

1) You'll notice the funky timestamp of user_info.  It appears to be
seconds since the epoch to 3 degrees of precision to the right of the
decimal point.  I simply multiplied the unix timestamp * 1000 to get to
it.  It won't be more than a second off in either direction.

2) The way I'm doing the uniquing based on the mac address is pretty
inefficient.  The way it should be done is by adding a constraint onto
the table (CONSTRAINT unique_macs_only UNIQUE(mac)).  The problem with
doing it this way is that the query bombs out when the constraint fails
(when it comes across a duplicate mac).  There's probably a way to make
it skip over the insert, but I couldn't find it.  Only adds a second or
so of overhead.

3) My storage of the mac address isn't optimal.  it should obviously be
of type macaddr, but in pgsql you cant compare values of different
types, and thats how the CAM stores them.  It's a temporary table though
so it really doesn't matter anyways.

Hope this helps someone!

Regards, 

Alex Lanstein 
Network/Systems Architect
FireEye, Inc.
The Network Malware Control Experts
http://www.fireeye.com
[log in to unmask]


[root@smmanager ~]# psql -h 127.0.0.1 -U postgres controlsmartdb
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

controlsmartdb=#  CREATE TEMPORARY TABLE oscount (mac CHARACTER
VARYING(64), os CHARACTER VARYING(64)); INSERT INTO oscount SELECT
DISTINCT user_mac,os_name FROM user_info WHERE login_time > (SELECT
EXTRACT(epoch from current_timestamp) * 1000 - (60 * 60 * 24 * 30 * 2));
INSERT INTO oscount SELECT DISTINCT dm_mac,dm_os FROM dm_report WHERE
current_timestamp - report_time < INTERVAL '2 months' AND dm_mac NOT IN
(SELECT mac FROM oscount); INSERT INTO oscount SELECT DISTINCT
mac,os_name FROM oob_user_info WHERE current_timestamp - login_time <
INTERVAL '2 months' AND mac NOT IN (SELECT mac FROM oscount); SELECT
os,count(*) FROM oscount GROUP BY os ORDER BY os; DROP TABLE oscount;
CREATE TABLE
INSERT 0 52
INSERT 0 1385
INSERT 0 31
             os             | count
----------------------------+-------
 LINUX                      |     3
 MAC_OSX                    |    56
 WINDOWS_2K                 |     3
 WINDOWS_95                 |     1
 WINDOWS_MCE_XP             |    96
 WINDOWS_PRO_XP             |  1039
 WINDOWS_TPC_XP             |    13
 WINDOWS_VISTA_BUSINESS     |    14
 WINDOWS_VISTA_ENTERPRISE   |     2
 WINDOWS_VISTA_HOME_BASIC   |    38
 WINDOWS_VISTA_HOME_PREMIUM |   119
 WINDOWS_VISTA_ULTIMATE     |    85
(12 rows)

DROP TABLE
controlsmartdb=#