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

[jip31]

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??

[dmarling · Accepted solution]

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

[jip31]

many thanks for your help