Join two splunk searches

[Athira]

in the outer query i am trying to pull  the ORDERS which is Not available .I need to match the ORDERS  which is Not available to with the ORDERS on Sub query. 

Result to be displayed  ORDERS  & UNIQUEID .  common field in two query is ORDERS 

my requirement is to use the combine two log statements  on "ORDERS"  and pull the ORDER and UNIQUEID in table  .

Below is the query i am using , but the result is pulling all ORDERS.  i want only the ORDERS and UNIQUEID from subquery to be displayed  which matches the ORDERS those  Not available in the first query  

index=source "status for : *  | "status for : * " AND "Not available"  | rex field=_raw "status for : (?<ORDERS>.*?)" | join ORDERS [search Message=Request for : * | rex field=_raw "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)" | rex field=_raw "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""] | table ORDERS UNIQUEID

[yuanliu]

Several problems with the illustrated search.  Most important one is use of join.  This is rarely the solution to any problem in Splunk.  Then, there is the problem of bad quotation marks.

Even without this, like @ITWhisperer says, illustrating useful data input is the best way to enable volunteers to help you.  Your illustrated search syntax is so mixed up I cannot even tell whether the two searches are using the same index.

Without any assumption about which data source(s) are used, you can get desired result using append and stats, assuming that my speculation about your syntax reflects the correct filter.

index=source "status for : *" "Not available"
| rex field=_raw "status for : (?<ORDERS>.*?)"
| fields ORDERS
| dedup ORDERS
| eval status = "Not available"
| append
    [search Message="Request for : *"
    | rex field=_raw "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)"
    | rex field=_raw "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""
    | fields ORDERS UNIQUEID
    | dedup ORDERS UNIQUEID]
| stats values(*) as * by ORDERS
| where status == "Not available"
| fields - status

Note in search command, AND between terms is implied and rarely need to be spelled out.

Now, if the two searches use the same index, it is perhaps more efficient to NOT use append. (Much less join.)  Instead, combine the two in one search.

index=source (("status for : *" "Not available") OR  Message="Request for : *")
| rex field=_raw "status for : (?<ORDERS>.*?)"
| rex field=_raw "data=[A-Za-z0-9-]+\|(?P<ORDERS>[\w\.]+)"
| rex field=_raw "\"unique\"\:\"(?P<UNIQUEID>[A-Z0-9]+)\""
| fields ORDERS UNIQUEID
| eval not_available = if(searchmatch("Not available"), "yes", null())
| stats values(*) as * by ORDERS
| where isnotnull(not_available)
| fields - not_available

[Athira]

hi @yuanliu thanks for tips, i tried running with the modified query. i got the results for ORDERS which are NOT AVAILABLE (which is the resultant of First search) . my requirement is to match ORDERS which are NOT AVAILABLE with ORDERS in second log . and display ORDERS  and UNIQUEID

sharing the data here 

INFO  [pool-9-thread-3]  CLASS_NAME=Q, METHOD=, MESSAGE=response status for TransNum: 629f2ad - 400 | Response - {"code":0001,"message":"Not available","messages":[],"additionalTxnFields":[]}

INFO  [pool-9-thread-7]  CLASS_NAME=Q, METHOD=, MESSAGE=Request for TransNum: 629f2ad - {"address":{"billToThis":true,"country":"","email":"******************","firstname":"FN","lastname":"LN","postcode":"0","salutation":null,"telephone":"+999999999999"},"deliveryMode":"","payments":[{"amount":10,"code":"BFD"}],"products":[{"currency":356,"price":600,"qty":2,"uniqueid":"QSTRUJIK"}],"refno":"629f2ad","syncOnly":true}

[yuanliu]

Thank you for sharing sample data.  This reveals additional weaknesses in the pursuit.

1. ORDERS seems to be the ID that comes after TransNum, not extracted by the original regex at all.  Sample data also show contradiction with your original index search.  But that is more for you to fine tune.
2. Part of the event is structured in JSON.  This should be treated as a structure not literal strings.  Extraction using regex is instable.

Based on your sample events (which suggest that the source is exactly the same, therefore subsearch is really a bad approach), this would be a much better strategy

index=source (("status for" "Not available") OR  "Request for")
| rex "TransNum: (?<ORDERS>\S+) .*?(?<JSON>{.+})"
| spath input=JSON path=products{}
| mvexpand products{}
| spath input=products{}
| stats values(uniqueid) as uniqueid by ORDERS

(Note the index search is purely based on sample data.  You may need to tune it to actually include the correct events.)  Your sample data will give you

ORDERS	uniqueid
629f2ad	QSTRUJIK

Here is an emulation of your data. Play with it and compare with real data and refine your search strategy

| makeresults
| fields - _*
| eval data = mvappend("INFO  [pool-9-thread-3]  CLASS_NAME=Q, METHOD=, MESSAGE=response status for TransNum: 629f2ad - 400 | Response - {\"code\":0001,\"message\":\"Not available\",\"messages\":[],\"additionalTxnFields\":[]}",
"INFO  [pool-9-thread-7]  CLASS_NAME=Q, METHOD=, MESSAGE=Request for TransNum: 629f2ad - {\"address\":{\"billToThis\":true,\"country\":\"\",\"email\":\"******************\",\"firstname\":\"FN\",\"lastname\":\"LN\",\"postcode\":\"0\",\"salutation\":null,\"telephone\":\"+999999999999\"},\"deliveryMode\":\"\",\"payments\":[{\"amount\":10,\"code\":\"BFD\"}],\"products\":[{\"currency\":356,\"price\":600,\"qty\":2,\"uniqueid\":\"QSTRUJIK\"}],\"refno\":\"629f2ad\",\"syncOnly\":true}")
| mvexpand data
| rename data as _raw
| extract
``` the above emulates
index=source (("status for" "Not available") OR  "Request for")
```

[ITWhisperer]

Assuming there is only one event per TransNum which has a message field and that TransNum is the correlating field, try something like this

| rex "TransNum:\s(?<TransNum>\S+)"
| rex "\"message\":\"(?<message>[^\"]+)"
| eventstats values(message) as message by TransNum
| where message="Not available"

[ITWhisperer]

Please share some anonymised sample events in code blocks (using the </> button above) so we can see what you are dealing with.