Reporting

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

andrewtrobec
Motivator

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

Vijeta
Influencer

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

0 Karma

andrewtrobec
Motivator

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

Vijeta
Influencer

This should work-

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

0 Karma

andrewtrobec
Motivator

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

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...