topic How to join two savedsearches based on a column and do a time comparison? in Splunk Search

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

Splunk_321 — Mon, 05 Dec 2022 17:05:06 GMT

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

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

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

Any help would be appreciated!

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

richgalloway — Mon, 05 Dec 2022 19:45:38 GMT

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

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

yuanliu — Mon, 05 Dec 2022 19:51:41 GMT

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:

Hope this helps.

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

Splunk_321 — Tue, 06 Dec 2022 11:40:11 GMT

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

}

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

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

richgalloway — Wed, 07 Dec 2022 13:29:07 GMT

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

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

Splunk_321 — Wed, 07 Dec 2022 08:30:29 GMT

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

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

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

richgalloway — Wed, 07 Dec 2022 13:29:29 GMT

My mistake. I've corrected my answer.