topic How to combine data from indexes with different fields as common results in Splunk Search

How to combine data from indexes with different fields as common results

sasankganta — Tue, 16 May 2023 09:05:11 GMT

Hi Team,

I have 2 indexes with same data.

In Index1 data is coming with the fields user, action, http_referrer and

In index 2 data is coming with the fields loginid, action, referrer

I want to table the results with common field data from 2 indexes like | table user, action, referrer

I tried using eval mvappend it didn't work. Can some one please assist. Thank You.

Re: How to combine data from indexes with different fields as common results

gcusello — Tue, 16 May 2023 09:13:51 GMT

Hi @sasankganta,

you have to use stats to correlate events from different Data Sources, something like this:

index=indexA OR index=indexB | eval referrer=coalesce(http_referrer,referrer) | stats values(user) AS user values(action) AS action BY referrer

if you want to correlate for referrer and user, you can move user field in the BY clause:

index=indexA OR index=indexB | eval referrer=coalesce(http_referrer,referrer) | stats values(action) AS action BY user referrer

Ciao.

Giuseppe

Re: How to combine data from indexes with different fields as common results

sasankganta — Tue, 16 May 2023 09:54:01 GMT

Hi @gcusello , Early I tried this as well before posting. It didn't work.

If I'm doing table / stats for the above data :

In Index1 fields user, action, http_referrer and

In index 2 fields loginid, action, referrer

final result in statistics I need user, action, referrer these 3 fields

Re: How to combine data from indexes with different fields as common results

gcusello — Tue, 16 May 2023 09:59:25 GMT

Hi @sasankganta,

what do you mean with "it doesn't work"?

it's a result different from the desidered one or there isn't any result?

if you want in each row user, action and referrer, you can use something like this:

index=indexA OR index=indexB | eval referrer=coalesce(http_referrer,referrer) | stats count BY user action referrer | fields - count

if you haven't any result, check the format of http_referrer and referrer: verify if they have the same format.

Ciao.

Giuseppe

Re: How to combine data from indexes with different fields as common results

sasankganta — Tue, 16 May 2023 10:55:58 GMT

Hi @gcusello None of the above query are working :

sorry if i confused you I will give you more details here:

Index1 data: may 16 04:00:00 10.10.01 vendor=* product=* user=xxxx action=allowed http_referrer=https:google.com

Index2 data: may 16 04:00:00 10.10.01 vendor=* product=* LoginID=xxxx action=allowed referrer=https:google.com

In above log data is same but filed names are changed. Now I want to name common filed in my results and I want results in below format in statistics:

action user referrer
allowed sasank https:google.com

allowed sasank https:google.com

Re: How to combine data from indexes with different fields as common results

gcusello — Tue, 16 May 2023 11:20:10 GMT

Hi @sasankganta,

let me understand: loginID has the same values of user?

if yes, you can use eval coalesce to gibe the same field name:

index=indexA OR index=indexB | eval referrer=coalesce(http_referrer,referrer), user=coalesce(user,LoginID) | stats count BY user action referrer | fields - count

if they are different:

index=indexA OR index=indexB | eval referrer=coalesce(http_referrer,referrer) | stats values(user) AS user BY action referrer | mvexpand user

Ciao.

Giuseppe