How to derive earliest and latest from the subquery?

[msg4sunil]

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

[ITWhisperer]

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
]

[PickleRick]

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.

[bowesmana]

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
]

[msg4sunil]

@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

[bowesmana]

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?

[gcusello]

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

[msg4sunil]

@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.

[gcusello]

Hi @msg4sunil,

did you understand which are the not matching IDs?

ciao.

Giuseppe