Knowledge Management

How to find the matching event from one index based on another index field?

sangs8788
Communicator

Below is my event details from two different indexes,

Event from index= Query_details
SPID="111", LOGIN="USER1",MSG="Sleeping query got killed", STARTTIME="2019-04-11 23:53:38.517", KILLTIME="2019-04-11 23:54:24.083", RUNDURATION ="100", TRANSACTIONID = 123

Event from index = Request_details
REQUEST_NAME ="Request1", REQUEST_ID="fkjgper6256f03ba90", RESPONSE_MS="2000", DB_SPID="743,111", DB_TX_ID="123, 439, 695, 3384, 5495", ORGNAME="ORG1"

I want to combine these two queries and display the request details along with database query details. How to go about it ? 
The field based on which i am trying to combine is TRANSACTIONID from index=Query_details with DB_TX_ID from index=Request_details

Note that TRANSACTIONID from index1 is int (TRANSACTIONID = 123) where in DB_TX_ID from index2 is string containing more than one id (DB_TX_ID="123, 439, 695, 3384, 5495").

I dont think i can do a join here since it is more of contains/like functionality. What other means can i do ? Could you please let me know. End result i want to display below way,

SPID | TRANSACTIONID | DB_TX_ID |REQUEST_NAME | LOGIN | KILLTIME | RUNDURATION | RESPONSE_MS

I tried something like below (definitely wrong) which results only the first index fields Query Details, not the matching Request Details

 index="Query_details"  TRANSACTIONID = 123  |table  SPID, TRANSACTIONID, LOGIN, MSG, KILLTIME, RUNDURATION | append [search index="Request_details"  _raw = "*"+ TRANSACTION_ID + "*"  | table REQUEST_NAME, RESPONSE_MS, DB_TX_ID ]

Could someone help me on this

Thanks

Tags (2)
0 Karma

woodcock
Esteemed Legend

Try this:

(index= Query_details OR index = Request_details)
| makemv DB_SPID delim=","
| eval SPID=coalesce(SPID, DB_SPID)
| makemv DB_TXID delim=","
| eval TRANSACTIONID=coalesce(TRANSACTIONID, DB_TX_ID)
| fields - DB_*ID
| stats values(*) AS * BY TRANSACTIONID SPID
| table SPID TRANSACTIONID REQUEST_NAME LOGIN KILLTIME RUNDURATION RESPONSE_MS
0 Karma

burwell
SplunkTrust
SplunkTrust

Hi @sangs8788. Your Request_details index has no timestamps so the time for the event will be the index time. That will mean comparing events in Request_details and Query_details possibly compute intensive if you have a lot of records and if these two don't have times near each other.

In terms of a solution, you could use the transaction command. Again over a large number of events this might be too slow.

The idea is that in order to compare TRANSACTIONID, if the format in Request_details is always up to the first comma, then just use rex to pull out everything up to the first comma. And then you have two TRANSACTIONIDs that are the same.

index=Query_details OR index=Request_details 
| rex field=DB_TX_ID "(?<TRANSACTIONID>\d+)\,"
| transaction TRANSACTIONID
| table SPID TRANSACTIONID  DB_TX_ID  REQUEST_NAME  LOGIN KILLTIME  RUNDURATION  RESPONSE_MS
0 Karma

riddhichandaran
Explorer

Hey @burwell
I think this query returns only data of Query_details index
you have to write

 index=Query_details OR index=Request_details | your search Query

to get data from both indexes

0 Karma

sangs8788
Communicator

Yes @burwell, your query only returns Query_details info. Not the related Request_details. @riddhichandarana What do you mean "Your search Query". That is where my trouble is

0 Karma

riddhichandaran
Explorer

Try this:

index=Query_details OR index=Request_details 
| rex field=DB_TX_ID "(?<TRANSACTIONID>\d+)\,"
 | transaction TRANSACTIONID
 | table SPID TRANSACTIONID  DB_TX_ID  REQUEST_NAME  LOGIN KILLTIME  RUNDURATION  RESPONSE_MS
0 Karma

sangs8788
Communicator

This is what @burwell has given too. It doesnt work. It lists only the Query details not the related Request_details

0 Karma

riddhichandaran
Explorer

Hey can you try the above query it will fetch result from both the indexes
alt text

0 Karma

sangs8788
Communicator

RUNDURATION, LOGIN, KILLTIME are from Query_details index. Those are not getting listed for me. only the ones which are under the Request_details are getting listed.

0 Karma

riddhichandaran
Explorer

please run the below query
because in the query of @burwell there is a small mistake he writes
index=Query_details OR Request_details
which is incorrect it should be :
index=Query_details OR index=Request_details

    index=Query_details OR index=Request_details 
| rex field=DB_TX_ID "(?<TRANSACTIONID>\d+)\," 
| transaction TRANSACTIONID 
| table SPID TRANSACTIONID DB_TX_ID REQUEST_NAME LOGIN KILLTIME RUNDURATION RESPONSE_MS
0 Karma

sangs8788
Communicator

Yea i modified that and i still get only Request_details. Not the Query_details.

0 Karma

sangs8788
Communicator

These fields from Query_details - RUNDURATION, LOGIN, KILLTIME arent getting listed

0 Karma

sangs8788
Communicator

I want to list the event from Request_details only when there is the TransactionId present under Query_details index. I dont want to list all Request_details.

0 Karma

riddhichandaran
Explorer

Can you please try this search Query

 index=Request_details 
    | eval Any = DB_TX_ID 
    | makemv delim=", " Any 
    | mvexpand Any 
    | rename Any as TRANSACTIONID 
    | join TRANSACTIONID 
        [| search index=Query_details] 
    | table SPID TRANSACTIONID DB_TX_ID REQUEST_NAME LOGIN KILLTIME RUNDURATION RESPONSE_MS
0 Karma

sangs8788
Communicator

Something like this is what i need. Except i want in this way,

index=Query_details |  join TRANSACTIONID  [search index=Request_details 
     | eval Any = DB_TX_ID 
     | makemv delim="," Any 
     | mvexpand Any 
     | rename Any as TRANSACTIONID | fields DB_TX_ID, TRANSACTIONID, REQUEST_NAME, RESPONSE_MS ] 
     | table SPID TRANSACTIONID DB_TX_ID REQUEST_NAME LOGIN KILLTIME RUNDURATION RESPONSE_MS
0 Karma

sangs8788
Communicator

Query details contains the transaction id of the query which get killed. Request details contains the Thread which triggers this transaction. One request could trigger more than one transactioon id.

What i want is to list the Killed Query details along with Request which triggered this Query.
In both indexes, the field which is common is transaction_id.

0 Karma

sangs8788
Communicator

And also i dont see any mapping between two indexes in this query. I want to list the event from Request_details only when there is the TransactionId is present under Query_details.

0 Karma

sangs8788
Communicator

Is there way i can say list the details from index=Request_details where _raw contains TRANSACTION_ID from index=Query_details.

index=Request_details | [search index=Query_details | fields TRANSACTION_ID, KILL_TIME, RUNDURATION, SPID ] | search _raw ="" + TRANSACTION_ID + "" | table R, RESPONSE_MS

0 Karma

riddhichandaran
Explorer

For this query, you need to perform join operation so that you can see data of both fields

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...