Splunk Search

how to match a specific field in 2 different csv file and 2 different index

jip31
Motivator

Hi

I use the search below in order to display in a table a specific EventCode by host
I am matching the host with the host list there is in "host.csv"

index="x" sourcetype="y" EventCode=6008 
    [| inputlookup host.csv] 
| table _time host EventCode

Now I need to add 2 fields (ROOM & SITE) in the table
The fields come from another index & sourcetype and they are also located in a csv file
In the csv file, there is also a field "host" but called "HOSTNAME"
So what I want is to match from the host list there is in "host.csv" all the event which have an EventCode=6008 and to retrieve for the same host the fields "ROOM" & "SITE" there is in sourcetype="view_splunk_assets"

index=master-data-lookups sourcetype="view_splunk_assets"

Could you help me please??

Tags (1)
0 Karma
1 Solution

dmarling
Builder

Depending on the speed to retrieve the data from the other sourcetype this can work as a streaming option by using a join to that data:

index="x" sourcetype="y" EventCode=6008 
    [| inputlookup host.csv] 
| table _time host EventCode 
| join type=left host 
    [search index=master-data-lookups sourcetype="view_splunk_assets"
    | stats count by HOSTNAME ROOM SITE
    | fields - count
    | rename HOSTNAME as host]

The stats count by HOSTNAME ROOM SITE will elminate duplicates if you have any in that sourcetype. If that sourcetype is static data, it may make more sense to make that a seperate lookup file and use a lookup command like below:

index="x" sourcetype="y" EventCode=6008 
    [| inputlookup host.csv] 
| table _time host EventCode 
| lookup view_splunk_assets.csv HOSTNAME as host output ROOM, SITE
If this comment/answer was helpful, please up vote it. Thank you.

View solution in original post

0 Karma

dmarling
Builder

Depending on the speed to retrieve the data from the other sourcetype this can work as a streaming option by using a join to that data:

index="x" sourcetype="y" EventCode=6008 
    [| inputlookup host.csv] 
| table _time host EventCode 
| join type=left host 
    [search index=master-data-lookups sourcetype="view_splunk_assets"
    | stats count by HOSTNAME ROOM SITE
    | fields - count
    | rename HOSTNAME as host]

The stats count by HOSTNAME ROOM SITE will elminate duplicates if you have any in that sourcetype. If that sourcetype is static data, it may make more sense to make that a seperate lookup file and use a lookup command like below:

index="x" sourcetype="y" EventCode=6008 
    [| inputlookup host.csv] 
| table _time host EventCode 
| lookup view_splunk_assets.csv HOSTNAME as host output ROOM, SITE
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

jip31
Motivator

many thanks for your help

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...