Transaction grouping between disparate logs with the same hostname

[daryllj]

hi there- trying to put together a query that will search two different sourcetypes for a hit within 1 minute where the hostname is the constant value between both log files- back story: I am reviewing log files for viruses detected, along with removable media added and trying to come up with a single dashboard panel that will flag any viruses that have been introduced into our environment via removable media, but having some issues using the transaction command. here is what I have tried so far:

sourcetype="sophos:devicecontrol" OR sourcetype="sophos:threats" | transaction ComputerName maxspan=1m

have spent a bit of time working through the transaction examples, but so far, I get no results- thoughts welcomed!

[martin_mueller · Accepted solution]

To rephrase, you're looking for sophos:devicecontrol events where a sophos:threats event followed within a minute for the same ComputerName value?

An approach could look something like this:

sourcetype="sophos:devicecontrol" OR sourcetype="sophos:threats"
| streamstats last(eval(case(sourcetype="sophos:threats", _time))) as threat_time by ComputerName
| eval delta = threat_time - _time
| where sourcetype="sophos:devicecontrol" AND delta<=60

This should annotate all device control events with the last (in reverse time order, so actually next) threat event timestamp for that computer. Then it'll only keep those device control events that have a threat time within a minute from the device control event timestamp.

[daryllj]

this does the job nicely- thanks for suggesting an alternative approach to using the transaction command!

[p_gurav]

Can you give sample logs from both the sourcetypes(mask imp data fields)?

[daryllj]

here you go- and thanks for your support!

InsertedAt=2019-01-04 19:52:05; EventID=18948; EventTime=2019-01-04 19:52:05; ActionTakenID=113; ActionTaken=Cleaned up; UserName=NT AUTHORITY\SYSTEM; ScannerTypeID=200; ScannerType=Unknown; StatusID=50; Status=Resolved; ThreatTypeID=1; ThreatType=Viruses/spyware; ThreatName=EICAR-AV-Test; FullFilePath=E:\eicar.exe; ComputerName=HOSTA; ComputerDomain=DOMAINA; ComputerIPAddress=10.145.11.46

InsertedAt=2019-01-04 19:51:22; EventID=12716; EventTime=2019-01-04 19:51:22; EventTypeID=6; EventType=Device control; Name=; ReportingName=USB Device/USBSTOR\DISK&VEN_&PROD_&REV_0.00\090115703D116E&0; UserName=NT AUTHORITY\LOCAL SERVICE; ActionID=4; Action=Alert only; SubTypeID=; SubType=; DeviceTypeID=1; DeviceType=Removable Storage; Model=USB Device; DeviceID=USBSTOR\DISK&VEN_&PROD_&REV_0.00\090115703D116E&0; ComputerName=HOSTA; ComputerDomain=DOMAINA; ComputerIPAddress=10.145.11.46