Can I use a savedsearch (or a macro) to filter events before the first pipe?

[andrewtrobec]

Hello,
I'm trying to make my searches more efficient and I'd like to know if savedsearches (or maybe macros) can be used to filter before the first pipe. Assume I have two indexes named current and history. I'd like to filter events in history based on a value in current. Simplified indexes:

Current
ID,Current_Status
0001,Open
0002,Open
0003,Closed

History
ID,Historical_Status
0001,Open
0002,Open
0003,Open
0003,In Progress
0003,Closed

Assuming I'd like to analyze events in history for IDs in current that have Current_Status="Closed", would it be possible to avoid having to load all data, make a join, and then use a where condition by including some sort of subsearch in the first line?

I've already got as far as creating a savedsearch get_current_status that will return the current value:

index="current" ID="$ID" | table Current_Status

I can successfully call this in a search as well:

| savedsearch get_current_status ID=ID

What I can't do, however, is figure out whether I can use a subsearch to filter in the first line. Something along the lines of:

index="history" [|savedsearch get_current_status ID=ID]="Closed"

Is this possible?

Thank you and best regards,

Andrew

[Vijeta]

IS Current Status a single value, if yes you can return Current_Status or use fields instead of table

[andrewtrobec]

Hello Vijeta,
Current_Status is a single field, yes. Could you please elaborate on how I could return that value? Also, can this approach be used in the first line of a search, before the first pipe?
Regards,
Andrew

[Vijeta]

This should work-

index="history" [|savedsearch get_current_status ID=ID| return Current_Status]

[andrewtrobec]

Hello Vijeta,
The search works, but does not return the desired result. It doesn't really do anything. I was hoping that it would match the ID and then return the field Current_Status that I could then use to filter or perform additional actions. This is not the case. What was the search supposed to do?
Best regards,
Andrew

[Vijeta]

Hi Andrew,
What is the end goal here? Do you want to search on index -history and get the ID from there and then search in your saved search and then search on status as Closed?
If that is the case your savedsearch should be-

index="current" | fields ID Current_Status

and final search-

|savedsearch get_current_status [index=history|return ID]| where Current_Status="Closed"