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.
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"
I've never used tstats
. That's amazing
this command does not give any events back
how can that be?
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.
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.
If it worked, come back here and click Accept
to close the question.
| 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?