Reporting

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

andrewtrobec
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

Vijeta
Influencer

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

0 Karma

andrewtrobec
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

Vijeta
Influencer

This should work-

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

0 Karma

andrewtrobec
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

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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...