How to search based on the time range returned from subsearch ?

[sangs8788]

The query,

|inputlookup ReleaseCalender.csv |sort Release ASC| reverse |streamstats current=f last(Production) as latest|rename Production as earliest| where Release="1"| fields earliest,latest| format "" "" "" "" "" ""

returns resultset of 1 row with column names - earliest, latest, search. In which earliest and latest column values are empty and search column value is --> earliest="21/1/2017" latest="18/3/2017"

I would like append the search column value to the main search so that the events between the results are returned. Below is a sample query

index=app | stats count |[|inputlookup ReleaseCalender.csv |sort Release ASC| reverse |streamstats current=f last(Production) as latest|rename Production as earliest| where Release="1"| fields earliest,latest| format "" "" "" "" "" ""]  

[sangs8788]

Also is it possible to group the number of events by Release field, which is not part of events at all - but has the timestart and timeend for groupin ? Somthing like below result

Release Count
1 100
2 50
3 200

[sangs8788]

Is it possible to get the Release number out in order to display in the result. If i add Release field to the subsearch. There is no field in the name "Release" in the events, the query doesnt work if Release field is added to subsearch.

[somesoni2]

Not sure if earliest="21/1/2017" latest="18/3/2017"will be accepted as valid time range. It'll be better to just change the value to epoch in subsearch before returning. ALso, as @Daljeanis suggested, the timerange should be specified in base search, not in stats or end of the search. Try like this

index=app  [|inputlookup ReleaseCalender.csv |sort Release ASC| reverse |streamstats current=f last(Production) as latest|rename Production as earliest| where Release="1"| fields earliest,latest | convert mktime(*) timeformat="%d/%m/%Y" | format "" "" "" "" "" ""] | stats count

[DalJeanis · Accepted solution]

TL;DR: move the subsearch [] before the first pipe |.

---

The format command removes the fields earliest and latest and leaves only the field search.

The search field is a special field. When the square brackets end, whatever is in the field search is returned as if the command return search or return $search had been executed. In other words, the value of search in the subsearch is dropped, as-is, into the search in the place where the square brackets [...]are.

thus, your command after the pipe reads like this

 | stats count earliest=xxxx latest=xxxx 

Earliest and latest are not valid arguments for stats, so you need to move them before the first pipe. They can be valid arguments for tstats, which is a generating command, but in this case, they need to be up with the implicit search before that first pipe.

[sangs8788]

Also is it possible to group the number of events by Release field, which is not part of events at all - but has the timestart and timeend for groupin ? Somthing like below result

Release Count
1 100
2 50
3 200

[sangs8788]

Is it possible to get the Release number out in order to display in the result. If i add Release field to the subsearch. There is no field in the name "Release" in the events, the query doesnt work if Release field is added to subsearch.