Network Management

I have a large amount of syslog events ( 121,157,336 Device Events)

The Airwave is underperforming so sifting through the UI is a pain (we are taking care of that).

 

Can I get something from the DB or in a log file somehow?

I need to get look at a certain date between a certain time.

 

 

hi Pasquale,

can do it from the shell, by making some sql queries directly against the device_events table.

 

Since this table stores traps and syslog, first filter against 'facility is not null' to sort the traps from syslog, then filter against time range

 

examples:

 

> Use 'limit' for doing initial testing of your query
> use 'count(*)' to make estimate of output before generating the full output to avoid nasty surprises of 10000000 lines of output

Modify the below timestamp(' date and time ') to reflect the range you are interested in. 

> Test query to check the number of results
dbc "select count(*) from device_event where facility is not null and timestamp between extract(epoch from timestamp '2015-03-19 00:00:00') and extract(epoch from timestamp '2015-03-19 02:00:00')"


> display first 5 results, plus convert the timestamp
dbc "select to_timestamp(timestamp),* from device_event where facility is not null and timestamp between extract(epoch from timestamp '2015-03-19 00:00:00') and extract(epoch from timestamp '2015-03-19 02:00:00') limit 5"

> display first 50 results, remove some fields, and truncate the syslog message down to 128 char to make it easier to skim

dbc "select to_timestamp(timestamp), severity, substr(message,0,128) from device_event where facility is not null and timestamp between extract(epoch from timestamp '2015-03-19 00:00:00') and extract(epoch from timestamp '2015-03-19 02:00:00') limit 50"

 

*disclaimer* there may indeed be more concise and better ways to do the sql query, the above works, but is likely far from optimal :)

 

hope that's useful

-jeff

 

Thanks Jeff, let me give it a shot as currently my "csv export" is still downloading at 12+Gb....:)

I'll report back

so running the query as follows

"dbc "select to_timestamp(timestamp),* from device_event where facility is not null and timestamp between extract(epoch from timestamp '2015-03-17 12:30:00') and extract(epoch from timestamp '2015-03-17 13:20:00') limit 5"

 actually timed out my ssh session lol.