topic Re: How do I use a common field found in two sourcetypes to output a corresponding value from sourcetype2? in Splunk Search

How do I use a common field found in two sourcetypes to output a corresponding value from sourcetype2?

Chrstover — Tue, 02 Aug 2016 19:37:31 GMT

I have two sources with different data in each except one common column in each sourcetype called "DeviceName". In sourcetype two (device), I have a column called "Zones".

Is there a way of using "DeviceName" to print the "Zones" name from sourcetype two?

My logic: Let's say DeviceName is "user-pc" and is in zone "Personal". Search the second sourcetype by DeviceName (user-pc) and pull out the zone info and place it into the table.

What I have so far is:

index=protect sourcetype=threat OR sourcetype=device 
    | dedup SHA256, "File Path", "File Name" 
    | table SHA256 "DeviceName" "File Status" "Classification" "Ever Run" "Auto Run" "Running" "File Path" "Zones"

Nothing is printed in the Zones column in the table however. Any help would be appreciated!

Re: How do I use a common field found in two sourcetypes to output a corresponding value from sourcetype2?

sundareshr — Tue, 02 Aug 2016 20:26:55 GMT

Try this

index=protect (sourcetype=threat OR sourcetype=device) | stats values(SHA256) as SHA256  values("File Status") as "File Status" values( "Classification") as Classification values("Ever Run") as "Ever Run" values("Auto Run") as "Auto Run" values("Running") as "Running" values("File Path") as "File Path" by Zones  DeviceName

Re: How do I use a common field found in two sourcetypes to output a corresponding value from sourcetype2?

Chrstover — Mon, 08 Aug 2016 19:08:51 GMT

This does not work.

Let me clarify what I am trying to accomplish:

I have two CSV documents and their column headers are as follows:

sourcetype=threat

File Name,File Status,Symantec Score,Signature Status,AV Industry,Global Quarantined,Safelisted,Signed,Cert,Timestamp,Cert Issuer,Cert Publisher,Cert Subject,Product Name,Description,File Version,Company Name,Copyright,SHA256,MD5,Classification,DeviceName,Serial Number,File Size (bytes),File Path,Drive Type,File Owner,Create Time,Modification Time,Access Time,Running,Auto Run,Ever Run,First Found,Last Found,Detected By

sourcetype=device

Device Name,Serial Number,OS Version,Agent Version,Policy,Zones,Mac Addresses,IP Addresses,Last Reported User,Background Detection,Created,Files Analyzed,Is Online,Online Date,Offline Date

Basically what I want to do is merge the two searches together to create a table where anything found in sourcetype=threat will show the Zones information (found only in sourcetype=device) for the respective sourcetype=threat.

Let me know if you follow what I am trying to achieve. I can clarify further if necessary.

Thank you so much for your help thus far!

Re: How do I use a common field found in two sourcetypes to output a corresponding value from sourcetype2?

somesoni2 — Mon, 08 Aug 2016 19:23:33 GMT

Give this a try

index=protect sourcetype=threat OR sourcetype=device 
| table SHA256 "DeviceName" "File Status" "Classification" "Ever Run" "Auto Run" "Running" "File Path" "Zones"
| stats values(*) as * by DeviceName

  index=protect sourcetype=threat OR sourcetype=device 
    | table SHA256 "DeviceName" "File Status" "Classification" "Ever Run" "Auto Run" "Running" "File Path" "Zones"
    | stats list(*) as * by DeviceName | foreach * [eval "<<FIELD>>"=mvindex('<<FIELD>>',-1)]

Re: How do I use a common field found in two sourcetypes to output a corresponding value from sourcetype2?

Chrstover — Mon, 08 Aug 2016 19:47:48 GMT

While your search actually works it does not append the "Zones" information.

So sourcetype=threat reports a threat and the only common column header is "DeviceName" and sourcetype=device is basically a list of all devices. If a threat is detected I want the zones information to be put into the table.

So let's say Devicename=user-pc I need to search sourcetype=device find the zones information and append it to the table. That way I know which department to go to in order to remedy the threat.