How to create a report where two values match from two different sources?


I am pulling data from two different sources. Each source contains data on a computer's serial number. I want to be able to generate a report when a serial number exists in both sources.

So I'm monitoring 2 folders for the following files which get updated every hour:

  1. ContactData.csv << This shows the contact data of who owns this server
    Serial=12345, Contact_Name=Dave Smirth, Phone_num=0123456789

  2. Software_violation << This shows a list of bad software running.
    SerialNum=12345; Software=Tor,uTorrent

In each source, the serials are in two different fields
1. "Serial Number supported"
2. System_Serial_Number

So I want a combined report output of

Dave Smith
Running Tor,uTorrent
0 Karma


You would use a join here:

|inputlookup ContactData.csv | join Serial [ |inputlookup Software_violation | rename SerialNum as Serial] | table Serial, Contact_Name, Phone_num, Software

I assumed your Software_violoation was a lookup. If its not a lookup, replace "|inputlookup Software_violation" with "search sourcetype=Software_violation", or other relevant search.

Good Luck!


Revered Legend

Try something like this (check the field names, especially in coalesce command)

(index=A source=source1) OR (index=B source=source2) | eval Serial=coalesce('Serial Number supported', System_Serial_Number) | stats values(Contact_Name) as Contact_Name values(Phone_num) as Phone_num values(Software) as Bad_Softwares by Serial
0 Karma


Thank you for the "stats values" part as that has given a bit part which i was missing; how to show only some data.

But the "eval Serial=coalesce" isn't quite doing what i need. I want to only select values where the serial number exists in both sources.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...