SOLVED

KQL; Failed to save analytic rule; The request timed out; series-outliers() ; NSG logs via Syslog

Contributor

Hi Guys, I'm with an MSSP and we are writing a monitoring rule in Sentinel for monitor data exfiltration using series-outliers(); following the KQL. We've parsed the information from Syslog; keep only the internal to external IPs; then sum the amount of data transferred per source IP address, per timebin (using make-series); then we calculate the series-outliers(). Finally, we've tried to filter only if the last time-bin contains a value outlier_score >1.5 (anomaly). I've been teaching the usage of series-outliers() using https://github.com/NorthwaveSecurity/explaining-kql-seriesoutliers . After everything is validated, when I click in 'create' it gives the following error message: "Failed to save analytic rule. Failed to save analytic rule 'Anomalous Data Transfer'. The request timed out. [...]" 

 

Can anyone help me? 

 

let lookback = ago(14d); //look back 7d to determine inter quartile range (IQR) Q90-Q10 for cturkey()
let timebin = 8h; //note: with a lower granularity (ex.1h) takes more time to run and the number of outliers are higher.
//
Syslog
| where SyslogMessage contains "<STRING-FROM-NSG-LOGS>"
| where TimeGenerated > lookback //good practice to filter the data to reduce resource consumption
| project TimeGenerated, columns = split(SyslogMessage, ',') //partialy parce the syslogmessage
| project TimeGenerated, SourceIP = replace('"','',tostring(columns[8])), DestinationIP = replace('"','',tostring(columns[9])), event = replace('"','',tostring(columns[5])), BytesSent = replace('"','',tostring(columns[28])), BytesRcvd = replace('"','',tostring(columns[29])) //parse fields of the syslogmessage
| where notempty(SourceIP) and event == "Connection closed" // filtering only entries that there is data transfering
| extend dst_second_octet = split(SourceIP, ".")[1] // getting the second octet of the destination IP
| where DestinationIP !startswith '192.168' and DestinationIP !startswith '10.' and not (DestinationIP startswith '172' and toint(dst_second_octet) > 15 or toint(dst_second_octet) < 32) // filtering to only external data
| make-series TotalBytesSent=sum(toint(BytesSent)) on TimeGenerated from lookback to now() step timebin by SourceIP//make a series considering the full time window
| extend outlier_scores = series_outliers(TotalBytesSent) // evaluation the outliers considering the TotalBytesSent (aggregated per timebin per user)
| mv-expand TimeGenerated to typeof(datetime), TotalBytesSent to typeof(double), outlier_scores to typeof(double)// expanding the variables for a better visualization and extration of outliers
| where TimeGenerated > ago(timebin) and outlier_scores > 1.5 //filtering ONLY if the outlier happened in the last timebin
| extend IPCustomEntity = SourceIP

  

4 Replies
best response confirmed by jjsantanna (Contributor)
Solution

@jjsantanna It looks like it was a 'hiccup' from Azure or Sentinel or Log Analytics. I was not able to debug.

@jjsantanna : just to confirm - were you able to save the rule? if not, it might have timeout. Can you post a few sample events so that we can try to help optimize the query?

@Ofer_Shezaf thanks for getting back! Yes, I was able to save the query. Then, I 'closed the message.' I must say that it was the first time that I've received a "timeout." I really don't know what I could have done differently. The main point is that to use "series_outliers()" we need to create a timeseries; and to filter the scores higher than 1,5 (output of series_outliers()) we need to convert the timeseries back to tabular with mv-expand. I've tried multiple ways to optimize the query but I was unsuccessful. 

 

| make-series TotalBytesSent=sum(toint(BytesSent)) on TimeGenerated from lookback to now() step timebin by SourceIP//make a series considering the full time window

 

| extend outlier_scores = series_outliers(TotalBytesSent) // evaluation the outliers considering the TotalBytesSent (aggregated per timebin per user)

 

| mv-expand TimeGenerated to typeof(datetime), TotalBytesSent to typeof(double), outlier_scores to typeof(double)// expanding the variables for a better visualization and extration of outliers

 

@jjsantanna : while series analysis is intensive in nature, but itself it is not a blocker. The trick is usually to prepare the data in the most efficient manner. As said, to try to look into this, I would need a sample of events to work with.