Splunk Search

How to join two savedsearches based on a column and do a time comparison?

Splunk_321
Path Finder

I have two savedsearches

savedsearch1: | basesearch | stats count by _time, LocationId

savedsearch2: | basesearch | count by _time, LocationId

I want to track monitoring LocationIds based on below criteria
1) LocationIds which are present in savedsearch2 but not in savedsearch1
2) LocationId if present in both reports, include those LocationIds with savedsearch1 timestamp>savedsearch2 timestamp, otherwise exclude it

I could get LocationIds which are only present in savedsearch2 using below query, but not able to make time comparison

##################################################

| savedsearch "savedsearch1"
| eval flag="match"
| append maxtime=1800 timeout=1800
[ savedsearch "savedsearch2"
| eval flag="metric"]
| stats values(flag) as flag by LocationId | where flag="metric" and flag!="match"
| table LocationId

##################################################

Any help would be appreciated!

Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

You have some pseudo code, so I assume that there is some test SPL that you have tried. Can you explain what problem you encounter?

Just to be clear on terminology so we are on the same page: Splunk classifies a knowledge object that you can reuse in a later search an "advanced search"; the term "saved searches" is otherwise called a report, which you can incorporate into a dashboard but not reuse in another search. The most common reusable advanced search is a macro.

Assume that you have saved your two searches in macros as "macro1" and "macro2", names being without quotation marks. (If you haven't, read that linked document.) Then, the actual SPL is extremely close to what you posted above:

| `macro1`
| eval flag="match"
| append maxtime=1800 timeout=1800
[ search `macro2` ``` keyword "search" is required in a subsearch ```
| eval flag="metric"]
| stats values(flag) as flag by LocationId | where flag="metric" and flag!="match"
| table LocationId

Hope this helps.

Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

You're unable to compare times because times were discarded by the stats command. You can add them back in this way.

| savedsearch "savedsearch1"
| eval flag="match"
| append maxtime=1800 timeout=1800
[ savedsearch "savedsearch2"
| eval flag="metric"]
| stats values(flag) as flag, min(_time) as _time by LocationId 
| where flag="metric" and flag!="match"
| table LocationId
---
If this reply helps you, Karma would be appreciated.
0 Karma

Splunk_321
Path Finder

@richgalloway

Thanks for your response. I don't want to get min of time out of those two locationIds. The moment I see LocationId in both the reports, I want to ensure that savedsearch1._time > savedsearch2._time.

In a simple coding language it could be.....

################################################

var LocationIdList;

//add if it is present in savedsearch2 but not in savedsearch1

if(savedsearch2.contains(LocationId) && !savedsearch1.contains(LocationId)){

LocationIdList.add(LocationId);

}

//if it is present in both

if (savedsearch2.contains(LocationId) && savedsearch1.contains(LocationId)){

//add only when savedsearch1 occurs later than savedsearch2

if (savedsearch1._time>savedsearch2._time){

LocationIdList.add(LocationId);

}

}

################################################

0 Karma

richgalloway
SplunkTrust
SplunkTrust

To keep the _time field from both searches, it's necessary to rename the field in one or both searches before combining the results.

 

| savedsearch "savedsearch1"
| eval flag="match"
| rename _time as time1
| append maxtime=1800 timeout=1800
  [ savedsearch "savedsearch2"
  | eval flag="metric"
  | rename _time as time2
  ]
| stats values(*) as * by LocationId 
| where (flag="metric" AND flag!="match" AND time1 > time2)
| table LocationId time1 time2

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

My mistake.  I've corrected my answer.

---
If this reply helps you, Karma would be appreciated.
0 Karma

Splunk_321
Path Finder

@richgalloway  Thanks for your reply.

I tried something like below, but what I realized is stats command is only propagating only LocationId and flag fields and hiding the time. Hence not able to make time comparison. If I check matches_time, metrics_time fields after stats command, those are blank.

| savedsearch "savedsearch1" 
| eval flag="match" 
| rename _time as matches_time
| append maxtime=1800 timeout=1800 
    [ savedsearch "savedsearch2"
    | eval flag="metric" 
    | rename _time as metrics_time] 
| stats values(flag) as flag by LocationId 
| where (flag="metric" and flag!="match") or (flag="metric" and flag="match" and metrics_time<matches_time) 
| table LocationId

 Is there any other way to achieve this. May be a different one rather than append mechanism.

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...