Splunk Search

How to read data from two different indexes?

splkjk
Explorer

Hello Team, I'm new to splunk, trying to get some insight/help for the below issue

I'm trying to read data from 2 different indexes and create a consolidated table. The scenarios here is the field values are same but the field names are different.

 index="itsi_grouped_alerts" source="ABC" sourcetype=itsi_notable:group | where itsi_group_id="8a84c088-ba86-4d0a"

index="itsi_notable_audit" source="Notable Event Audit" sourcetype=itsi_notable:audit event_id="8a84c088-ba86-4d0a"

When i try to use a join command, doesn't gives any error.

Appreciate your assistance

Labels (5)
0 Karma
1 Solution

paksoi
Engager

You should be able to do it with a join-command. Can you show us what you've tried?

One way to go about it would be to rename either of the fields so that both fields have the same name:

index="itsi_grouped_alerts" source="ABC" sourcetype=itsi_notable:group 
| where itsi_group_id="8a84c088-ba86-4d0a"
| stats values(*) as * by itsi_group_id
| join itsi_group_id 
    [ search index="itsi_notable_audit" source="Notable Event Audit" sourcetype=itsi_notable:audit event_id="8a84c088-ba86-4d0a"
    | stats values(*) as * by event_id
    | rename event_id as itsi_group_id ]

 

Alternatively, you can keep the original names and use the where clause in the join-command:

index="itsi_grouped_alerts" source="ABC" sourcetype=itsi_notable:group 
| where itsi_group_id="8a84c088-ba86-4d0a"
| stats values(*) as * by itsi_group_id
| join left=L right=R where L.itsi_group_id = R.event_id
    [ search index="itsi_notable_audit" source="Notable Event Audit" sourcetype=itsi_notable:audit event_id="8a84c088-ba86-4d0a"
    | stats values(*) as * by event_id ]

 

See the documentation for more information on the join-command.

View solution in original post

paksoi
Engager

You should be able to do it with a join-command. Can you show us what you've tried?

One way to go about it would be to rename either of the fields so that both fields have the same name:

index="itsi_grouped_alerts" source="ABC" sourcetype=itsi_notable:group 
| where itsi_group_id="8a84c088-ba86-4d0a"
| stats values(*) as * by itsi_group_id
| join itsi_group_id 
    [ search index="itsi_notable_audit" source="Notable Event Audit" sourcetype=itsi_notable:audit event_id="8a84c088-ba86-4d0a"
    | stats values(*) as * by event_id
    | rename event_id as itsi_group_id ]

 

Alternatively, you can keep the original names and use the where clause in the join-command:

index="itsi_grouped_alerts" source="ABC" sourcetype=itsi_notable:group 
| where itsi_group_id="8a84c088-ba86-4d0a"
| stats values(*) as * by itsi_group_id
| join left=L right=R where L.itsi_group_id = R.event_id
    [ search index="itsi_notable_audit" source="Notable Event Audit" sourcetype=itsi_notable:audit event_id="8a84c088-ba86-4d0a"
    | stats values(*) as * by event_id ]

 

See the documentation for more information on the join-command.

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

(index="itsi_grouped_alerts" source="ABC" sourcetype=itsi_notable:group) OR (index="itsi_notable_audit" source="Notable Event Audit" sourcetype=itsi_notable:audit)
``` Create a field which has event_id or itsi_group_id ```
| eval _id=coalesce(event_id, itsi_group_id)
``` Gather the values from the two indexes by _id ```
| stats values(*) as * by _id

splkjk
Explorer

Thanks @ITWhisperer  for the inputs, which provided me some breakthrough, but the output is publishing just the content ( field names and values) from second index, we can see the fields names from first index but no values. So my requirement was, to read the event values from both indexes and if the field values  for both events are equal, then push the output of both index in a table format..

Appreciate your inputs...
Once again thanks so much for your inputs...

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It sounds like your ids don't quite match across the two indexes - are there trailing/leading spaces? do they exactly match including case e.g. "A" is not equal to "a"? does the _id field contain the expected values from events in both indexes? Please check your data

0 Karma

splkjk
Explorer

Values totally match, A=A.., there is no trailing/leading zero or space

itsi_group_id="8a84c088-ba86"
event_id="8a84c088-ba86d"
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

These don't match - one has a "d" at the end

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