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

[Splunk_321]

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!

[yuanliu]

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.

[richgalloway]

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

[Splunk_321]

@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);

}

}

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

[richgalloway]

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

[richgalloway]

My mistake.  I've corrected my answer.

[Splunk_321]

@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.