Sydney, this is a great post thank you, I wonder if you have ever tried to correlate 2 searches together to show overlapping data? For instance we had some DNS traffic trigger an alert on our firewall and tracked it down to some users logged in remotely (not from our firewall logs). I was able to see visually the 2 events but that was only after manually eliminating the events that didn't overlap:
This was the gist of my search:
index="firewall" {interesting.DNS.Query}
| timechart span=5m count AS Suspect_DNS
| appendcols [
search index="remote_access" NOT ({Long list of users I filtered out})
| rename total_user_minutes AS "Minutes User Seen",overlapping_minutes AS "Minutes User & Suspicious DNS Seen", overlap_on_percent AS "User Connected Score",overlap_sus_percent AS "User Suspicious DNS Score",total_suspicious_minutes AS "Minutes Suspicious DNS Seen" |table User ]
Sydney, this is a great post thank you, I wonder if you have ever tried to correlate 2 searches together to show overlapping data? For instance we had some DNS traffic trigger an alert on our firewall and tracked it down to some users logged in remotely (not from our firewall logs). I was able to see visually the 2 events but that was only after manually eliminating the events that didn't overlap:
This was the gist of my search:
index="firewall" {interesting.DNS.Query}
| timechart span=5m count AS Suspect_DNS
| appendcols [
search index="remote_access" NOT ({Long list of users I filtered out})
| timechart span=5m count by user
]
https://substack.com/profile/472408239-brian-e-kirk/note/c-221945706?utm_source=substack&utm_content=first-note-modal
I have found a way to achieve this! It quickly filtered out everything that didn't have at least 70% overlap with the suspicious DNS traffic.
https://substackcdn.com/image/fetch/$s_!qniP!,w_1136,c_limit,f_webp,q_auto:good,fl_progressive:steep/https%3A%2F%2Fsubstack-post-media.s3.amazonaws.com%2Fpublic%2Fimages%2F5b604de2-50ac-4c05-864c-483841ac58f8_1885x840.png
This is the gist of the search:
index="firewall" kylinlot.com
| timechart span=15m count AS Suspect_DNS
| appendcols [
search index="remote_access" | search [ search (index="firewall" kylinlot.com) OR (index="remote_access")
| bin _time span=1m
| eval sus_flag=if(index="firewall",1,0)
| stats
max(sus_flag) as sus_flag
values(User) as User
by _time
| eventstats sum(sus_flag) as total_suspicious_minutes
| mvexpand User
| where isnotnull(User)
| eval overlap=if(sus_flag=1,1,0)
| stats
count as total_user_minutes
sum(overlap) as overlapping_minutes
values(total_suspicious_minutes) as total_suspicious_minutes
by User
| eval overlap_percent=round((overlapping_minutes/total_suspicious_minutes)*100,2)
| where overlap_percent >= 70
| sort - overlap_on_percent, overlap_sus_percent
| rename total_user_minutes AS "Minutes User Seen",overlapping_minutes AS "Minutes User & Suspicious DNS Seen", overlap_on_percent AS "User Connected Score",overlap_sus_percent AS "User Suspicious DNS Score",total_suspicious_minutes AS "Minutes Suspicious DNS Seen" |table User ]
| where isnotnull(User) |eval User=lower(User)
| timechart span=15m count by User
]