The drop table didn't make me nervous.

It was whether to include the output from the command.  (it wasn't clear
if it was output or more commands until your follow up email)

Mike


-----Original Message-----
From: Cisco Clean Access Users and Administrators
[mailto:[log in to unmask]] On Behalf Of Lanstein, Alex C
Sent: Wednesday, September 05, 2007 1:20 PM
To: [log in to unmask]
Subject: Re: os count redux

Correct.  Although you want to keep the DROP TABLE at the end.  I create
a temporary table at the beginning of the statement.  Normally the
table, and the space used, wouldnt be freed until the service is
sighup'd.  It's a trivial thing either way.  If DROPing something makes
you nervous, just don't do it and it will get cycled out the next time
the box is restarted :-)

I'd run it a couple times during the day to get the most accurate
readings.  Depending on your CCA setup, the data gets phased out at
different points (in band/oob, how long you keep them certified, etc)

Also remember that this query shows the users for the last two months
only, nothing older.  I did it that way because we rarely wiped out our
CAM - it always seemed like we retained all sorts of ancient data :-)

acl

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 King,
Michael
Sent: Wed 9/5/2007 7:34 AM
To: [log in to unmask]
Subject: Re: os count redux
 
So Basically, If I just pasted the following SQL query (up to the DROP
TABLE oscount;) it will just print out that list?

I'm being asked to product a list like below.  I'm just not an SQL guy.

Mike

-----Original Message-----
[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=#