2633
Comment:
|
5529
|
Deletions are marked like this. | Additions are marked like this. |
Line 4: | Line 4: |
* Regex extract var(err) and bin for chart {{{ | extend err = extract("(WSREP( [[:alpha:]]+)+)", 1, Message) | summarize AggregatedValue = count() by bin(TimeGenerated, 1s), err }}} |
|
Line 119: | Line 124: |
SecurityEvent | summarize count() by bin(TimeGenerated, 1h) | render timechart }}} |
|
Line 120: | Line 129: |
{{{ SecurityEvent | union (SecurityAlert | summarize count()) | project count_ }}} SecurityEvent | union (SecurityAlert | summarize count()) | project count_ {{{ let SQlData = Event | where Source has "MSSQL" ; let Sqlactivity = SQlData | where RenderedDescription !has "LGIS" and RenderedDescription !has "LGIF" | parse RenderedDescription with * "action_id:" Action:string " " * | parse RenderedDescription with * "client_ip:" ClientIP:string " permission" * | parse RenderedDescription with * "session_server_principal_name:" CurrentUser:string " " * | parse RenderedDescription with * "database_name:" DatabaseName:string "schema_name:" Temp:string "object_name:" ObjectName:string "statement:" Statement:string "." * ; let FailedLogon = SQlData | where EventLevelName has "error" | where RenderedDescription startswith "Login" | parse kind=regex RenderedDescription with "Login" LogonResult:string "for user '" CurrentUser:string "'. Reason:" Reason:string "provided" * | parse kind=regex RenderedDescription with * "CLIENT" * ":" ClientIP:string "]" * ; let dbfailedLogon = SQlData | where RenderedDescription has " Failed to open the explicitly specified database" | parse kind=regex RenderedDescription with "Login" LogonResult:string "for user '" CurrentUser:string "'. Reason:" Reason:string " '" DatabaseName:string "'" * | parse kind=regex RenderedDescription with * "CLIENT" * ":" ClientIP:string "]" * ; let successLogon = SQlData | where RenderedDescription has "LGIS" | parse RenderedDescription with * "action_id:" Action:string " " LogonResult:string ":" Temp2:string "session_server_principal_name:" CurrentUser:string " " * | parse RenderedDescription with * "client_ip:" ClientIP:string " " * ; (union isfuzzy=true Sqlactivity, FailedLogon, dbfailedLogon, successLogon ) | project TimeGenerated, Computer, EventID, Action, ClientIP, LogonResult, CurrentUser, Reason, DatabaseName, ObjectName, Statement |
Azure/KqlKustoLogs
* Examples of Microsoft Azure KQL/Kusto log queries
Regex extract var(err) and bin for chart
| extend err = extract("(WSREP( [[:alpha:]]+)+)", 1, Message) | summarize AggregatedValue = count() by bin(TimeGenerated, 1s), err
let timeOffset = 7d; let discardEventId = 4688; SecurityEvent | where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset) | where EventID != discardEventId
let suspiciousAccounts = datatable(account: string) [ @"\administrator", @"NT AUTHORITY\SYSTEM" ]; SecurityEvent | where Account in (suspiciousAccounts)
let LowActivityAccounts = SecurityEvent | summarize cnt = count() by Account | where cnt < 10; LowActivityAccounts | where Account contains "Mal"
search "err"
search in (SecurityEvent,SecurityAlert,A*) "err"
SecurityEvent | where TimeGenerated > ago(1h) | where EventID == 4624 | where AccountType =~ "user"
SecurityAlert | where TimeGenerated > ago(7d) | extend severityOrder = case ( AlertSeverity == "High", 3, AlertSeverity == "Medium", 2, AlertSeverity == "Low", 1, AlertSeverity == "Informational", 0, -1)
let timeframe = 1d; let DomainList = dynamic(["tor2web.org", "tor2web.com"]); Syslog | where TimeGenerated >= ago(timeframe) | where ProcessName contains "squid" | extend HTTP_Status_Code = extract("(TCP_(([A-Z]+)…-9]{3}))",8,SyslogMessage), Domain = extract("(([A-Z]+ [a-z]{4…Z]+ )([^ :\\/]*))",3,SyslogMessage) | where HTTP_Status_Code == "200" | where Domain contains "." | where Domain has_any (DomainList)
SecurityAlert | where TimeGenerated > ago(7d) | extend severityOrder = case ( AlertSeverity == "High", 3, AlertSeverity == "Medium", 2, AlertSeverity == "Low", 1, AlertSeverity == "Informational", 0, -1) | order by severityOrder desc
SecurityEvent | where EventID == "4688" | summarize cnt=count() by Process, Computer
- count uniq ip's
SecurityEvent | summarize dcount(IpAddress)
- failed logins to disabled accounts, summarisze by User
let timeframe = 1d; let threshold = 3; SigninLogs | where TimeGenerated >= ago(timeframe) | where ResultType == "50057" | where ResultDescription =~ "User account is disabled. The account has been disabled by an administrator." | summarize applicationCount = dcount(AppDisplayName) by UserPrincipalName, IPAddress | where applicationCount >= threshold
SecurityEvent | where EventID == "4624" | summarize make_list(Account) by Computer
SecurityEvent | summarize count() by Account | render barchart
SecurityEvent | summarize count() by bin(TimeGenerated, 1h) | render timechart
SecurityEvent | union (SecurityAlert | summarize count()) | project count_
SecurityEvent | union (SecurityAlert | summarize count()) | project count_
let SQlData = Event | where Source has "MSSQL" ; let Sqlactivity = SQlData | where RenderedDescription !has "LGIS" and RenderedDescription !has "LGIF" | parse RenderedDescription with * "action_id:" Action:string " " * | parse RenderedDescription with * "client_ip:" ClientIP:string " permission" * | parse RenderedDescription with * "session_server_principal_name:" CurrentUser:string " " * | parse RenderedDescription with * "database_name:" DatabaseName:string "schema_name:" Temp:string "object_name:" ObjectName:string "statement:" Statement:string "." * ; let FailedLogon = SQlData | where EventLevelName has "error" | where RenderedDescription startswith "Login" | parse kind=regex RenderedDescription with "Login" LogonResult:string "for user '" CurrentUser:string "'. Reason:" Reason:string "provided" * | parse kind=regex RenderedDescription with * "CLIENT" * ":" ClientIP:string "]" * ; let dbfailedLogon = SQlData | where RenderedDescription has " Failed to open the explicitly specified database" | parse kind=regex RenderedDescription with "Login" LogonResult:string "for user '" CurrentUser:string "'. Reason:" Reason:string " '" DatabaseName:string "'" * | parse kind=regex RenderedDescription with * "CLIENT" * ":" ClientIP:string "]" * ; let successLogon = SQlData | where RenderedDescription has "LGIS" | parse RenderedDescription with * "action_id:" Action:string " " LogonResult:string ":" Temp2:string "session_server_principal_name:" CurrentUser:string " " * | parse RenderedDescription with * "client_ip:" ClientIP:string " " * ; (union isfuzzy=true Sqlactivity, FailedLogon, dbfailedLogon, successLogon ) | project TimeGenerated, Computer, EventID, Action, ClientIP, LogonResult, CurrentUser, Reason, DatabaseName, ObjectName, Statement