topic Re: Join two splunk searches in Splunk Search

Join two splunk searches

Athira — Wed, 13 Nov 2024 17:34:33 GMT

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

Re: Join two splunk searches

ITWhisperer — Wed, 13 Nov 2024 08:40:32 GMT

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

Re: Join two splunk searches

yuanliu — Thu, 14 Nov 2024 06:16:54 GMT

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.

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.

Re: Join two splunk searches

Athira — Thu, 14 Nov 2024 13:14:49 GMT

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}

Re: Join two splunk searches

ITWhisperer — Thu, 14 Nov 2024 13:27:03 GMT

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"

Re: Join two splunk searches

yuanliu — Thu, 14 Nov 2024 17:06:45 GMT

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

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