topic Re: Joining two queries not giving the desired result in Splunk Search

Joining two queries not giving the desired result

Deprasad — Thu, 06 Jul 2023 11:09:42 GMT

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

Re: Joining two queries not giving the desired result

ITWhisperer — Thu, 06 Jul 2023 11:21:47 GMT

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

Re: Joining two queries not giving the desired result

Deprasad — Thu, 06 Jul 2023 13:03:36 GMT

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.

Re: Joining two queries not giving the desired result

ITWhisperer — Thu, 06 Jul 2023 13:07:02 GMT

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?

Re: Joining two queries not giving the desired result

ITWhisperer — Thu, 06 Jul 2023 13:08:34 GMT

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

Re: Joining two queries not giving the desired result

Deprasad — Thu, 06 Jul 2023 13:29:51 GMT

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.

Re: Joining two queries not giving the desired result

ITWhisperer — Thu, 06 Jul 2023 15:18:17 GMT

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?

Re: Joining two queries not giving the desired result

Deprasad — Thu, 06 Jul 2023 16:33:40 GMT

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.

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

Re: Joining two queries not giving the desired result

Deprasad — Thu, 06 Jul 2023 16:39:53 GMT

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

Re: Joining two queries not giving the desired result

ITWhisperer — Thu, 06 Jul 2023 17:05:34 GMT

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")

Re: Joining two queries not giving the desired result

Deprasad — Fri, 07 Jul 2023 10:34:16 GMT

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.