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

[sasankganta]

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. 

[sasankganta]

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              

[gcusello]

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

[sasankganta]

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 

[gcusello]

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

[gcusello]

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