Joining two queries not giving the desired result

[Deprasad]

I have 2 queries and joining it with "Join" using the common field "SessionID".

With  the below query I'm just getting the results if there are results from both the search. If there is no result for either the parent search or the sub search the result is not getting printed.

For example if there is no LogoutTime available from the sub search, the results of parent search is not getting printed and. Is there any way to achieve the desired result.

index = test "testrequest"

| rex "(?:.+email\=)(?<Email>[a-zA-Z0-9_\-\@\.]+)"

| rex "(?:.+trasactionId\=)(?<TransactionID>[a-zA-Z0-9-]+)"

| rex "(?:.+TransactionTime\=)(?<LoginTime>[a-zA-Z0-9\s:]+EDT)"

| rex "(?:.+Status\=)(?<Status>\w+)"

| rex "(?:.+TimeTaken\=)(?<TimeTaken>\d+)"

| rex "(?:.+\+\+)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"

| table Email,TransactionID,LoginTime,Status,TimeTaken,SessionID

| join SessionID

[search index = test "testrespone"

| rex "(?:.+TransactionTime\=)(?<LogoutTime>[a-zA-Z0-9\s:]+EDT)"

| rex "(?:.+SessionId\=)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"

| table SessionID,LogoutTime]

| table Email,TransactionID,LoginTime,Status,TimeTaken,SessionID,LogoutTime

[Deprasad]

I'm still not getting the LogoutTime. 
Also please note that the field from first query such as Email, TransactionID, LoginTime will have multiple values. PFA.

[ITWhisperer]

Would "joining" by transaction id be a better way of correlating the events?

[Deprasad]

The transaction ID is different for both the events, only common filed is "SessionID". there are few other fields which are common in name but has different values. 

The logic is, Single SessionID will have 'n' no of Email,TransactionID,LoginTime but only have one LogoutTime in a different event.

[ITWhisperer]

Try this

index = test "testrequest" OR "testrespone"
| rex "(?:.+email\=)(?<Email>[a-zA-Z0-9_\-\@\.]+)"
| rex "(?:.+trasactionId\=)(?<TransactionID>[a-zA-Z0-9-]+)"
| rex "(?:.+TransactionTime\=)(?<TransactionTime>[a-zA-Z0-9\s:]+EDT)"
| rex "(?:.+Status\=)(?<Status>\w+)"
| rex "(?:.+TimeTaken\=)(?<TimeTaken>\d+)"
| rex "(?:.+\+\+)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"
| eval LoginTime=if(searchmatch("testrequest"),TransactionTime,null())
| eval LogoutTime=if(searchmatch("testresponse"),TransactionTime,null())
| eventstats values(LogoutTime) as LogoutTime by SessionID
| table Email,TransactionID,LoginTime,Status,TimeTaken,SessionID,LogoutTime

If this is still not working, it would imply that the extract of the transaction time is not working for the testresponse events or that the searchmatch for testresponse is not finding the right events. Either way, in order to be more help, please can you share some anonymised sample events (both testrequest and testresponse) so we can see what might be the issue?

[Deprasad]

The Query works but some formatting is needed. 
For the test request I've multiple rows(Ex: multiple loginTime) and Test response will be only one(Ex: single logoutTime).

So the output is coming like first few rows for request and the followed by response as last row.

I need the output like something similar below:

Email | transactionID | logintime | status  | timetaken | sessionID | LogoutTime 
 xxx       xxxxxxxxxxx  02:10:00      xxxxx     xxxxxxxx    xxxxxxxxx    02:45:15    

yyy        yyyyyyyyyyy  02:15:15      yyyyy     yyyyyyyy     xxxxxxxxx   02:45:15

zzz        zzzzzzzzzzz  02:25:15     zzzzz       zzzzzzzz    xxxxxxxxx    02:45:15

Test request event: 
Test request:email=xxxx:trasactionId=xxx-xxx-xxx-xxx-xxx:RequestType=dummy:TimeTaken=16209,TransactionTime=02:10:00:SessionId=xxxxxxx:Status=SUCCESS

Test response event:
Test response:email=yyyyyy:trasactionId=yyyyy-yyyyy-yyyy-yyyy-yyyyy:TimeTaken=381:TransactionTime=02:45:15: SessionId=xxxxxxx:Status=SUCCESS

[ITWhisperer]

So, it sounds like you have everything you need apart from removing the testresponse line, so just add the following

