Splunk Enterprise

Nested Join?

bretai2k
New Member

I have 3 different searches I need to combine, where the secondary and tertiary searches need to be joined, and then the results of those searches need to be joined to another search. I've got the secondary and tertiary joined together, but am now trying to figure out how to do it so I can join their results to the main search I need to query the results of the other two against.

Here's what I have so far: (the two queries where I have to combine the results)

index=index1 search_name=search1
| eval field1=otherfield
| eval Starting_date = strftime(Startingdate, "%Y-%m-%d %H:%M:%S")
| join type=outer field2 [dbxquery connection=DB shortnames=t query="select * from Table where Column1 = 4 and Column2 = 3" | eval field2 = substr(Name,5, len(Name)-11) | eval NewDate = Date | table field2, NewDate | fields field2, NewDate]
| eval NewDateepoch = strptime(NewDate, "%Y-%m-%d %H:%M:%S")
| dedup field2, field1, Startingdate
| eval NewDateepoch = if(isnull(NewDateepoch), 0, NewDateepoch)
| eval Startingdate = if(isnull(Starting_date), 0, Startingdate)
| eval LatestTime = if(NewDateepoch>Startingdate, NewDateepoch, Startingdate)
| eval LatestTime = strftime(LatestTime, "%Y-%m-%d %H:%M:%S")
| table field2, field1, NewDate, Starting_date, LatestTime

I need to combine the results of this with another search where I will only match on field1

This is my first Splunk project, so I'm really new with all of this. Any insight anyone could provide would be greatly appreciated.

Thank you,
Ryan

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Just a couple of tweaks to your posted code - not to solve your issue, but to show a couple of efficiency improvements.

1) The| fields field2 NewDate command is redundant immediately after the | table field2 NewDate command. The primary difference is that "table" kills internal fields like _time, whereas "fields" does not -- there's nothing left for "fields" to do at that point.

2) If you are going to dedup on Startingdate, then don't create/reformat to Starting_date until after that dedup. Really, until just before the table command in this case.

3) "Coalesce(a,b)" is a quicker way to write "if(isnull(a),b,a)". You can even use it for more complex ones that would be hard to read like "coalesce(a,b,c)" is much more concise way to say the same as "if(isnull(a),if(isnull(b),c,b),a)" or "case(NOT isnull(a),a,NOT isnull(b),b,true(),c)"

4) In SQL, select only what you need, which in this case is Name and Date.

index=index1 search_name=search1
| eval field1=otherfield
| join type=outer field2 
    [dbxquery connection=DB shortnames=t query="select Name, Date from Table where Column1 = 4 and Column2 = 3" 
    | eval field2 = substr(Name,5, len(Name)-11) 
    | eval NewDate = Date 
    | table field2, NewDate]
| eval NewDateepoch = strptime(NewDate, "%Y-%m-%d %H:%M:%S")
| dedup field2, field1, Startingdate
| eval NewDateepoch = coalesce(NewDateepoch, 0)
| eval Startingdate = coalesce(Startingdate, 0)
| eval LatestTime = if(NewDateepoch>Startingdate, NewDateepoch, Startingdate)
| eval LatestTime = strftime(LatestTime, "%Y-%m-%d %H:%M:%S")
| eval Starting_date = strftime(Startingdate, "%Y-%m-%d %H:%M:%S")
| table field2, field1, NewDate, Starting_date, LatestTime
0 Karma

bretai2k
New Member

Thank you for the code tweaks, I have implemented your changes and it seems to be working well. Now if I could just figure out how to join the results of this with my other search, so I can do some timestamp evaluations, I will nearly be done with this report.

Thank you!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

1) Is your other search the left side, or the right side, or is it an inner join?
2) What is the approximate number of results on each side of the join?
3) Is the next part a search or a dbxquery or something else?

The trivial method is

| join field1 [your other search |table field1 foo1 foo2 bar1 bar2 ]
0 Karma

woodcock
Esteemed Legend

Try this:

|multisearch
[ search index=index1 search_name=search1
    | eval field1=otherfield
    | eval Starting_date = strftime(Startingdate, "%Y-%m-%d %H:%M:%S")
    | appendpipe [|dbxquery connection=DB shortnames=t query="select * from Table where Column1 = 4 and Column2 = 3" | eval field2 = substr(Name,5, len(Name)-11) | eval NewDate = Date | table field2, NewDate ]
    | stats values(*) AS * dc(index) AS num_sets BY field2 | search num_sets=1
    | eval NewDateepoch = strptime(NewDate, "%Y-%m-%d %H:%M:%S")
    | dedup field2, field1, Startingdate
    | eval NewDateepoch = if(isnull(NewDateepoch), 0, NewDateepoch)
    | eval Startingdate = if(isnull(Starting_date), 0, Startingdate)
    | eval LatestTime = if(NewDateepoch>Startingdate, NewDateepoch, Startingdate)
    | eval LatestTime = strftime(LatestTime, "%Y-%m-%d %H:%M:%S")
    | table field2, field1, NewDate, Starting_date, LatestTime ]
[ Your Other Search Here ]
| stats values(*) AS * BY field1
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

multisearch is never going to like a stats command in the middle (line 7).

0 Karma

bretai2k
New Member

It's currently returning the following error:

Unknown search command 'index'.

0 Karma

bretai2k
New Member

Okay, so I figured out the problem was not having search in front of index. When I added the new search, I began getting the error:

Error in 'multisearch' command: Multisearch subsearches may only contain purely streaming operations (subsearch 1 contains a non-streaming command.)

0 Karma

woodcock
Esteemed Legend

Try it now; I was missing the initial pipe character ( | ) for dbxquery.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...