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!

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...