Issues adding columns from a subsearch

[gavinsopra]

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

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

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?

[skoelpin]

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

[gavinsopra]

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

[to4kawa · Accepted solution]

 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?

[gavinsopra]

Bingo. That worked nicely,
Many thanks