How to use append to work like Inner join

[Punnu]

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 

[isoutamo]

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-joi...

[livehybrid]

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

[Punnu]

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 

[livehybrid · Accepted solution]

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.

[yuanliu]

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

| search request_time = *