I want to diff the counts before and after a certain date. Here is the 'before' query.
sourcetype=alpha _time<1501646400 | … | stats count BY site_id
For dev/example purposes, I hardcoded the date. The ... is a bunch of stuff I hope is not germane to this issue. In any case, I get what I want from the first query. It gives me a nice table with site_id and count columns. I can add:
| eval start_count=count
and still I get nice table of site_id and start_count.
Now I run the same query, but flip the comparator to greater than. So:
sourcetype=alpha _time>1501646400 | … | stats count BY site_id
If I run that by itself I get a nice table as before. But I need them both. I have tried an append query (appending this greater than query to the prior less-than query) with adding to this last one a:
|eval curr_count=count
But the results are weird, not a nice table of site_id start_count curr_count. I want to end up with a start_count and a curr_count (by site_id) so I can do:
eval diffcount = start_count - curr_count (per site_id)
Probably the way to go is with a summary index, so if you can tell me how to do that great. Any suggestions though are welcome.
A standalone alternative
| makeresults count=10
| streamstats count as number, min(_time) as first_time
| eval _time = _time - (10-number)
| eval site_id = CASE(number=1,"A",number=2,"B",number=3,"B",number=4,"C",number=5,"A",number=6,"A",number=7,"C",number=8,"C",number=9,"C",number=10,"C")
| eval cut_off_time = first_time - 5
| eval is_start_count = IF(_time < cut_off_time, "start_count", "current_count")
| stats count by site_id,is_start_count
| xyseries site_id,is_start_count,count
| fillnull
| eval diff_count = start_count - current_count
| table site_id, start_count, current_count, diff_count
Try like this
sourcetype=alpha | … | eval period=if(_time<1501646400,"before_count","curr_count") | chart count BY site_id period | eval diffcount = start_count - curr_count