Splunk Search

How to calculate two months of deviation with count

igschloessl
Explorer
index=proxy earliest=-1month@month latest=@month|fields host month | eval month=strftime(_time, "%m") | stats count by host| rename count as curr_count | table curr_count host
 |appendcols [search index=proxy earliest=-2month@month latest=-1month@month|fields host month | eval month=strftime(_time, "%m") | stats count by host|rename count as old_count | table old_count host] 
 | fillnull value=1 
  | eval ratio=curr_count-old_count,   "up/down"=if (curr_count > old_count, "down", "up")
 | table host old_count curr_count ratio  "up/down"
 | rename old_count AS "-2month" curr_count AS "-1month" ratio AS "deviation"

It works but it is really really slow
How can I make it faster?

Best regards and thank you.

0 Karma

woodcock
Esteemed Legend

This will be lightning fast:

| tstats count WHERE index=proxy earliest=-2month@month latest=@month BY host _time span=1month
| streamstats count AS _serial BY host
| eval old_count = if(_serial==1, count, null())
| eval curr_count = if(_serial==2, count, null())
| fields host old_count curr_count
| stats first(*) AS * BY host
| eval ratio = curr_count - old_count, "up/down"=if(curr_count > old_count, "down", "up") 
| table old_count curr_count ratio "up/down" 
| rename old_count AS "-2month" curr_count AS "-1month" ratio AS "deviation"
0 Karma

to4kawa
Ultra Champion

I've never used tstats. That's amazing

igschloessl
Explorer

this command does not give any events back
how can that be?

0 Karma

woodcock
Esteemed Legend

The core answer is solid, so do the standard debugging. Remove pipelines from the bottom, one at a time, until you get results that make sense. Then start adding back in the pipelines, fixing the obvious mistakes.

0 Karma

to4kawa
Ultra Champion

tstats OR my query?
If there is an example of the expected result or a sample log in the first place, the cause and correction are easy.

0 Karma

woodcock
Esteemed Legend

If it worked, come back here and click Accept to close the question.

0 Karma

to4kawa
Ultra Champion
| stats count
| eval raw="2019/07/01,2019/10/31"
| makemv delim="," raw
| mvexpand raw
| eval time=strptime(raw,"%Y/%m/%d")
| makecontinuous time span=1d
| eval _time=time
| eval host="host".(random() % 5 + 1)
| timechart span=1month count by host
`comment("this is sample data")`
| eval verdict=case(_time=relative_time(now(),"-1month@month")
 ,"curr",_time=relative_time(now(),"-2month@month"),"old")
| where IN(verdict,"curr", "old")
| untable verdict host count
| xyseries host verdict count
| eval ratio=curr-old, "up/down"=if (curr > old, "down", "up")
| rename old as old_count
| rename curr as curr_count
| table host old_count curr_count ratio "up/down"
| rename old_count AS "-2month" curr_count AS "-1month" ratio AS "deviation"

Hi, this is sample query.

index=proxy earliest=-2month@month latest=@month
| timechart span=1month count by host
| eval verdict=case(_time=relative_time(now(),"-1month@month") ,"curr"
  ,_time=relative_time(now(),"-2month@month"),"old")
| where IN(verdict,"curr", "old")
| untable verdict host count
| xyseries host verdict count
| eval ratio=curr-old, "up/down"=if (curr > old, "down", "up")
| rename old as old_count
| rename curr as curr_count
| table host old_count curr_count ratio "up/down"
| rename old_count AS "-2month" curr_count AS "-1month" ratio AS "deviation"

How about this?
By the way, where did the severity come from?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...