Reporting

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

ramgnisiv
Path Finder

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?

Tags (1)
0 Karma
1 Solution

ramgnisiv
Path Finder

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

View solution in original post

0 Karma

ramgnisiv
Path Finder

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

ramgnisiv
Path Finder

@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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

FrankVl
Ultra Champion

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 🙂

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...