Splunk Search

append command- more efficient query

akawacz
Path Finder

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

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

somesoni2
Revered Legend

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
0 Karma

akawacz
Path Finder

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

0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...