topic Re: How to read data from two different indexes? in Splunk Search

How to read data from two different indexes?

splkjk — Mon, 17 Oct 2022 21:38:38 GMT

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

Re: How to read data from two different indexes?

ITWhisperer — Mon, 17 Oct 2022 22:01:27 GMT

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

Re: How to read data from two different indexes?

splkjk — Mon, 17 Oct 2022 22:36:10 GMT

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

Re: How to read data from two different indexes?

ITWhisperer — Tue, 18 Oct 2022 06:26:18 GMT

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

Re: How to read data from two different indexes?

splkjk — Tue, 18 Oct 2022 13:04:13 GMT

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

itsi_group_id="8a84c088-ba86"

event_id="8a84c088-ba86d"

Re: How to read data from two different indexes?

paksoi — Tue, 18 Oct 2022 13:35:16 GMT

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.

Re: How to read data from two different indexes?

ITWhisperer — Tue, 18 Oct 2022 20:40:39 GMT

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