Splunk Search

Join 2 searches with the base search in right join

Communicator

Hi all!

I recently discovered that i can wire in my xml dashboard a search and then calling it in N other searches in N other different panels.

I found it really useful since in my dashboard the searches often share some part.

Now I need to join my base search with another search but I have the following problems:

  • I cannot use append and then stats max since In the second search I have more than one row with the same join key because the base search contains the average execution duration of some processes (so one for each process name) and the 2nd search contain the processes execute in the time span i choose (so they can be more than one for each process name).

alt text

  • So the optimal solution would be a right join, bust Splunk does not have a right join (and i sincerely don't understand why!)

  • But at the same time I cannot exchange the searches for using a left join because a base search cannot be used like this

    second search
    | join type=left key [base search]

I trued and if hard code the 2 searches together with the 2nd search in left join with the base search it work perfectly

SplunkTrust
SplunkTrust

suppose there were data in the base search for process c (pippo3) and in the second search for process d (blah4), what results are you looking for in those cases?

a pippo1 bla1
a pippo1 bla2
b pippo2 bla3
c pippo3        do you want this record?
d        bla4   do you want this record?

Given somesoni2's code, record C will appear in the results. If you want record D also, then modify somesoni2's code as following -

base search 
| append [2nd search | eval flag=1]
| eventstats values(basesearchfield) as basesearchfield sum(flag) as sumflag by process
| where flag=1 or isnull(sumflag)
0 Karma

SplunkTrust
SplunkTrust

Give this a try. The events stats will populate basesearchfield to all rows of search2, so you can just filter rows for search2 using where clause.

base search | append [2nd search | eval flag=1]| eventstats values(basesearchfield) as basesearchfield by process
| where flag=1

Communicator

OMG!

It seems to work but i don't understand why!

what does eventstats values(basesearchfield) as basesearchfield by process does?

Thanks

0 Karma

Splunk Employee
Splunk Employee

@andreafebbo - Did the answer provided by somesoni2 help provide a working solution to your question? If yes, please don't forget to resolve this post by clicking "Accept". If no, please leave a comment with more feedback. Thanks!

0 Karma

SplunkTrust
SplunkTrust

It basically add (updates if same name is used) based on the aggregation done by eventstats query, without modifying the number of results. e.g. If your data was like this after append. hypen is null here)

process basesearchfield 2ndsearchfield1 2ndsearchfield2
A                     b1                          -                            -
B                     b2                          -                            -
A                     -                             s1                         s2
B                     -                             s3                         s4
B                     -                             s5                         s6

After eventstats, will become

process basesearchfield 2ndsearchfield1 2ndsearchfield2
A                     b1                          -                            -
B                     b2                          -                            -
A                     b1                             s1                         s2
B                     b2                            s3                         s4
B                     b2                            s5                         s6
0 Karma

Communicator

I dont understand how is possible that if i put my 2 search in a dashboard everything works.

Bu if in that dashboard i click the magnifying lens the research (the 2 researches together) comes out and it does not work any more.
It cannot evaluate the variable timing because AVGDuration is empty.

This is the full code:

(index and source)  earliest=-31d latest=now  (Result="OK" OR LogType ="START")
                | eval EndTime = if(Result="OK", _time, null)
                | eval StartTime = if(LogType ="START", _time, null)
                | stats Latest(StartTime) as StartTime Latest(EndTime) as EndTime by PackageName ExecutionInstanceGUID
                | eval Duration = (EndTime-StartTime)
                | where (Duration != "" OR Duration >= 0)
                | stats avg(EndTime) as AVGEndTime avg(Duration) as AVGDuration avg(StartTime) as AVGStartTime by PackageName |  table PackageName, AVGDuration
            | append [ search (index and source)  earliest=-24h@h latest=now
                     | eval StartTime = if(LogType = "START", _time, null)
                     | eval EndTime = if(LogType = "END", _time, null)
                     | stats Latest(StartTime) as StartTime Latest(EndTime) as EndTime Latest(Result) as Result by PackageName ExecutionInstanceGUID
                     | eval EndTime = if(isnull(EndTime), now(), EndTime)
                     | eval duration = (EndTime-StartTime)
                     | where (duration != "" OR duration >= 0) 
                     | table StartTime PackageName Result duration
                     | sort StartTime
                     |eval flag = 1
                     ]
            | eventstats values(AVGDuration) as AVGDuration by PackageName
            | where flag=1
            | fields - flag
            | eval Timing = if(duration > AVGDuration, "InLate", "OnTime")
            | eval Status = if(isnull(Result), Timing, Result)
            | eval Duration = duration * 1000

I think the problem is that when the 2 searches come together, the first one (base) does not work anymore for some reason, but in the dashboard everything fine.
Please help.

0 Karma

SplunkTrust
SplunkTrust

Try to run the queries in part and see where it's failing. Run everything before append, then add append subsearch and then add rest.

0 Karma

Communicator

I did that.
The error comes in the append: the appendo command add the empty column AVGDuration but it does not append any rows.

0 Karma

Communicator

If I invert the orders of the queries it works 😞

0 Karma

SplunkTrust
SplunkTrust
0 Karma