Splunk Search

I want to diff the counts before and after a certain date.

claatu
Explorer

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.

0 Karma

msivill_splunk
Splunk Employee
Splunk Employee

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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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 
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...