Splunk Search

Issues adding columns from a subsearch

gavinsopra
Engager

My first subsearch – and its not going well. I have two queries I need to combine to get a single results table.

My first query finds how many changes there have been and when the most recent change occurred (events in my data will have a ‘no_of_changes' field which is an integer value)

index=syncserver sync_job_name=Purchasing sync_job_name!="Stage 1" no_of_changes!=0 
| stats sum(no_of_changes) AS "No. of Changes" latest(_time) AS "Last Change" by sync_group_name 
| convert ctime("Last Change")

which produces the “Query 1 results table” below

alt text

Then I have a second query, which is very similar to the first, but all I really want from it is the date/time of the last event for each ‘syncGroup’.

index=syncserver sync_job_name=Purchasing sync_job_name!="Stage 1" | stats sum(no_of_changes) AS "No. of Changes" latest(_time) AS "Last Run" by sync_group_name 
| convert ctime("Last Run")

which produces the “Query 2 results table” above.
Note that ‘SyncGroup03’ does not appear in the first set of results as there where no changes in the selected time period.
The table I want to produce combines these two sets of results as in “Desired results table” below

alt text

So I have tried the following query which makes my first query a subsearch of the second query

index=syncserver sync_job_name=Purchasing sync_job_name!="Stage 1" 
| stats sum(no_of_changes) latest(_time) AS “lastRun” by sync_group_name 
| appendcols 
    [ search index=syncserver sync_job_name=Purchasing sync_job_name!="Stage 1" no_of_changes!=0 
    | stats latest(_time) AS lastChange by sync_group_name] 
| convert ctime(lastChange) 
| rename lastChange AS "Last Change" lastRun AS "Last Run" sum(no_of_changes) AS "No. of Changes" 
| table sync_group_name "No. of Changes" "Last Change" "Last Run"

Which isn’t what I was hoping for (see “Actual results table” above)

So the “Last Run” column isn’t getting populated and the time for “Last Change” on ‘SyncGroup04” (04/14/2020 03:05:37) has appeared against SyncGroup03.

I also had to use different field names in the query as Splunk complained about the presence of ‘last’ in ‘latest(_time) AS "Last Change"’ once the subsearch was added.

Can someone help me with this?

0 Karma
1 Solution

to4kawa
Ultra Champion
 index=syncserver sync_job_name=Purchasing sync_job_name!="Stage 1"
 | stats sum(no_of_changes) AS "No. of Changes" latest(eval(if(no_of_changes!=0,_time,NULL))) AS "Last Change" latest(_time) AS "Last Run" by sync_group_name 
 | convert ctime("Last Change") ctime("Last Run")

How about doing a search together?

View solution in original post

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Why are you subsearching on the same dataset? Don't do this. Search against the data set once and flag your results rather than filtering

0 Karma

gavinsopra
Engager

skoelpin, what do you mean by "flag your results"? Can you point me at an example?

0 Karma

to4kawa
Ultra Champion
 index=syncserver sync_job_name=Purchasing sync_job_name!="Stage 1"
 | stats sum(no_of_changes) AS "No. of Changes" latest(eval(if(no_of_changes!=0,_time,NULL))) AS "Last Change" latest(_time) AS "Last Run" by sync_group_name 
 | convert ctime("Last Change") ctime("Last Run")

How about doing a search together?

0 Karma

gavinsopra
Engager

Bingo. That worked nicely,
Many thanks

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...