Splicing events with the same time stamp together

[tristanmatthews]

Hi,

I have a weird data structure I'm trying to figure out a better way to handle. The data I'm getting uses category and type fields to tell me what the counts mean. Right now its set up to work something like this, where I do a fair amount of logic to get the counts I want. (but there are actually a lot more categories, types, and logic):

index=counts
| eval count_a=case(category=1 AND type='A', count1, category=2 AND type='A', count2)
| eval count_b=case(category=1 AND type='B', count3, category=2 AND type='B', count4)
| table _time, count_a, count_b

This gives me results like

     _time,                      count_a,    count_b

1 10/26/13 8:15:00.201 PM, [BLANK], 50

2 10/26/13 8:15:00.201 PM, 65, [BLANK]

3 10/26/13 8:00:00.201 PM, [BLANK] , 43

4 10/26/13 8:00:00.201 PM, 78, BLANK]....

The [BLANK]s are literally blank, because not every line if the file has data for each type of count I'm interested in. After all the logic I have count fields for all the counts and every time step has a value for each count, but they are split over multiple lines. Is there a way to combine them so that I can get table with out blanks in it? Like

     _time,                      count_a,    count_b

1 10/26/13 8:15:00.201 PM, 65, 50

2 10/26/13 8:00:00.201 PM, 78 , 43....

Thanks,
Tristan

[Ayn · Accepted solution]

Either setup a transaction based on _time:

index=counts | eval count_a=case(category=1 AND type='A', count1, category=2 AND type='A', count2) | eval count_b=case(category=1 AND type='B', count3, category=2 AND type='B', count4) | transaction _time | table _time, count_a, count_b

Or for better performance, use stats.

index=counts | stats count(eval(category=1 AND type='A')) as count1, count(eval(category=2 AND type='A')) as count2 by _time

[tristanmatthews]

ok I fell silly, transaction was the first thing I tried, but clearly I miss read it.