Common event format (CEF) data
Use the flex parser fcefparser to load OpenText ArcSight or other Common Event Format (CEF) log file data into columnar and flexible tables.
When you use the parser to load arbitrary CEF-format files, it interprets key names in the data as virtual columns in your flex table. After loading, you can query your CEF data directly, regardless of which set of keys exist in each row. You can also use the associated flex table data and map functions to manage CEF data access.
Create a flex table and load CEF data
This section uses a sample set of CEF data. All IP addresses have been purposely changed to be inaccurate, and Return characters added for illustration.
To use this sample data, copy the following text and remove all Return characters. Save the file as CEF_sample.cef, which is the name used throughout these examples.
CEF:0|ArcSight|ArcSight|6.0.3.6664.0|agent:030|Agent [test] type [testalertng] started|Low|
eventId=1 mrt=1396328238973 categorySignificance=/Normal categoryBehavior=/Execute/Start
categoryDeviceGroup=/Application catdt=Security Mangement categoryOutcome=/Success
categoryObject=/Host/Application/Service art=1396328241038 cat=/Agent/Started
deviceSeverity=Warning rt=1396328238937 fileType=Agent
cs2=<Resource ID\="3DxKlG0UBABCAA0cXXAZIwA\=\="/> c6a4=fe80:0:0:0:495d:cc3c:db1a:de71
cs2Label=Configuration Resource c6a4Label=Agent
IPv6 Address ahost=SKEELES10 agt=888.99.100.1 agentZoneURI=/All Zones/ArcSight
System/Private Address Space
Zones/RFC1918: 888.99.0.0-888.200.255.255 av=6.0.3.6664.0 atz=Australia/Sydney
aid=3DxKlG0UBABCAA0cXXAZIwA\=\= at=testalertng dvchost=SKEELES10 dvc=888.99.100.1
deviceZoneURI=/All Zones/ArcSight System/Private Address Space Zones/RFC1918:
888.99.0.0-888.200.255.255 dtz=Australia/Sydney _cefVer=0.1
-
Create a flex table
logs:=> CREATE FLEX TABLE logs(); CREATE TABLE -
Load the sample CEF file, using the flex parser
fcefparser:=> COPY logs FROM '/home/dbadmin/data/CEF_sample.cef' PARSER fcefparser(); Rows Loaded ------------- 1 (1 row) -
Use the
maptostring()function to see the contents of thelogsflex table:=> SELECT maptostring(__raw__) FROM logs; maptostring ------------------------------------------------------------------------------------- { "_cefver" : "0.1", "agentzoneuri" : "/All Zones/ArcSight System/Private Address Space Zones/RFC1918: 888.99.0.0-888.200.255.255", "agt" : "888.99.100.1", "ahost" : "SKEELES10", "aid" : "3DxKlG0UBABCAA0cXXAZIwA==", "art" : "1396328241038", "at" : "testalertng", "atz" : "Australia/Sydney", "av" : "6.0.3.6664.0", "c6a4" : "fe80:0:0:0:495d:cc3c:db1a:de71", "c6a4label" : "Agent IPv6 Address", "cat" : "/Agent/Started", "catdt" : "Security Mangement", "categorybehavior" : "/Execute/Start", "categorydevicegroup" : "/Application", "categoryobject" : "/Host/Application/Service", "categoryoutcome" : "/Success", "categorysignificance" : "/Normal", "cs2" : "<Resource ID=\"3DxKlG0UBABCAA0cXXAZIwA==\"/>", "cs2label" : "Configuration Resource", "deviceproduct" : "ArcSight", "deviceseverity" : "Warning", "devicevendor" : "ArcSight", "deviceversion" : "6.0.3.6664.0", "devicezoneuri" : "/All Zones/ArcSight System/Private Address Space Zones/RFC1918: 888.99.0.0-888.200.255.255", "dtz" : "Australia/Sydney", "dvc" : "888.99.100.1", "dvchost" : "SKEELES10", "eventid" : "1", "filetype" : "Agent", "mrt" : "1396328238973", "name" : "Agent [test] type [testalertng] started", "rt" : "1396328238937", "severity" : "Low", "signatureid" : "agent:030", "version" : "0" } (1 row)
Create a columnar table and load CEF data
This example lets you compare the flex table for CEF data with a columnar table. You do so by creating a new table and load the same CEF_sample.cef file used in the preceding flex table example.
-
Create a columnar table,
col_logs, defining the prefix names that are hard coded infcefparser:=> CREATE TABLE col_logs(version INT, devicevendor VARCHAR, deviceproduct VARCHAR, deviceversion VARCHAR, signatureid VARCHAR, name VARCHAR, severity VARCHAR); CREATE TABLE -
Load the sample file into
col_logs, as you did for the flex table:=> COPY col_logs FROM '/home/dbadmin/data/CEF_sample.cef' PARSER fcefparser(); Rows Loaded ------------- 1 (1 row) -
Query the table. You can find the identical information in the flex table output.
=> \x Expanded display is on. VMart=> SELECT * FROM col_logs; -[ RECORD 1 ]-+---------------------------------------- version | 0 devicevendor | ArcSight deviceproduct | ArcSight deviceversion | 6.0.3.6664.0 signatureid | agent:030 name | Agent [test] type [testalertng] started severity | Low
Compute keys and build a flex table view
In this example, you use a flex helper function to compute keys and build a view for the logs flex table.
-
Use the
compute_flextable_keys_and_build_viewfunction to compute keys and populate a view generated from thelogsflex table:=> SELECT compute_flextable_keys_and_build_view('logs'); compute_flextable_keys_and_build_view ------------------------------------------------------------------------------------- Please see public.logs_keys for updated keys The view public.logs_view is ready for querying (1 row) -
Query the
logs_keystable to see what the function computed from the sample CEF data:=> SELECT * FROM logs_keys; key_name | frequency | data_type_guess ----------------------+-----------+----------------- c6a4 | 1 | varchar(60) c6a4label | 1 | varchar(36) categoryobject | 1 | varchar(50) categoryoutcome | 1 | varchar(20) categorysignificance | 1 | varchar(20) cs2 | 1 | varchar(84) cs2label | 1 | varchar(44) deviceproduct | 1 | varchar(20) deviceversion | 1 | varchar(24) devicezoneuri | 1 | varchar(180) dvchost | 1 | varchar(20) version | 1 | varchar(20) ahost | 1 | varchar(20) art | 1 | varchar(26) at | 1 | varchar(22) cat | 1 | varchar(28) catdt | 1 | varchar(36) devicevendor | 1 | varchar(20) dtz | 1 | varchar(32) dvc | 1 | varchar(24) filetype | 1 | varchar(20) mrt | 1 | varchar(26) _cefver | 1 | varchar(20) agentzoneuri | 1 | varchar(180) agt | 1 | varchar(24) aid | 1 | varchar(50) atz | 1 | varchar(32) av | 1 | varchar(24) categorybehavior | 1 | varchar(28) categorydevicegroup | 1 | varchar(24) deviceseverity | 1 | varchar(20) eventid | 1 | varchar(20) name | 1 | varchar(78) rt | 1 | varchar(26) severity | 1 | varchar(20) signatureid | 1 | varchar(20) (36 rows) -
Query several columns from the
logs_view:=> \x Expanded display is on. VMart=> select version, devicevendor, deviceversion, name, severity, signatureid from logs_view; -[ RECORD 1 ]-+---------------------------------------- version | 0 devicevendor | ArcSight deviceversion | 6.0.3.6664.0 name | Agent [test] type [testalertng] started severity | Low signatureid | agent:030
Use the fcefparser delimiter parameter
In this example, you use the fcefparser delimiter parameter to query events located in California, New Mexico, and Arizona.
-
Create a new columnar table,
CEFData3:=> CREATE TABLE CEFData3(eventId INT, location VARCHAR(20)); CREATE TABLE -
Using the
delimiter=','parameter, load some CEF data into the table:=> COPY CEFData3 FROM stdin PARSER fcefparser(delimiter=','); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> eventId=1,location=California >> eventId=2,location=New Mexico >> eventId=3,location=Arizona >> \. -
Query the table:
=> SELECT eventId, location FROM CEFData3; eventId | location ---------+------------ 1 | California 2 | New Mexico 3 | Arizona (3 rows)