Reporting

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

arrowecssupport
Communicator

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

Serial=12345
Dave Smith
0123456789
Running Tor,uTorrent
0 Karma

lvetter
Explorer

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!

Laura.

somesoni2
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

arrowecssupport
Communicator

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!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...