KQL Samples

Some may be analytics rules, some may be just something I was looking for.

Lots of stuff learned from https://learnsentinel.blog so make sure you check it out! Awesome dude who is also very active on twitter https://twitter.com/reprise_99 and Github https://github.com/reprise99

Check for processname within accountdomain

// Check for process RDCMan in "FunkyDomain"
DeviceProcessEvents
| where AccountDomain == "FunkyDomain"
| where FileName has "RDCMan"

---

Failed logins in Azure

Timechart

// Failed logins to Azure, timechart
SigninLogs
| where TimeGenerated > ago(14d)
| where ResultType in ("50126", "50053" , "50055", "50056")
// 50053 Account is locked because the user tried to sign in too many times with an incorrect user ID or password. 
// 50055 Invalid password, entered expired password. 
// 50056 Invalid or null password - Password does not exist in store for this user. 
// 50126 Invalid username or password, or invalid on-premises username or password.
| summarize Count=count() by bin(TimeGenerated, 1h)
| render timechart 

Table

// Failed logins to Azure, Table
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType in ("50126", "50053" , "50055", "50056")
// 50053 Account is locked because the user tried to sign in too many times with an incorrect user ID or password. 
// 50055 Invalid password, entered expired password. 
// 50056 Invalid or null password - Password does not exist in store for this user. 
// 50126 Invalid username or password, or invalid on-premises username or password.
| project TimeGenerated, UserPrincipalName, ResultType , AppDisplayName, Location, IPAddress, AuthenticationRequirement, ConditionalAccessStatus, ConditionalAccessPolicies, IsInteractive, UserAgent

---

Finding changed permissions in a sharepoint site

// Finding changes in permissions on sharepoint sites
OfficeActivity
| where TimeGenerated > ago(30d) 
// Looks for workload SharePoint
| where OfficeWorkload has "SharePoint"
// Find objects where the "url" contains test
| where OfficeObjectId has "test" 
| where RecordType == "SharePointSharingOperation"
| where Operation == "AddedToGroup"
| project TimeGenerated, RecordType, Operation, ['AddedBy']=UserId, Site_Url, ['TargetUser']=TargetUserOrGroupName, ['Group']=Event_Data

---

Sentinel Table Growth

Takes all tables into account

// Table Growth
// https://techcommunity.microsoft.com/t5/azure-observability/log-analytics-table-growth/m-p/1024814 
// Add respective tables from wildcard search into tt
// withsource= 
union withsource = tt *
| where TimeGenerated >= ago(31d) 
// Source = tt sets column Source to the table name
| summarize count() by bin(TimeGenerated,1d), Source=tt
| render timechart title = "Monthly growth"

Takes only SecurityEvent and Signinlogs into account

// https://techcommunity.microsoft.com/t5/azure-observability/log-analytics-table-growth/m-p/1024814 
// Add respective tables from wildcard search into tt
// withsource= 
union withsource = tt SecurityEvent, SigninLogs
| where TimeGenerated >= ago(31d) 
// Source = tt sets column Source to the table name
| summarize count() by bin(TimeGenerated,1d), Source=tt
| render timechart title = "Monthly growth"

---

Find computer/user who connected to an IP

DeviceNetworkEvents
| where RemoteIP == "8.8.8.8"
| sort by TimeGenerated

---

Extracting applocker events

SecurityEvent
| where Channel has "Applocker"
| where EventID == "8004"
| extend xmlData=parse_xml(EventData)
| extend xmlDataDetails=xmlData.UserData.RuleAndFileData
| extend xmlPolicyName=xmlDataDetails.PolicyName, xmlRuleName=xmlDataDetails.RuleName
| project-away xmlData,xmlDataDetails, EventData
| extend User=extract_all(@"([A-Z]*\.[A-Z]*)",FilePath)[0], Executeable=extract_all(@"([A-Z]*\.[A-Z]*)",FilePath)[1]

---

Finding sysmonlogs

// Finding sysmon logs
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventSourceName == "Microsoft-Windows-Sysmon"

---

Finding SMBv1 audit data (if you have enabled auditing!)

//SMBv1 data 
SecurityEvent 
| where TimeGenerated > ago(1h) 
| where Channel == "Microsoft-Windows-SMBServer/Audit" 
| extend xmlData = parse_xml(EventData)
| extend xmlClientName=xmlData.EventData.Data.["#text"]
| project-away xmlData

---

MDE billable data

This should (I think) all Microsoft Defender for Endpoint/Server related tables - may be wrong...

// MDE billable device data
union withsource = tt Device*
| where _IsBillable == true
| where TimeGenerated > ago(30d)
| extend computerName = tolower(tostring(split(DeviceName, '.')[0]))
| summarize GBytes=sum(_BilledSize) /1024/1024/1024 by computerName | sort by GBytes nulls last 

---

Find all wifi SSID names people have connected to

For fun...

// list all network names
DeviceNetworkInfo
| where TimeGenerated > ago(30d)
// Not neccesary but if you have devicegroups for clients and want to narrow down your search..
//| where MachineGroup has "My Client Devices"
| where NetworkAdapterStatus == "Up"
| where isnotempty(IPv4Dhcp)
| extend NetworkName = tostring(parse_json(ConnectedNetworks)[0].Name)
| extend IsConnectedToInternet = tostring(parse_json(ConnectedNetworks)[0].IsConnectedToInternet)
| mv-expand DnsAddresses
| distinct NetworkName