Forum Discussion
Solved
Incidents from Potential malicious events and Data source anomalies
We are envisioning managing Sentinel mainly from Incidents, rather than manually watching the Sentinel console in the Azure portal. We would like incidents auto-created for serious Potential maliciou...
Q1 - How about (updated, to make it more Alert friendly)?
Go to Log Analytics and Run Query
union isfuzzy=true (W3CIISLog | extend TrafficDirection = "InboundOrUnknown", Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude), (DnsEvents | extend TrafficDirection = "InboundOrUnknown", Country= RemoteIPCountry, Latitude = RemoteIPLatitude, Longitude = RemoteIPLongitude), (WireData | extend TrafficDirection = iff(Direction != "Outbound","InboundOrUnknown", "Outbound"), Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude), (WindowsFirewall | extend TrafficDirection = iff(CommunicationDirection != "SEND","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude), (CommonSecurityLog | extend TrafficDirection = iff(CommunicationDirection != "Outbound","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude, Confidence=ThreatDescription, Description=ThreatDescription), (VMConnection | where Type == "VMConnection" | extend TrafficDirection = iff(Direction != "outbound","InboundOrUnknown", "Outbound"), Country=RemoteCountry, Latitude=RemoteLatitude, Longitude=RemoteLongitude) | where isnotempty(MaliciousIP) and isnotempty(Country) and isnotempty(Latitude) and isnotempty(Longitude) | summarize AggregatedValue = count() by Country //| where AggregatedValue > 100 | sort by AggregatedValue descYou can have this as a Table or Chart? Or filter out Countries with more than 100 events for example (see the commented out example)
Q2:
You can adapt the query e.g.
DnsEvents | summarize Count=count() by Type, bin_at(TimeGenerated, 1h , startofday(ago(7d)) ) | order by TimeGenerated ascor see across all Tables with
let daystoSearch = 7d; // Please enter how many days worth of data to look at? union withsource = tt * | where TimeGenerated > startofday(ago(daystoSearch)) and TimeGenerated < startofday(now()) | summarize Count=count() by Type, bin_at(TimeGenerated, 1h , startofday(ago(7d)) ) // ignore Perf table or other noisy tables | where Type !in ("Perf","NetworkMonitoring") // ignore event counts under 10k | where Count > 10000 | order by TimeGenerated ascGo to Log Analytics and Run Query
CliveWatson Thank you Clive for the tip, and we had considered that method, however, the resulting queries don't achieve the goals.
- In the case of Potential Malicious Events, the query is specific to the geographic spot the event occurred in. To be useful as a rule, the geography should be neutral. If you remove the line from the query "| where Latitude == __.__ and Longitude == ___.__" then you just get a big list of every event where "isnotempty(MaliciousIP)". That does not meet the desire where any large orange or any red hit that would appear on the map creates an incident where that geographic site is the only site in the alert. We were hoping for insight into the logic that determines there is a high number of hits from a specific geo and alert on that. (the same logic that is creating the map)
- For Data Source Anomalies, the query is specific to a time period for that specific data type. If you remove the custom time from the query, for example "datetime(2019-10-25T22:27:47.031Z)" the query doesn't run. We were hoping for insight into the logic that determines which data type experiences the highest anomalies and alert on that. (the same logic that is deciding which two data type to display on the overview page).
Thanks,
John Joyner
Microsoft MVP CDM
Q1 - How about (updated, to make it more Alert friendly)?
Go to Log Analytics and Run Query
union isfuzzy=true
(W3CIISLog
| extend TrafficDirection = "InboundOrUnknown", Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude),
(DnsEvents
| extend TrafficDirection = "InboundOrUnknown", Country= RemoteIPCountry, Latitude = RemoteIPLatitude, Longitude = RemoteIPLongitude),
(WireData
| extend TrafficDirection = iff(Direction != "Outbound","InboundOrUnknown", "Outbound"), Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude),
(WindowsFirewall
| extend TrafficDirection = iff(CommunicationDirection != "SEND","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude),
(CommonSecurityLog
| extend TrafficDirection = iff(CommunicationDirection != "Outbound","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude, Confidence=ThreatDescription, Description=ThreatDescription),
(VMConnection
| where Type == "VMConnection"
| extend TrafficDirection = iff(Direction != "outbound","InboundOrUnknown", "Outbound"), Country=RemoteCountry, Latitude=RemoteLatitude, Longitude=RemoteLongitude)
| where isnotempty(MaliciousIP) and isnotempty(Country) and isnotempty(Latitude) and isnotempty(Longitude)
| summarize AggregatedValue = count() by Country
//| where AggregatedValue > 100
| sort by AggregatedValue desc
You can have this as a Table or Chart? Or filter out Countries with more than 100 events for example (see the commented out example)
Q2:
You can adapt the query e.g.
DnsEvents
| summarize Count=count() by Type, bin_at(TimeGenerated, 1h , startofday(ago(7d)) )
| order by TimeGenerated asc
or see across all Tables with
let daystoSearch = 7d; // Please enter how many days worth of data to look at?
union withsource = tt *
| where TimeGenerated > startofday(ago(daystoSearch)) and TimeGenerated < startofday(now())
| summarize Count=count() by Type, bin_at(TimeGenerated, 1h , startofday(ago(7d)) )
// ignore Perf table or other noisy tables
| where Type !in ("Perf","NetworkMonitoring")
// ignore event counts under 10k
| where Count > 10000
| order by TimeGenerated asc
Go to Log Analytics and Run Query
CliveWatson you are amazing sir! We were able to craft alert rules for both geographic and data source anomalies based on your reply. This means so much to us, thank you.
John Joyner
Microsoft MVP Cloud & Datacenter Management