tstats and using timechart not displaying any results

[nmohammed]

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?

[somesoni2 · Accepted solution]

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

[nmohammed]

Thank you Somesoni2.

Works Just fine..!!!

[nmohammed]

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.

[somesoni2]

Try the updated answer.

[nmohammed]

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

[somesoni2]

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)

[nmohammed]

Awesome ..!! worked as expected.

Thanks again SomeSoni2.