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
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
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
The key thing is to avoid BOTH join
and subsearch
, which is generally possible, like I did here.
Hi When I am running the query I am getting this error
Error in 'eval' command: The expression is malformed. Expected ).
Please help.
I was missing two )
; I re-edited and tested it.
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())
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.
If you can provide logs, we can create queries that do not use Join
.
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.