topic Re: How to derive earliest and latest from the subquery? in Splunk Search

How to derive earliest and latest from the subquery?

msg4sunil — Thu, 14 Apr 2022 16:16:23 GMT

index=app1 [search index=app1 "orderid"| fields id]

How do I modify the above query wherein "search index=app1 "orderid"| fields id" query is run and its first event's time and the last event's time is take as earliest and latest time respectively for the query, "index=app1"

thus it would look sometime like

index=app1 earliest=x latest=y [search index=app1 "orderid"| fields id]

wherein the values x and y is the first and last event's datetime of the query, "search index=app1 "orderid"| fields id"

Thank you

Re: Derive earliest and latest from the subquery

gcusello — Thu, 14 Apr 2022 06:27:34 GMT

Hi @msg4sunil,

please try something like this:

index=app1 [ search index=app1 "orderid" | addinfo | rename info_max_time AS latest info_min_time AS earliest | fields id earliest latest ]

Ciao.

Giuseppe

Re: Derive earliest and latest from the subquery

bowesmana — Thu, 14 Apr 2022 06:30:55 GMT

Should work, i.e. pass the earliest and latest out from the subsearch

index=app1 [ search index=app1 "orderid" | stats latest(_time) as latest earliest(_time) as earliest values(id) | fields id earliest latest ]

Re: Derive earliest and latest from the subquery

msg4sunil — Thu, 14 Apr 2022 08:03:28 GMT

@bowesmana , sorry its not working me

The results of both doesnt match since the ID's are different

index=app1 "order1"
| stats count by id

index=app1 [
search index=app1 "order1"
| stats latest(_time) as latest earliest(_time) as earliest values(id)
| fields id earliest latest
]
| stats count by id

Re: Derive earliest and latest from the subquery

msg4sunil — Thu, 14 Apr 2022 08:07:39 GMT

@gcusello , its pretty close but not exact.

index=app1 "order1"| stats count by id

index=app1 [ search index=app1 "orderid" | addinfo | rename info_max_time AS latest info_min_time AS earliest | fields id earliest latest ]|stats count by id

Comparing the output of the above command, while most of the ID's matched based on the above but not all.

Re: Derive earliest and latest from the subquery

gcusello — Thu, 14 Apr 2022 09:38:44 GMT

Hi @msg4sunil,

did you understand which are the not matching IDs?

ciao.

Giuseppe

Re: Derive earliest and latest from the subquery

bowesmana — Mon, 18 Apr 2022 23:00:05 GMT

Can you explain what you mean by the ids are different?

What IDs are you referring to and can you provide an example of how the outer and inner searches are supposed to filter each other?

Re: How to derive earliest and latest from the subquery?

PickleRick — Tue, 19 Apr 2022 04:55:22 GMT

What is the point? Since from what you showed so far you'd be modifying a search with results from the same search which seems a bit pointless.

Re: How to derive earliest and latest from the subquery?

ITWhisperer — Tue, 19 Apr 2022 06:38:04 GMT

Is id required as part of the filtering or just earliest and latest times from the events which match orderid?

index=app1 [ search index=app1 "orderid" | stats latest(_time) as latest earliest(_time) as earliest | fields earliest latest ]