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!
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)]
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.
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
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!