topic Re: How to use append to work like Inner join in Splunk Search

How to use append to work like Inner join

Punnu — Thu, 03 Apr 2025 02:48:41 GMT

I have two searches and I only want to find rows which has common MessageID . Currently it is returning extra row because of second search .

Query before Or is returning 100 records and after OR one was returning 110 rows and for those extra 10 rows messageID in first is NULL , So I want to drop those messages . Please help how can i change this query to make it work . I am trying to find count of matched IDs and list of all such ids

```query for apigateway call``` (index=aws_np earliest="03/28/2025:13:30:00" latest="03/28/2025:14:35:00" Method response body after transformations: sourcetype="aws:apigateway" business_unit=XX aws_account_alias ="XXXX" network_environment=xxXXX source="API-Gateway-Execution-Logs*" (application="xXXXXX" OR application="xXXXX-xXX") | rex field=_raw "Method response body after transformations: (?<json>[^$]+)" | spath input=json path="header.messageID" output=messageID | spath input=json path="payload.statusType.code" output=status | spath input=json path="payload.statusType.text" output=text | spath input=json path="header.action" output=action | where status=200 and action="Create" ` | rename _time as request_time | table messageID, request_time) | append ```query for 2nd query call``` [ search kubernetes_cluster="eks-XXX*" index="aws_XXX" sourcetype = "kubernetes_logs" source = *XXXX* "sendData" | rex field=_raw "sendData: (?<json>[^$]+)" | spath input=json path="header.messageID" output=messageID | rename _time as pubsub_time | table messageID, pubsub_time ] | stats values(request_time) as request_time values(pubsub_time) as pubsub_time by messageID

Re: How to use append to work like Inner join

yuanliu — Thu, 03 Apr 2025 05:56:48 GMT

If all you want is to remove those extra messageID's, you can simply remove those with null request_time, like

| search request_time = *

Re: How to use append to work like Inner join

livehybrid — Thu, 03 Apr 2025 06:28:33 GMT

Hi @Punnu

To achieve an inner join effect and only keep results where messageID exists in both searches, you can filter the results after your stats command to remove rows where request_time is null (meaning the messageID only existed in the second search).

Add | where isnotnull(request_time) after your stats command.

🌟 Did this answer help you? If so, please consider:

Adding kudos to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

Re: How to use append to work like Inner join

Punnu — Thu, 03 Apr 2025 10:35:14 GMT

hi @livehybrid Thank you for reply . I would like to ask one more question . Post filtering out records how we can find count of messageID

Re: How to use append to work like Inner join

livehybrid — Thu, 03 Apr 2025 13:36:52 GMT

Hi @Punnu

If you wanted a count of the unique messageID after filtering then a simple stats count should do, as we've already stats by messageID

| stats count

🌟 Did this answer help you? If so, please consider:

Adding kudos to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing.

Re: How to use append to work like Inner join

isoutamo — Thu, 03 Apr 2025 14:41:21 GMT

Old post how you could emulate joins in splunk https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-join-and-the/m-p/391288/thread-id/113948