SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1518779%22%20slang%3D%22en-US%22%3EKQL%3B%20Failed%20to%20save%20analytic%20rule%3B%20The%20request%20timed%20out%3B%20series-outliers()%20%3B%20NSG%20logs%20via%20Syslog%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518779%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%26nbsp%3BI'm%20with%20an%20MSSP%20and%20we%20are%20writing%20a%20monitoring%20rule%20in%20Sentinel%20for%20monitor%20data%20exfiltration%20using%20series-outliers()%3B%20following%20the%20KQL.%20We've%20parsed%20the%20information%20from%20Syslog%3B%20keep%20only%20the%20internal%20to%20external%20IPs%3B%20then%20sum%20the%20amount%20of%20data%20transferred%20per%20source%20IP%20address%2C%20per%20timebin%20(using%20make-series)%3B%20then%20we%20calculate%20the%20series-outliers().%20Finally%2C%20we've%20tried%20to%20filter%20only%20if%20the%20last%20time-bin%20contains%20a%20value%20outlier_score%20%26gt%3B1.5%20(anomaly).%20I've%20been%20teaching%20the%20usage%20of%20series-outliers()%20using%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FNorthwaveSecurity%2Fexplaining-kql-seriesoutliers%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FNorthwaveSecurity%2Fexplaining-kql-seriesoutliers%3C%2FA%3E%26nbsp%3B.%20After%20everything%20is%20validated%2C%20when%20I%20click%20in%20'create'%20it%20gives%20the%20following%20error%20message%3A%26nbsp%3B%3CSTRONG%3E%22Failed%20to%20save%20analytic%20rule.%20Failed%20to%20save%20analytic%20rule%20'Anomalous%20Data%20Transfer'.%20The%20request%20timed%20out.%20%5B...%5D%22%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20lookback%20%3D%20ago(14d)%3B%20%2F%2Flook%20back%207d%20to%20determine%20inter%20quartile%20range%20(IQR)%20Q90-Q10%20for%20cturkey()%0Alet%20timebin%20%3D%208h%3B%20%2F%2Fnote%3A%20with%20a%20lower%20granularity%20(ex.1h)%20takes%20more%20time%20to%20run%20and%20the%20number%20of%20outliers%20are%20higher.%0A%2F%2F%0ASyslog%0A%7C%20where%20SyslogMessage%20contains%20%22%3CSTRING-FROM-NSG-LOGS%3E%22%0A%7C%20where%20TimeGenerated%20%26gt%3B%20lookback%20%2F%2Fgood%20practice%20to%20filter%20the%20data%20to%20reduce%20resource%20consumption%0A%7C%20project%20TimeGenerated%2C%20columns%20%3D%20split(SyslogMessage%2C%20'%2C')%20%2F%2Fpartialy%20parce%20the%20syslogmessage%0A%7C%20project%20TimeGenerated%2C%20SourceIP%20%3D%20replace('%22'%2C''%2Ctostring(columns%5B8%5D))%2C%20DestinationIP%20%3D%20replace('%22'%2C''%2Ctostring(columns%5B9%5D))%2C%20event%20%3D%20replace('%22'%2C''%2Ctostring(columns%5B5%5D))%2C%20BytesSent%20%3D%20replace('%22'%2C''%2Ctostring(columns%5B28%5D))%2C%20BytesRcvd%20%3D%20replace('%22'%2C''%2Ctostring(columns%5B29%5D))%20%2F%2Fparse%20fields%20of%20the%20syslogmessage%0A%7C%20where%20notempty(SourceIP)%20and%20event%20%3D%3D%20%22Connection%20closed%22%20%2F%2F%20filtering%20only%20entries%20that%20there%20is%20data%20transfering%0A%7C%20extend%20dst_second_octet%20%3D%20split(SourceIP%2C%20%22.%22)%5B1%5D%20%2F%2F%20getting%20the%20second%20octet%20of%20the%20destination%20IP%0A%7C%20where%20DestinationIP%20!startswith%20'192.168'%20and%20DestinationIP%20!startswith%20'10.'%20and%20not%20(DestinationIP%20startswith%20'172'%20and%20toint(dst_second_octet)%20%26gt%3B%2015%20or%20toint(dst_second_octet)%20%26lt%3B%2032)%20%2F%2F%20filtering%20to%20only%20external%20data%0A%7C%20make-series%20TotalBytesSent%3Dsum(toint(BytesSent))%20on%20TimeGenerated%20from%20lookback%20to%20now()%20step%20timebin%20by%20SourceIP%2F%2Fmake%20a%20series%20considering%20the%20full%20time%20window%0A%7C%20extend%20outlier_scores%20%3D%20series_outliers(TotalBytesSent)%20%2F%2F%20evaluation%20the%20outliers%20considering%20the%20TotalBytesSent%20(aggregated%20per%20timebin%20per%20user)%0A%7C%20mv-expand%20TimeGenerated%20to%20typeof(datetime)%2C%20TotalBytesSent%20to%20typeof(double)%2C%20outlier_scores%20to%20typeof(double)%2F%2F%20expanding%20the%20variables%20for%20a%20better%20visualization%20and%20extration%20of%20outliers%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(timebin)%20and%20outlier_scores%20%26gt%3B%201.5%20%2F%2Ffiltering%20ONLY%20if%20the%20outlier%20happened%20in%20the%20last%20timebin%0A%7C%26nbsp%3Bextend%26nbsp%3BIPCustomEntity%26nbsp%3B%3D%26nbsp%3BSourceIP%3C%2FSTRING-FROM-NSG-LOGS%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1518779%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMonitoring%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Emonitoring%20rule%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ensg%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eseries-outiliers%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esyslog%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1520656%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%3B%20Failed%20to%20save%20analytic%20rule%3B%20The%20request%20timed%20out%3B%20series-outliers()%20%3B%20NSG%20logs%20via%20Sysl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520656%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584375%22%20target%3D%22_blank%22%3E%40jjsantanna%3C%2FA%3E%26nbsp%3BIt%20looks%20like%20it%20was%20a%20'hiccup'%20from%20Azure%20or%20Sentinel%20or%20Log%20Analytics.%20I%20was%20not%20able%20to%20debug.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531828%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%3B%20Failed%20to%20save%20analytic%20rule%3B%20The%20request%20timed%20out%3B%20series-outliers()%20%3B%20NSG%20logs%20via%20Sysl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584375%22%20target%3D%22_blank%22%3E%40jjsantanna%3C%2FA%3E%26nbsp%3B%3A%20just%20to%20confirm%20-%20were%20you%20able%20to%20save%20the%20rule%3F%20if%20not%2C%20it%20might%20have%20timeout.%20Can%20you%20post%20a%20few%20sample%20events%20so%20that%20we%20can%20try%20to%20help%20optimize%20the%20query%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1532623%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%3B%20Failed%20to%20save%20analytic%20rule%3B%20The%20request%20timed%20out%3B%20series-outliers()%20%3B%20NSG%20logs%20via%20Sysl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1532623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F293879%22%20target%3D%22_blank%22%3E%40Ofer_Shezaf%3C%2FA%3E%26nbsp%3Bthanks%20for%20getting%20back!%20Yes%2C%20I%20was%20able%20to%20save%20the%20query.%20Then%2C%20I%20'closed%20the%20message.'%20I%20must%20say%20that%20it%20was%20the%20first%20time%20that%20I've%20received%20a%20%22timeout.%22%20I%20really%20don't%20know%20what%20I%20could%20have%20done%20differently.%20The%20main%20point%20is%20that%20to%20use%20%22series_outliers()%22%20we%20need%20to%20create%20a%20timeseries%3B%20and%20to%20filter%20the%20scores%20higher%20than%201%2C5%20(output%20of%20series_outliers())%20we%20need%20to%20convert%20the%20timeseries%20back%20to%20tabular%20with%20mv-expand.%20I've%20tried%20multiple%20ways%20to%20optimize%20the%20query%20but%20I%20was%20unsuccessful.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20make-series%20TotalBytesSent%3Dsum(toint(BytesSent))%20on%20TimeGenerated%20from%20lookback%20to%20now()%20step%20timebin%20by%20SourceIP%2F%2Fmake%20a%20series%20considering%20the%20full%20time%20window%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20extend%20outlier_scores%20%3D%20series_outliers(TotalBytesSent)%20%2F%2F%20evaluation%20the%20outliers%20considering%20the%20TotalBytesSent%20(aggregated%20per%20timebin%20per%20user)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20mv-expand%20TimeGenerated%20to%20typeof(datetime)%2C%20TotalBytesSent%20to%20typeof(double)%2C%20outlier_scores%20to%20typeof(double)%2F%2F%20expanding%20the%20variables%20for%20a%20better%20visualization%20and%20extration%20of%20outliers%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536707%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%3B%20Failed%20to%20save%20analytic%20rule%3B%20The%20request%20timed%20out%3B%20series-outliers()%20%3B%20NSG%20logs%20via%20Sysl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584375%22%20target%3D%22_blank%22%3E%40jjsantanna%3C%2FA%3E%26nbsp%3B%3A%20while%20series%20analysis%20is%20intensive%20in%20nature%2C%20but%20itself%20it%20is%20not%20a%20blocker.%20The%20trick%20is%20usually%20to%20prepare%20the%20data%20in%20the%20most%20efficient%20manner.%20As%20said%2C%20to%20try%20to%20look%20into%20this%2C%20I%20would%20need%20a%20sample%20of%20events%20to%20work%20with.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

www.000webhost.com