How to calculate two months of deviation with count

[igschloessl]

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.

[woodcock]

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"

[to4kawa]

I've never used tstats. That's amazing

[igschloessl]

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

[woodcock]

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.

[to4kawa]

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.

[woodcock]

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

[to4kawa]

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