Consolidate data from 2 indexes

[vikram_m]

We have 2 index

1. Having user name and his machine details and everything about his login

2. User name and his actual machine behaviour like link speed, could usage, memusage etc.

I am trying to frame a query where both indexes will be called and getting an output something like username, user machine, user machine make, os details, CPU, mem, ......

I tried 2 ways from last 2 days

1. (Index 1 sourcetype) or (index 2 source type) | eval which I want | rename cols | table columns

Outcome : details from index 1 appear but not from index2

2. Index 1 sourcetype | join [search index 2 sourcetype] | eval which I want | rename cols | table columns

Outcome : details of few columns are not visible rest appear

Please help which command and how do I frame query to get my panel completed.

Thanks.

[vikram_m]

I tried the stats command but I need an output matching username in index1 with login name in index2 followed with table command from both the indexes.

[isoutamo]

As @bowesmana said, you should use that |eval ... to define a new common field name for userid which you are using later on. Just forgot those original login name and username.
r. Ismo

[vikram_m]

Thanks @isoutamo  I read through the doc you shared accorss....in future if I need it I'll use the solution in the document....Thanks again

[vikram_m]

I tried the above stats query but I am trying to fame something like username from index1 is login_name in index2 and then rest of the data from both index should be used like a table command

[bowesmana]

@vikram_m 

The way to use a field from index 1 and a different fields from index 2 is like this

| eval userId=if(sourcetype="sourcetype1",username,login_name)

Then use the stats command as mentioned with userId. Stats gives you a table like output anyway, but you just need to use table to order them fields as you need.

[bowesmana · Accepted solution]

@vikram_m 

Avoid using join as it can have unexpected outcomes, depending on search time, subsearch size and so on.

The way to aggregate two data sets into a single one is to use 'stats' and aggregate by the common field, so you would do something like this

(index 1 sourcetype) or (index 2 sourcetype)
| eval which I want 
| stats values(*) as * by userId

which would then give you a single 'event' with the fields from both data sets for a common userId

Hope this helps

[vikram_m]

Thanks bow.....Let me try it today and confirm the outcome

[isoutamo]

Hi
here is Nick's (yearly) presentation on conf20. https://conf.splunk.com/files/2020/slides/TRU1761C.pdf
Excellent explanation why you should use join.
r. Ismo