How to join the same sourcetype - Basically inner join with same sourcetype with different type of search string and compare the value (IN) condition. )

[shanaiyappan]

I am using the below query to achieve IN condition in same source. Basically I am achieving how many Order has been confirmed from hold. I got what I need but is there a better way of doing in.

In simple words SQL IN query from same table.

sourcetype="sourcetype1*" "called with OrderId : * and OperationType : confirm*" | rex field=message "OrderId : (?.?) and" | table OrderId | join type=inner OrderId
[| search sourcetype="sourcetype1" "called with OrderId :, Type : mobile and OperationType : hold" | rex field=message "OrderId : (?.*?,)" | table OrderId] |stats count by OrderId.

[shanaiyappan]

Thank you for the answer let me check that

[arjunpkishore5]

In your case, just use a subsearch

sourcetype="sourcetype1*" "called with OrderId : and OperationType : confirm" 
    [| search sourcetype="sourcetype1" "called with OrderId :, Type : mobile and OperationType : hold" 
    | rex field=message "query: (?.*?,)" 
    | table query] 
| rex field=message "OrderId : (?.?) and" 
| stats count by OrderId.

In the above sample, the inner search returns order id's which have hold. - https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchTutorial/Useasubsearch

Why I changed the name to query instead of OrderId in the inner search - https://docs.splunk.com/Documentation/Splunk/latest/Search/Changetheformatofsubsearchresults