Multiple stats commands in saved search do not work according to jobInspector

[ramgnisiv]

Hi guys,

I created a saved search that looks like this:

index=development_sdlcmetrics_summary_pr search_name="metric_sonarqube_projectcomponent_[blockers]_[1d]" earliest=-1d@d latest=@d 
| stats avg(blockers) as countPerKey by sonarKey 
| appendcols 
    [ search index=development_sdlcmetrics_summary_pr search_name="metric_sonarqube_projectcomponent_[blockers]_[1d]" earliest=-1d@d latest=@d 
    | stats avg(blockers) as avgCount 
    | eval avgCount=round(avgCount,2)] 
| fields * 
| filldown avgCount 
| eval comparison=case(countPerKey>=avgCount,"3", countPerKey==avgCount,"2", countPerKey<avgCount,"1") 
| sistats avg(comparison) as score by sonarKey countPerKey avgCount 
| eval _time = relative_time( relative_time(now(), "@d") , "-1d")

The goal is to get this data into a summary index.

The problem is, that when i use the jobInspector in the saved search "view recent" overview, i can see that the field "eventSearch" only contains the very first part of the query, being this:

index=development_sdlcmetrics_summary_pr search_name="metric_sonarqube_projectcomponent_[blockers]_[1d]" earliest=-1d@d latest=@d 

I've come to learn that this happens because everything before the first search command is viewed upon as the eventSearch by Splunk.

Question:

How do i make sure that all of my query is ran by the saved search, up to the part just before the "sistats" command?

[ramgnisiv · Accepted solution]

This problem had to do with the newline characters in the search Stanza in the savedsearches.conf file.
2 ways to fix this: Escape the newline characters, or put the entire saved search on a single row.
I choose the latter, so by adding all of the search on the same row, the problem was solved and the savedsearch was executed from thereon perfectly.

edit: typos

[richgalloway]

How did you create this search? It seems like you edited a .conf file. That's fine, but multi-line values like eventSearch need a line continuation character \ on the end of each line except the last.

[ramgnisiv]

@richgalloway: Thanks for your response. All saved searches are created using savedsearches.conf and are pushed to the search head cluster using a CICD pipeline. This way we keep track of all changes done to the app. This applies for everything done in the app, so for all knowledge objects we use the pipeline and we use configuration as code.

I'm not sure i understand your advice.

My savedsearches.conf for this particular search looks like this:

[score_sonarqube_projectcomponent_[blockers_comparison]_[1d]]
action.summary_index = 1
action.summary_index._name = development_sdlcmetrics_summary_pr
alert.track = 0
cron_schedule = 15 1 * * *
description = {"type":"metric","id":"0083","dataCreateSource":"sonarqube","scheduleFrequency":"1d","description of metric":"comparison of average number of blockers versus number of blockers per SonarQube key per day"}
dispatch.earliest_time = -1d@d
dispatch.latest_time = @d
display.visualizations.show = 0
enableSched = 1
request.ui_dispatch_app = sdlc_metrics
request.ui_dispatch_view = search
search = index=development_sdlcmetrics_summary_pr search_name="metric_sonarqube_projectcomponent_[blockers]_[1d]" earliest=-1d@d latest=@d 
| stats avg(blockers) as countPerKey by sonarKey 
| appendcols 
    [ search index=development_sdlcmetrics_summary_pr search_name="metric_sonarqube_projectcomponent_[blockers]_[1d]" earliest=-1d@d latest=@d 
    | stats avg(blockers) as avgCount 
    | eval avgCount=round(avgCount,2)] 
| fields * 
| filldown avgCount 
| eval comparison=case(countPerKey>=avgCount,"3", countPerKey==avgCount,"2", countPerKey<avgCount,"1") 
| sistats avg(comparison) as score by sonarKey countPerKey avgCount 
| eval _time = relative_time( relative_time(now(), "@d") , "-1d")

So when it is viewed via the jobInspector, i can see that the saved search is executed, but it only takes the very first part ( that part is: index=development_sdlcmetrics_summary_pr search_name="metric_sonarqube_projectcomponent_[blockers]_[1d]" earliest=-1d@d latest=@d ), probably because of the |stats command that is directly behind it.

[richgalloway]

Only the first line of the search is executed because Splunk only knows about the first line.
It only knows about the first line because the savedsearches.conf file is not properly formatted.
After search =, each line must end with \, except for the last line of the search.

search = index=development_sdlcmetrics_summary_pr

search_name="metric_sonarqube_projectcomponent_[blockers]_[1d]" earliest=-1d@d latest=@d \
 | stats avg(blockers) as countPerKey by sonarKey \
 | appendcols \
     [ search index=development_sdlcmetrics_summary_pr search_name="metric_sonarqube_projectcomponent_[blockers]_[1d]" earliest=-1d@d latest=@d \
     | stats avg(blockers) as avgCount \
     | eval avgCount=round(avgCount,2)] \
 | fields * \
 | filldown avgCount \
 | eval comparison=case(countPerKey>=avgCount,"3", countPerKey==avgCount,"2", countPerKey<avgCount,"1") \
 | sistats avg(comparison) as score by sonarKey countPerKey avgCount \
 | eval _time = relative_time( relative_time(now(), "@d") , "-1d")

You should be able to find examples of this in $SPLUNK_HOME/etc/apps//default/savedsearches.conf.

[FrankVl]

Yes, this should be it.

@richgalloway note that your initial post is a bit confusing since \ doesn't show the backslash, just the two backticks. The backslash works as an escape for the second backtick, causing the backticks to simply get printed as plain backticks, rather than triggering the code markup.

The example conf file is crystal clear though 🙂

[richgalloway]

Thanks for pointing that out, @FrankVI. I missed the missing character.