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 siteid and startcount.
Now I run the same query, but flip the comparator to greater than. So:
sourcetype=alpha time>1501646400 | … | stats count BY siteid
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:
But the results are weird, not a nice table of siteid startcount currcount. I want to end up with a startcount and a currcount (by siteid) so I can do:
eval diffcount = startcount - currcount (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.
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
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