Splunk Search

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

Chrstover
New Member

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!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

OR

  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)]
0 Karma

Chrstover
New Member

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.

0 Karma

sundareshr
Legend

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
0 Karma

Chrstover
New Member

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!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...