Splunk Search

How do I correlate two fields from various sources?

dfigurello
Communicator

Hi Splunkers,

I am having problem to correlate two sources in my splunk.
How to add information in the table with a field located in various source.

For example:

file1.csv

  employer,location
    james,TEXAS
    John,CALIFORNIA
    Peter,OREGON
    Karon,MONTANA

file2.csv

name, central
james, MONTANA
james, MONTANA
james, TEXAS
Peter,OREGON
Peter,OREGON
Peter,OREGON

I would create in splunk a table with 03 fields like this:

employer | Employer Location  | central
james | TEXAS | MONTANA
james | TEXAS | MONTANA
james | TEXAS | TEXAS

Cheers!

Tags (3)
0 Karma
1 Solution

lguinn2
Legend

There are a couple of ways to do this in Splunk. However, if you have data that is not event-based and is just used for lookups, you should put it in a lookup table rather than indexing it in Splunk.

Use field lookups tutorial describes how to set up a lookup table. In your case, the file1.csv should probably be the lookup table.

View solution in original post

lguinn2
Legend

There are a couple of ways to do this in Splunk. However, if you have data that is not event-based and is just used for lookups, you should put it in a lookup table rather than indexing it in Splunk.

Use field lookups tutorial describes how to set up a lookup table. In your case, the file1.csv should probably be the lookup table.

lguinn2
Legend

If you are using data from 2 databases, why not use Splunk DBConnect to retrieve the data instead of CSV files? Here is how to set up a lookup in Splunk DBConnect that accesses a database:

http://docs.splunk.com/Documentation/DBX/1.1.4/DeployDBX/Setupadatabaselookuptable

0 Karma

dfigurello
Communicator

I got it:
source="C:\Users\dfigurello\Desktop\xxx\ligacoes_tronco.csv" name=* central=* | rename nome as employer | join employer [ search index=brq source="C:\Users\dfigurello\Desktop\xxx\rm_local_sigla.csv" ] | stats count by employer ,central,central| sort - count | where count > 15 | where central!=central
cheers

dfigurello
Communicator

My challenge: I need to know what is the employer office and what is the central phone he is using?

0 Karma

dfigurello
Communicator

Hi lguinn,

I created two files to replicate a scenario in my splunk (files1.csv and files2.csv), however I am collecting data from 2 databases in real scenario.

I have this structure in first source:
employer | cod_location
james | 01A
John | 02A

Here I applied a lookup to convert the codes to city.
My search returns:

employer | cod_location | location(lookup)
james | 01A | TEXAS
John | 02A | CALIFORNIA

Now, I need create a "lookup" with internal data that correlate with another source>
e.g:

employer | location | Central
james | TEXAS | MONTANA
james | TEXAS | MONTANA

Thanks!

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