Is there any way that we can use Join with if or case statement.

[Lovika]

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?

[woodcock]

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

[Lovika]

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

[woodcock]

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?

[Lovika]

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)