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
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 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
@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.
@Vijeta Never mind got it working by adding this at the end:
| where forumId != ""
Thanks for all your help. +1
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
With this query I do get OrderId and Itemname in the table but no value for forumId.
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 |
-----------------------------------------------------------
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.
@woodcock | selfjoin OrderId -- This doesn't help. The value for forumId becomes blank.
@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.
@woodcock Never mind got it working by adding this at the end:
| where forumId != ""
Thanks for all your help.
Add this to the bottom:
| selfjoin OrderId
@woodcock | selfjoin OrderId -- This doesn't help. With this the value for forumId becomes blank.
Are you absolutely sure. That really makes no sense. Are you using the same timepicker?
This should work fine. Whats not working? What happens if you run each search individually?
It shows events count but the table that I get has nothing in it.