I am trying to use the tstats along with timechart for generating reports for last 3 months. We have accelerated data models.
my original query without the tstats or using data models (takes forever to finish) :
index=abc sourcetype=xyz transaction=* client=* | search ( date_hour <= 18 AND date_hour > 5 ) | timechart span=1d eval(round(avg(exec_time),1)) by transaction useother=f
I am writing the same query using tstats (shows matching event, but does not display any results) :
| tstats avg(exec_time) as avg_time_ms FROM datamodel=ABC groupby _time, client, transaction |timechart span=1d eval(round(avg(avg_time_ms),1)) by transaction
Can someone please guide me re-writing the query utilizing data models and tstats?
Give this a try
| tstats avg(exec_time) as exec_time FROM datamodel=ABC by _time, client, trasaction span=1d |timechart span=1d eval(round(avg(avg_time_ms),1)) by transaction
Update
Give this a try
| tstats avg(ABC.exec_time) as exec_time FROM datamodel=ABC where sourcetype=abc groupby ABC.transaction, _time span=1h | eval date_hour=tonumber(strftime(_time,"%H")) | where date_hour <= 18 AND date_hour > 5 | timechart span=1d eval(round(avg(exec_time),1)) by ABC.transaction
Give this a try
| tstats avg(exec_time) as exec_time FROM datamodel=ABC by _time, client, trasaction span=1d |timechart span=1d eval(round(avg(avg_time_ms),1)) by transaction
Update
Give this a try
| tstats avg(ABC.exec_time) as exec_time FROM datamodel=ABC where sourcetype=abc groupby ABC.transaction, _time span=1h | eval date_hour=tonumber(strftime(_time,"%H")) | where date_hour <= 18 AND date_hour > 5 | timechart span=1d eval(round(avg(exec_time),1)) by ABC.transaction
Thank you Somesoni2.
Works Just fine..!!!
Thanks Somesoni2,
I actually tried this exact query you mentioned in answers last evening, but it was showing events matched... but again did not display results..
Then I tried this one , which worked for me.
| tstats count FROM datamodel=ABC where sourcetype=abc groupby ABC.transaction, ABC.client, ABC.exec_time, _time span=1d | timechart avg(ABC.exec_time) by ABC.transaction
But another Challenge here was, round with eval was not working and also I was not able to pull events for specific time frame ---> search ( date_hour <= 18 AND date_hour > 5 )
The report required is for last 3 months and business hours.
Try the updated answer.
SomeSoni2,
I tried this filter with other query using tstats.. the filter just doesn't work when I am trying to get results for entire day ..
| tstats count(ABC.SlowCalls) as SlowCalls count(ABC.Calls) as Calls count(ABC.Errors) as Errors count as Total FROM datamodel=ABC where (sourcetype=info OR sourcetype=error) ABC.clientid=1122337 ABC.transaction_name=* groupby _time span=1h | eval qual = round(((Total-Errors)/Total)*100, 3) | eval Perf = round((1-(SlowCalls/Calls))*100, 3) | eval date_hour=tonumber(strftime(_time,"%H")) | eval date_wday=lower(strftime(_time,"%A")) | where NOT (_time=22) OR ((date_wday="wednesday" OR date_wday="friday") AND date_hour<2) OR ((date_wday="saturday") AND date_hour>=16) OR ((date_wday="sunday") AND date_hour<4)))
SlowCalls, Calls, Errors are a calculated fields of the datamodel "ABC" .
It works perfectly if I use span=1h at with tstats , but If I change to span = 1d from span=1h , it doesn't filter the events for maintenance windows. This is a report for one client, but I will do it for multiple clients and would essentially run daily with data in a day and not per hour. Is there any other way to specify span or override it ?
Thanks
Your filter is based on date_hour and date_wday field. When you choose the span=1d, all date_hour values will become 0 as _time will be bucketed as mm/dd/yyyy 00:00:00
, thus your filter fails. So you need to use the span=1h, filter the events and then again do the aggregation with span=1d.
| tstats count(ABC.SlowCalls) as SlowCalls count(ABC.Calls) as Calls count(ABC.Errors) as Errors count as Total FROM datamodel=ABC where (sourcetype=info OR sourcetype=error) ABC.clientid=1122337 ABC.transaction_name=* groupby _time span=1h
| eval date_hour=tonumber(strftime(_time,"%H")) | eval date_wday=lower(strftime(_time,"%A")) | where NOT (_time=22) OR ((date_wday="wednesday" OR date_wday="friday") AND date_hour<2) OR ((date_wday="saturday") AND date_hour>=16) OR ((date_wday="sunday") AND date_hour<4)))
| bucket span=1d _time | stats sum(SlowCalls) as SlowCalls sum(Calls) as Calls sum(Errors) as Erros by _time
| eval qual = round(((Total-Errors)/Total)*100, 3) | eval Perf = round((1-(SlowCalls/Calls))*100, 3)
Awesome ..!! worked as expected.
Thanks again SomeSoni2.