Can I have a subsearch that returns a list of sources for the main search to use?

[schilds427]

Hello,

I'm working on a splunk alert that monitors processes. If a process has been running for a long time I want to retrieve the stage that process is on to include in the alert. Like "Process A has been running for 2 hours and is currently on stage 'Load Library' and has been for 45 minutes".  The process name value in the subsearch is the same as the source value in the main search (with "console" appended to each).  The subsearch does return a table of the sources I want but the main search then makes a table of lots of sources that I don't want: 

sourcetype="text:jenkins" 
| where source in (source, [search index=jenkins_statistics  event_tag="job_event" node="*" job_name="*" build_number="*"  earliest=-2h@h latest=now()
|dedup host build_url sortby -_time
| search (type="started" ) `utc_to_local_time(job_started_at)`
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(job_started_at) as epocTime
| eval job_duration = if(isnull(job_duration), now() - epocTime, job_duration)
| eval Duration = tostring(job_duration,"duration")
| eval job_result=if(type="started", "INPROGRESS", job_result)
| eval ExceededLimit = if(Duration > "01:00:00", "Limit Met", "Limit Not Met")
| eval source = if(type="started", "\""+build_url+"console\"",null())
| table source])
| stats values(_raw) as Raw by _time,source
|eval Stages = if(like(Raw, "[Pipeline] { (%)"),trim(substr(Raw,15),")"),null())
| sort - _time
| stats  values(source) as Source, values(Stages) as Stages, values(Raw) as Raw, values(_time) as Time by source
| table source, Stages

 For instance the subsearch table is currently 3 processes/sources long but the main search table is 81 processes/sources and doesn't include any of the sources the subsearch returned. 

[richgalloway · Accepted solution]

Run the subsearch by itself with | format on the end to see exactly what is returned to the main search.  I think you'll then find the main search looks something like

sourcetype="text:jenkins" 
| where source in (source, (source="foo" OR source="bar"))
...

which is incorrect.  Adjust the subsearch until it returns a proper result and the query should work.

[schilds427]

Yep my formatting was all messed up as you said. It was returning my sources separated by 'OR's  instead of commas you need in a "IN list" statement. So to fix it I compiled a list of sources with stats and used 'format mvsep="," to separate the list values by comma instead of OR: 

sourcetype="text:jenkins" 
| where source in [search index=jenkins_statistics  event_tag="job_event" node="*" job_name="*" build_number="*"  earliest=@d latest=now()
|dedup host build_url sortby -_time
| search (type="started" ) `utc_to_local_time(job_started_at)`
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(job_started_at) as epocTime
| eval job_duration = if(isnull(job_duration), now() - epocTime, job_duration)
| eval Duration = tostring(job_duration,"duration")
| eval job_result=if(type="started", "INPROGRESS", job_result)
| eval ExceededLimit = if(Duration > "01:00:00", "Limit Met", "Limit Not Met")
| eval source = if(ExceededLimit="Limit Met" AND job_result="INPROGRESS", build_url+"console",null())
| stats values(source) delim="," as search
| format mvsep="," "" "" "" "" "" ""]
| stats values(_raw) as Raw, values(search) by _time,source
|eval Stages = if(like(Raw, "[Pipeline] { (%)"),trim(substr(Raw,15),")"),null())
| sort - _time
| stats  values(source) as Source, values(Stages) as Stages, values(Raw) as Raw, values(_time) as Time by source
| table source, Stages