How do I correlate two fields from various sources?

[dfigurello]

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!

[lguinn2 · Accepted solution]

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]

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

[dfigurello]

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]

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

[dfigurello]

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!