Splunk Search

Consolidate data from 2 indexes

vikram_m
Path Finder

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.

Labels (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

@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

 

 

View solution in original post

vikram_m
Path Finder

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.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

vikram_m
Path Finder

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

@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
Path Finder

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

0 Karma

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