append command- more efficient query

[akawacz]

Hi,

could you help me to write more efficient query? My is really time consuming.

Example.

--First part cacluate backlog for June, second for July and third for August

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

[somesoni2 · Accepted solution]

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

[akawacz]

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