How to combine Multiple joins with subsearch?

[ravikanthbadugu]

Hi ,

I have 3 joins with subsearch ,how can I combine those 3 joins and make as one join?

join new1 max=0 [search index=abc Source=WeeklyData earliest=@d+07h+30m latest=@d+14h+30m |rename xy AS new1, ExtraInfo AS "cd"]|rename new1 AS new2
|join new2 max=0 [search index=abc Source=WeeklyData earliest=@d+14h+30m latest=@d+16h+30m |rename xy AS new2, ExtraInfo AS "ef"]|rename new2 AS new3
| join new3 max=0 [search index=abc Source=WeeklyData earliest=-1d@d+16h+30m latest=@d+16h+30m |rename CurrentMouseName AS new3, _time AS "newtime", ExtraInfo AS "newinfo"]|table new3 oldInfo3 newinfo newtime

[woodcock · Accepted solution]

Try this:

(index=abc Source=WeeklyData earliest=@d+07h+30m latest=@d+14h+30m) OR
(index=abc Source=WeeklyData earliest=@d+14h+30m latest=@d+16h+30m) OR
(index=abc Source=WeeklyData earliest=-1d@d+16h+30m latest=@d+16h+30m)
| eval which=case(
   _time>=relative_time(now(), "@d+07h+30m") AND _time<=relative_time(now(), "@d+14h+30m"), "src1",
   _time>=relative_time(now(), "@d+14h+30m") AND _time<=relative_time(now(), "@d+16h+30m"), "src2",
   true(), "src3")
| eval joiner = if(which=="src3", CurrentMouseName, xy)
| eval cd = if(which=="src1", ExtraInfo, null())
| eval ef = if(which=="src2", ExtraInfo, null())
| eval xy = if(which=="src3", xy, null())
| eval newtime = if(which=="src3", _time, null())
| fields - _* ExtraInfo
| stats values(*) AS * BY joiner
| table joiner oldInfo3 newinfo newtime

You might also do well to check out timewrap:
https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Timewrap

[woodcock]

The key thing is to avoid BOTH join and subsearch, which is generally possible, like I did here.

[ravikanthbadugu]

Hi When I am running the query I am getting this error

Error in 'eval' command: The expression is malformed. Expected ).

Please help.

[woodcock]

I was missing two ); I re-edited and tested it.

[mstark31]

Why have you decided to use 3 joins?

From what I can tell, it looks like the major difference between the 3 different subsearches is the time window. Would it be possible to use an if or case statement to rename fields based on when the events occur?

An example:

| eval TimeDiv1_Start = relative_time(now(), "@d+7h+30m")
| eval TimeDiv1_End = relative_time(now(), "@d+14h+30m")
| eval TimeDiv2_Start = relative_time(now(), "@d+14h+30m")
| eval TimeDiv2_End = relative_time(now(), "@d+16h+30m")
| eval TimeDiv3_Start = relative_time(now(), "-1d@d+16h+30m")
| eval TimeDiv3_End = relative_time(now(), "@d+16h+30m")
| eval TimeDiff = now() - _time
| eval TimeGroup = case((TimeDiff >= TimeDiv1_Start AND TimeDiff < TimeDiv1_End), "Bucket1", (TimeDiff >= TimeDiv2_Start AND TimeDiff < TimeDiv2_End), "Bucket2", (TimeDiff >= TimeDiv3_Start AND TimeDiff < TimeDiv2_End), "Bucket1", 1=1, null())

[ravikanthbadugu]

Hi

This is query is regarding the Mouse Replacement.

we have bluetooth mouse connected to iMacs, so when the mouse are moved from one desk to another Desk , we need get that of those desk number to where the mouse has moved along with mouse details hence we are using multiple joins to get the data.

[to4kawa]

If you can provide logs, we can create queries that do not use Join.

[to4kawa]

index=abc Source=WeeklyData earliest=-1d@d+16h+30m latest=@d+16h+30m
| eval label=case(relative_time(_time,"@d+07h+30m") <= _time OR _time < relative_time(_time,"@d+14h+30m"),"first"
,relative_time(_time,"@d+14h+30m") <= _time OR _time < relative_time(_time,"@d+16h+30m"), "second")
| eval label2=if(relative_time(_time,"-1d@d+14h+30m") <= _time OR _time < relative_time(_time,"@d+16h+30m"),"third",NULL)
| eventstats dc(label) as label_count values(label2) as label2 by CurrentMouseName ExtraInfo
| where label_cont > 1 AND isnotnull(label2)
| table _time ,CurrentMouseName , ExtraInfo
| rename _time as newTime, CurrentMouseName as new3, ExtraInfo as newInfo

I made it without join.
I am not sure oldInfo3 field.