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:
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
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)
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
OMG!
It seems to work but i don't understand why!
what does eventstats values(basesearchfield) as basesearchfield by process
does?
Thanks
@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!
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
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.
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.
I did that.
The error comes in the append: the appendo command add the empty column AVGDuration but it does not append any rows.
If I invert the orders of the queries it works 😞