topic Re: How to merge events with identical timestamps into one event, but drop all differing data? in Getting Data In

How to merge events with identical timestamps into one event, but drop all differing data?

david_rundle_fi — Tue, 09 Sep 2014 15:04:38 GMT

I have the following 9 events with the identical timestamps, but differing information:

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, queue_len, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, files_skipped, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, buildup_skips, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, malware_detected, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, scans_canceled, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, scans_completed, null, null, null, null, null, null, null, null, null, null, 1461, 6735, 8101, 3869, 20166, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, spf_reads, null, null, null, null, null, 1401, 6342, 8101, 3869, 19713, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, spf_writes, 1401, 6342, 8101, 3869, 19713, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, spoolc_drops, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0

I want to drop the event type (spfreads, spfwrites, etc) and the null values, and combine the events into a single event.

How can I do this?

Re: How to merge events with identical timestamps into one event, but drop all differing data?

kristian_kolb — Tue, 09 Sep 2014 20:49:35 GMT

You could probably submit some more info, especially on just how you want the combined information to look like. One thing that you might try is the transaction command.

Assuming that the KQ25B6P is some sort of SessionID, perhaps ... | transaction SessionID max_span=1s | might work for you.

Re: How to merge events with identical timestamps into one event, but drop all differing data?

david_rundle_fi — Wed, 10 Sep 2014 16:15:56 GMT

Ok, this is a good start - I ran the transaction on the timestamp, as this is a performance stats collection that is running every 5 minutes on multiple devices.(Session ID's)

I now have a single event that is the composite of the 9 event types.

Any way to remove the duplicate null values? (dedup on each field name?)

Re: How to merge events with identical timestamps into one event, but drop all differing data?

kristian_kolb — Thu, 11 Sep 2014 21:32:52 GMT

Well, there is perhaps a far more attractive option;
Drop the stuff you don't want (the type) by setting it to the same value everywhere, then make use of the stats max() function. You may first need to replace the string 'null' with a real NULL value, if that is what you have. Or perhaps not. At least in my test you don't

your search | eval type = "Combined" | stats max(*) by _time

This should look something like;

2014-09-09 05:57:58, KQ25B6P, 7.7.1, CommandPost+, 4, 70701, Combined, 1401, 6432, 1234, 3424, 7663, 2342, null, null, 8787, 1461, 6735, 8101, 3869, 20166, null, null, null, null, null, null, null, 0, 0, 0, 0, null, null, null, null, null, null, null, null, null, 5435, 123, 0, 6676, null, null, null, null, null, 0

i.e. if there is a field that does not have a value in either of the events, the combined event will still have 'null'. Otherwise, the highest value will take that place.

For presentation purposes you might then want play with fields, replace, table, or rename etc.

Hope this helps,