topic Re: How do I correlate two fields from various sources? in Splunk Search

How do I correlate two fields from various sources?

dfigurello — Thu, 11 Sep 2014 05:03:53 GMT

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!

Re: How do I correlate two fields from various sources?

lguinn2 — Thu, 11 Sep 2014 06:16:18 GMT

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.

Re: How do I correlate two fields from various sources?

dfigurello — Thu, 11 Sep 2014 17:38:50 GMT

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:

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

Thanks!

Re: How do I correlate two fields from various sources?

dfigurello — Thu, 11 Sep 2014 17:46:48 GMT

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

Re: How do I correlate two fields from various sources?

lguinn2 — Thu, 11 Sep 2014 19:49:44 GMT

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

Re: How do I correlate two fields from various sources?

dfigurello — Mon, 28 Sep 2020 17:34:45 GMT

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