Splunk Search

joining results from 2 different indexes where field in one of the search is extracted using rex

jainkul123
Explorer

I would like to join the result from 2 different indexes on a field named OrderId (see details below) and show field values from both indexes in a tabular form.
where
firstIndex -- OrderId, forumId
secondIndex -- OrderId, ItemName

Here my firstIndex does not contain the OrderId field directly and thus I need to use regex to extract that. However secondIndex contains the OrderId field.

index="firstIndex"  forumId=F1
| rex field=_raw ".*\]\[(?<OrderId>\w{3})\]\s*main" 
| join OrderId [search index="secondIndex"] 
| table OrderId, forumId, ItemName 
Tags (3)
0 Karma
1 Solution

woodcock
Esteemed Legend

Never use join; try this:

(index="firstIndex" AND forumId="F1") OR (index="secondIndex")
| rex ".*\]\[(?<OrderId2>\w{3})\]\s*main" 
| eval OrderId = coalesce(OrderId, OrderId2)
| stats values(forumId) AS forumId values(ItemName) AS ItemName BY OrderId

View solution in original post

Vijeta
Influencer

@jainkul123 This query given by @woodcock should work. Are you sure you are not doing any mistake in your rex command in extracting OrderID. Can you share your raw event from which you are extracting OrderId. When doing stats by order ID you cannot get 2 rows for same ID value.

(index="firstIndex" AND forumId="F1") OR (index="secondIndex")
 | rex ".*\]\[(?<OrderId2>\w{3})\]\s*main" 
 | stats values(forumId) AS forumId values(ItemName) AS ItemName BY OrderId

jainkul123
Explorer

@Vijeta, Yes, you are right. The 2 rows I was getting as the OrderId from different indexes had different case, one is lower and other is Capital. Solved it by using lower() method

| eval OrderId = lower( coalesce( OrderId, OrderId2) )

But the other issue still remains, i.e. the I still get the OrderIds from the second index for which the corresponding events from first index are filtered out. And that is the reason I am getting some entries in the table for which forumId is empty.
I only want the OrderId in the table which are from both indexes after the filtering.

0 Karma

jainkul123
Explorer

@Vijeta Never mind got it working by adding this at the end:
| where forumId != ""

Thanks for all your help. +1

0 Karma

woodcock
Esteemed Legend

Never use join; try this:

(index="firstIndex" AND forumId="F1") OR (index="secondIndex")
| rex ".*\]\[(?<OrderId2>\w{3})\]\s*main" 
| eval OrderId = coalesce(OrderId, OrderId2)
| stats values(forumId) AS forumId values(ItemName) AS ItemName BY OrderId

jainkul123
Explorer

With this query I do get OrderId and Itemname in the table but no value for forumId.

0 Karma

jainkul123
Explorer

Sorry my bad, it works. The only minor issue there is the data from different indexes shows up in different rows, please suggest how to fix it. Something like following:

-----------------------------------------------------------
| OrderId      |  forumId     | ItemName   |
-----------------------------------------------------------
| 123             |  200             |                       |
| 123             |                      |    444            |
-----------------------------------------------------------
0 Karma

jainkul123
Explorer

Moreover, this above one I ran the search for specific OrderId (123), but if I don't specify that then I get table with huge records where the forumId is missing. So, it essentially want only those records for which there are same OrderIDs in both indexes. However, this query returns me even those that exists only in one index too.

0 Karma

jainkul123
Explorer

@woodcock | selfjoin OrderId -- This doesn't help. The value for forumId becomes blank.

0 Karma

jainkul123
Explorer

@woodcock . The 2 rows I was getting as the OrderId from different indexes had different case, one is lower and other is Capital. Solved it by using lower() method

| eval OrderId = lower( coalesce( OrderId, OrderId2) )

But the other issue still remains, i.e. the I still get the OrderIds from the second index for which the corresponding events from first index are filtered out. And that is the reason I am getting some entries in the table for which forumId is empty.
I only want the OrderId in the table which are from both indexes after the filtering.

0 Karma

jainkul123
Explorer

@woodcock Never mind got it working by adding this at the end:
| where forumId != ""

Thanks for all your help.

0 Karma

woodcock
Esteemed Legend

Add this to the bottom:

| selfjoin OrderId
0 Karma

jainkul123
Explorer

@woodcock | selfjoin OrderId -- This doesn't help. With this the value for forumId becomes blank.

0 Karma

woodcock
Esteemed Legend

Are you absolutely sure. That really makes no sense. Are you using the same timepicker?

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

This should work fine. Whats not working? What happens if you run each search individually?

0 Karma

jainkul123
Explorer

It shows events count but the table that I get has nothing in it.

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