Kurt,
That might be, we are running 4.1 It might have been added for some
of the audit functionality. Do you have the dm_report_soft table?
If so, you might be able to run the second one.
Here is a cheatsheet I created for my work on the reports part of the
db. Note the descriptons on the right hand side.
dm_report: 1 entry per report
CREATE TABLE dm_report
(
report_id int4 NOT NULL, (used to link dm_report, dm_report_av,
and dm_report_soft)
report_ver varchar(64), (Always 1000)
report_enc int4, (Always 0)
dm_key varchar(255), (unique alpha-num string)
dm_ip varchar(64), (IP address)
dm_mac varchar(64), (MAC address)
dm_user varchar(255), (User ID)
dm_os varchar(64), (Operating System)
dm_agent varchar(64), (CCA Agent version)
report_text text, (html text of report)
report_time timestamp(0), (YYYY-MM-DD hh:mm:ss)
report_status int2, (1 for pass, 0 for fail)
CONSTRAINT dm_report_pkey PRIMARY KEY (report_id)
)
WITHOUT OIDS;
ALTER TABLE dm_report OWNER TO postgres;
---------------------
dm_report_av: 1 entry per AV/AS found
CREATE TABLE dm_report_av
(
dmr_av_id int4 NOT NULL, (unique id)
report_id int4 NOT NULL, (report_id, from dm_report and
dm_report_soft)
av_type varchar(64), (AV or AS)
av_prod_id varchar(255), (Product ID, e.g. NortonAV)
av_prod_name varchar(255), (Full product name, e.g. Symantec
AntiVirus)
av_prod_ver varchar(255), (Product version number, e.g. 9.0.1.1000)
av_def_ver varchar(255), (Product definition version, e.g.
3/11/2007 rev. 21 or 1.15.2336.7)
av_def_date varchar(255), (Product definition date, format varies
by product. Sometimes mm/dd/yyyy)
CONSTRAINT dm_report_av_pkey PRIMARY KEY (dmr_av_id)
)
WITHOUT OIDS;
ALTER TABLE dm_report_av OWNER TO postgres;
------------------------
dm_report_soft; multiple entries per report
CREATE TABLE dm_report_soft
(
dmr_soft_id int4 NOT NULL, (unique id)
report_id int4 NOT NULL, (report_id, from dm_report, dm_report_av)
soft_id int4 NOT NULL, (soft_id, from dm_soft_info)
soft_status int4, (1 = pass, 0 = fail)
CONSTRAINT dm_report_soft_pkey PRIMARY KEY (dmr_soft_id)
)
WITHOUT OIDS;
ALTER TABLE dm_report_soft OWNER TO postgres;
--------------------------
dm_soft_info; requirement lookup table
CREATE TABLE dm_soft_info
(
soft_id int4 NOT NULL, (unique id, referenced by
dm_report_soft.soft_id)
soft_type int2, (Type of requirement; 2 = link, 3 = local, or 4
= AV definitions)
soft_name varchar(255), (Requirement name)
soft_path varchar(255), (link value if soft_type = 2)
soft_version varchar(64), (unknown; blank or 1)
soft_req_os varchar(255), (Requirement OS conditions)
soft_comment text, (Requirement comment text)
soft_web_text text, (blank)
soft_optional int2, (2 if audit requirement, else 0)
soft_byte_size int8, (0)
soft_priority int4, (Requirement priority)
CONSTRAINT dm_soft_info_pkey PRIMARY KEY (soft_id)
)
WITH OIDS;
ALTER TABLE dm_soft_info OWNER TO postgres;
Michael Grinnell
Network Security Administrator
The American University
On Apr 28, 2007, at 10:27 AM, [log in to unmask] wrote:
> Michael,
>
> I think this looks great -- but my CAM does not have the
> dm_report_av table. We are running CAM 4.0.4; I suspect this is a
> 4.1.x feature?
>
> Kurt
>
>
> Quoting Michael Grinnell <[log in to unmask]>:
>
>> Kurt,
>>
>> Here is a general AS audit. It selects the newest report by MAC
>> address, restricted by IP if you wish, then displays the AS product
>> name, version, and def date for each report.
>>
>> -- AS audit report
>> select a.dm_user as "User ID", a.dm_mac as "MAC Address",
>> z.max_report_time as "Report Time", b.av_prod_name as "AS Product",
>> b.av_prod_ver as "AS Version", b.av_def_date as "Spyware Definition
>> Date"
>> from dm_report a, dm_report_av b,
>
|
