Reporting

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

Builder

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

0 Karma

Influencer

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

0 Karma

Builder

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

0 Karma

Influencer

This should work-

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

0 Karma

Builder

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

0 Karma

Influencer

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 getcurrentstatus [index=history|return ID]| where Current_Status="Closed"

0 Karma