Hi,
could you help me to write more efficient query? My is really time consuming.
Example.
index=A
| eval MONTH = relative_time(now(),"-7d@mon-3mon")
| eval BACKLOG = if(CREATE_INCIDENT<REPORT_MONTH AND CLOSED_INCIDENT>MONTH ,1,0)
| where BACKLOG =1
| append [search index=A
| eval MONTH = relative_time(now(),"-7d@mon-2mon")
| eval BACKLOG = if(CREATE_INCIDENT<REPORT_MONTH AND CLOSED_INCIDENT>MONTH ,1,0)
| where BACKLOG =1]
| append [search index=A
| eval MONTH = relative_time(now(),"-7d@mon-1mon")
| eval BACKLOG = if(CREATE_INCIDENT<REPORT_MONTH AND CLOSED_INCIDENT>MONTH ,1,0)
| where BACKLOG =1]
stats dc(Ticket) as BACKLOG_COUNT by MONTH |
---|
in A index we have filed Ticket.
Thanks
I'm sure you missed the specific earliest and latest for each query, So I will include them in my answer. Try something like this
index=A earliest=-7d@mon latest=-7d@mon-3mon
| bucket span=1mon _time
| eval BACKLOG = if(CREATE_INCIDENT<REPORT_MONTH AND CLOSED_INCIDENT>MONTH ,1,0)
| stats sum(BACKLOG) as BACKLOG_COUNT by _time | eval Month=strftime(_time,"%Y-%m") | table Month BACKLOG_COUNT
Update
Give this a try
index=A earliest=-7d@mon latest=-7d@mon-3mon CREATE_INCIDENT<REPORT_MONTH
| eval MONTH= if(CLOSED_INCIDENT>relative_time(now(),"-7d@mon-3mon"),strftime(relative_time(now(),"-7d@mon-3mon"),"%Y-%m"),"")
| eval MONTH= if(CLOSED_INCIDENT>relative_time(now(),"-7d@mon-2mon"),MONTH." ".strftime(relative_time(now(),"-7d@mon-2mon"),"%Y-%m"),MONTH)
| eval MONTH= if(CLOSED_INCIDENT>relative_time(now(),"-7d@mon-1mon"),MONTH." ".strftime(relative_time(now(),"-7d@mon-1mon"),"%Y-%m"),MONTH)
| makemv MONTH | stats count by by MONTH
I'm sure you missed the specific earliest and latest for each query, So I will include them in my answer. Try something like this
index=A earliest=-7d@mon latest=-7d@mon-3mon
| bucket span=1mon _time
| eval BACKLOG = if(CREATE_INCIDENT<REPORT_MONTH AND CLOSED_INCIDENT>MONTH ,1,0)
| stats sum(BACKLOG) as BACKLOG_COUNT by _time | eval Month=strftime(_time,"%Y-%m") | table Month BACKLOG_COUNT
Update
Give this a try
index=A earliest=-7d@mon latest=-7d@mon-3mon CREATE_INCIDENT<REPORT_MONTH
| eval MONTH= if(CLOSED_INCIDENT>relative_time(now(),"-7d@mon-3mon"),strftime(relative_time(now(),"-7d@mon-3mon"),"%Y-%m"),"")
| eval MONTH= if(CLOSED_INCIDENT>relative_time(now(),"-7d@mon-2mon"),MONTH." ".strftime(relative_time(now(),"-7d@mon-2mon"),"%Y-%m"),MONTH)
| eval MONTH= if(CLOSED_INCIDENT>relative_time(now(),"-7d@mon-1mon"),MONTH." ".strftime(relative_time(now(),"-7d@mon-1mon"),"%Y-%m"),MONTH)
| makemv MONTH | stats count by by MONTH
This is really good but logic in my query is a bit different. Every block of query count backlog for specific month range + furter month if condtions are met (so duplicate data can be there).
e.g.
June block Created= <2015-Jun and Closed>2015-Jun
- June 1000
- July 200
- August 100
- total for JUNE - 1300
July block Created= <2015-Jul and Closed>2015-Jul
- July 1500
- August 100
- total for JULY 1600
August block Created= <2015-AUG and Closed>2015-AUG
- August 1800
- total 1800
Hope this make sense