index = test "testrequest" OR "testrespone"
| rex "(?:.+email\=)(?<Email>[a-zA-Z0-9_\-\@\.]+)"
| rex "(?:.+trasactionId\=)(?<TransactionID>[a-zA-Z0-9-]+)"
| rex "(?:.+TransactionTime\=)(?<TransactionTime>[a-zA-Z0-9\s:]+EDT)"
| rex "(?:.+Status\=)(?<Status>\w+)"
| rex "(?:.+TimeTaken\=)(?<TimeTaken>\d+)"
| rex "(?:.+\+\+)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"
| eval LoginTime=if(searchmatch("testrequest"),TransactionTime,null())
| eval LogoutTime=if(searchmatch("testresponse"),TransactionTime,null())
| eventstats values(LogoutTime) as LogoutTime by SessionID
| table Email,TransactionID,LoginTime,Status,TimeTaken,SessionID,LogoutTime
| where searchmatch("testrequest")

[Deprasad · Accepted solution]

Still the same format issue, where getting few rows of test request and then testresponse which has the logouttime as the last row.

Interestingly I'm getting the desired result for below query with just adding the Join type as Left. It seems splunk takes Join type as Inner by default which gives results if there is a match between search and subsearch.

index = test "testrequest"
| rex "(?:.+email\=)(?<Email>[a-zA-Z0-9_\-\@\.]+)"
| rex "(?:.+trasactionId\=)(?<TransactionID>[a-zA-Z0-9-]+)"
| rex "(?:.+TransactionTime\=)(?<LoginTime>[a-zA-Z0-9\s:]+EDT)"
| rex "(?:.+Status\=)(?<Status>\w+)"
| rex "(?:.+TimeTaken\=)(?<TimeTaken1>\d+)"
| eval TimeTaken=TimeTaken1/1000
| rex "(?:.+\+\+)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"
| rex "(?:.+FailureReason\=)(?<FailureReason>[\w\s]+)"
| table Email,TransactionID,LoginTime,Status,TimeTaken,SessionID
| join type=left SessionID
[search index = test "testresponse"
| rex "(?:.+TransactionTime\=)(?<LogoutTime>[a-zA-Z0-9\s:]+EDT)"
| rex "(?:.+SessionId\=)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"
| table SessionID,LogoutTime ]
| table Email,TransactionID,LoginTime,TimeTaken,SessionID,LogoutTime,Status,FailureReason

Thanks for the wonderful conversation and help that led to this excellent learning. 

[Deprasad]

The below query also works but its grouping the email,transactionid,logintime filed together in ascending order w.r.t the session ID causing mismatch between email,transactionid,logintime.

index = test "testrequest"
| rex "(?:.+email\=)(?<Email>[a-zA-Z0-9_\-\@\.]+)"
| rex "(?:.+trasactionId\=)(?<TransactionID>[a-zA-Z0-9-]+)"
| rex "(?:.+TransactionTime\=)(?<LoginTime>[a-zA-Z0-9\s:]+EDT)"
| rex "(?:.+Status\=)(?<Status>\w+)"
| rex "(?:.+TimeTaken\=)(?<TimeTaken1>\d+)"
| rex "(?:.+\+\+)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"
| table Email,TransactionID,LoginTime,Status,TimeTaken,SessionID
| append
[search index = test  "testresponse"
| rex "(?:.+TransactionTime\=)(?<LogoutTime>[a-zA-Z0-9\s:]+EDT)"
| rex "(?:.+SessionId\=)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"
| table SessionID,LogoutTime]
| stats values(*) as * by SessionID
| table Email,TransactionID,LoginTime,TimeTaken,SessionID,LogoutTime,Status

[ITWhisperer]

Is multiple values (for the same session id) what you were expecting?

Do you want multiple events for the same session id?

Perhaps if you could share some of your events (anonymised of course) to give a clearer picture of what you are dealing with?

[ITWhisperer]

Rather than using join (which is usually best avoided), try something like this

index = test "testrequest" OR "testrespone"
| rex "(?:.+email\=)(?<Email>[a-zA-Z0-9_\-\@\.]+)"
| rex "(?:.+trasactionId\=)(?<TransactionID>[a-zA-Z0-9-]+)"
| rex "(?:.+TransactionTime\=)(?<TransactionTime>[a-zA-Z0-9\s:]+EDT)"
| rex "(?:.+Status\=)(?<Status>\w+)"
| rex "(?:.+TimeTaken\=)(?<TimeTaken>\d+)"
| rex "(?:.+\+\+)(?<SessionID>[a-zA-Z0-9-_:@.]+)(?:\:Status)"
| eval LoginTime=if(searchmatch("testrequest"),TransactionTime,null())
| eval LogoutTime=if(searchmatch("testresponse"),TransactionTime,null())
| stats values(Email) as Email values(TransactionID) as TransactionID values(LoginTime) as LoginTime values(Status) as Status values(TimeTaken) as TimeTaken values(LogoutTime) as LogoutTime by SessionID