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 "" "" "" "" "" ""]
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.
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
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.
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
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.
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
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.