I am using this query
index=dtwn sourcetype = sessionserver Serverid=$sev$ | dedup _raw | join Serverid [search index=dtwn sourcetype=latencyuser $user$ | rename Server AS Serverid] | chart max(Max_Session_Startup_sec) AS "Max Session Startup - Sec" max(Max_Profile Load_sec) AS "Max Profile Load - Sec" max(Max_Obtain_Creds_sec) AS "Max Obtain Credentials" max(Max_Logon_Script_sec) AS "Max Login Script (secs)" avg(Avg_Session_Startup_sec) AS "Avg Session Startup - Sec" avg(Avg_Profile_Load_sec) AS "Avg Profile Load - Sec" avg(Avg_Obtain_Creds_sec) AS "Avg Obtain Credentials" avg(Avg_Logon_Script_sec) AS "Avg Login Script (secs)" by Serverid | head 20
I want the common values as well as the non common ones: common ones only for the same dashboard and non commons for the drilldown purpose.
Is there any way that I can use join
with If
and case
statements?
Yes, like this:
(index=dtwn sourcetype = sessionserver Serverid=$sev$) OR
(index=dtwn sourcetype = latencyuser $user$)
| eval Serverid = coalesce(Serverid, Server)
| stats dc(sourcetype) AS sourcetypes values(*) AS *
max(Max_Session_Startup_sec) AS "Max Session Startup - Sec"
max(Max_Profile Load_sec) AS "Max Profile Load - Sec"
max(Max_Obtain_Creds_sec) AS "Max Obtain Credentials"
max(Max_Logon_Script_sec) AS "Max Login Script (secs)"
avg(Avg_Session_Startup_sec) AS "Avg Session Startup - Sec"
avg(Avg_Profile_Load_sec) AS "Avg Profile Load - Sec"
avg(Avg_Obtain_Creds_sec) AS "Avg Obtain Credentials"
avg(Avg_Logon_Script_sec) AS "Avg Login Script (secs)" by Serverid
The above base search gives the fully merged set (full join), assuming that each event has a Serverid (or Server).
For each function, tack on the appropriate final search string to perform the desired logic:
For XOR (outer join):
| where sourcetypes = 1
For left join:
| where sourcetype = sessionserver
For right join:
| where sourcetype = latencyuser
For inner join:
| where sourcetypes > 1
The output which we are getting from the current Query :
Serverid sourcetypes Server ClientIP Latency RoundTrip
AKLCTX1059 1 AKLCTX1059 10.8.141.138 0 108
10.8.141.139 103 119
10.8.192.103 13 122
10.8.192.109 130 124
10.8.192.135 14 130
10.8.192.237 15 154
10.94.12.233 16 161
172.20.243.66 18 164
Where, or more importantly why, are you using Userid="*"
? I did not specify that anywhere in my solution. What are you trying to modify? Have you even tried my solution as-is?
When I am using Userid=*
in where
clause (not values(*)
clause), I am not getting the expected output. This query is considering only sourcetype 1
(i.e latencyuser
) and giving the related records but not the records related to sourcetype 2
(i.e sessionserver
)