KQL Workbooks
Date: 22. March 2022
Hosts
- Gianni
- Alex
Guest
Matthew Lowe
Show Content
- Show Presentation
- 
Agenda
- Hello again
- What's new in KQL
- IOCs
- Learn KQL
- KQL Tools
- Todays guest speaker: Matthew Lowe
- What did you do with KQL this month?
- KQL Challenge of the month
What's new in KQL
Creating large watchlists
You can now create large watchlists with up to 500 MB file size by storing the files in an Azure Blob Storage account.
Examples
```// when the CNSArmyList is stored in an Azure blog storage Account
// The CINS Army List
// https://cinsscore.com/#list
let cinsarmylist = (externaldata(ip:string)
[@""]
with (format="txt",IgnoreFirstRecord=true));
DeviceNetworkEvents
| where TimeGenerated > ago(30d)
| where RemoteIP in (cinsarmylist)
| project TimeGenerated, DeviceName, RemoteIP, RemotePort, LocalIP, LocalPort, ActionType
// when the CNSArmylist is imported as a watchlist
let CNSArmyList = _GetWatchlist('CNSArmylist') | project IP;
DeviceNetworkEvents
| where TimeGenerated > ago(30d)
| where RemoteIP in (CNSArmyList)
| project TimeGenerated, DeviceName, RemoteIP, RemotePort, LocalIP, LocalPort, ActionType
- [Large Watchlist using SAS key is in Public Preview!](https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/large-watchlist-using-sas-key-is-in-public-preview/ba-p/3242370)
### DeviceTvmSoftwareInventory
the ***ProductCodeCpe*** column within the [DeviceTvmSoftwareInventory](https://docs.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-devicetvmsoftwareinventory-table?view=o365-worldwide) table can now be filtered to also identify software for which there is not a CPE.
## IOCs
This month [Gianni](https://twitter.com/castello_johnny) talked about IOCs related to the Conti Leaks
***Vulnerability management***
https://pastebin.com/raw/pv1mDGYC
kudos to @c3rb3ru5d3d53c
***Advanced hunting***
https://ddanchev.blogspot.com/2022/02/exposing-conti-ransomware-gang-osint_28.html
kudos to @dancho_danchev
let ContiIP = externaldata(IP:string)
[
@"https://raw.githubusercontent.com/KustoKing/ExternalData/main/Conti-IP"
];
DeviceNetworkEvents
| where RemoteIP in~(ContiIP) or LocalIP in~(ContiIP)
## Learn KQL
[Gianni](https://twitter.com/castello_johnny) talked about the use of [Externaldata](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/externaldata-operator?pivots=azuredataexplorer) operator in KQL queries.
## KQL Tools
### Uncoder CTI
Use [Uncoder CTI](https://cti.uncoder.io/) to generate IOC queries on the fly for Microsoft Sentinel and Microsoft Defender for Endpoint.
## Guest Speaker Matthew Lowe
This month our guest speaker was [Matthew Lowe](https://www.linkedin.com/in/matthew-lowe-13b61990/).
During his presentation Matthew walked us through the following Microsoft Sentinel Workbooks:
- Intro to KQL
- Advanced KQL for Microsoft Sentinel
For more information watch the KQL Cafe session video or read the following blogs on Microsoft Tech Community.
- [Get Hands-On KQL](https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/get-hands-on-kql-practice-with-this-microsoft-sentinel-workbook/ba-p/3055600)
- [Feedback Form](https://aka.ms/introtokqlsurvey)
- [Advanced KQL Framework](https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/advanced-kql-framework-workbook-empowering-you-to-become-kql/ba-p/3033766)
- [Feedback Form](https://forms.office.com/r/qNS7cRmPWS)
## What did you do with KQL this month?
### Patch Management
This month - [Alex](https://twitter.com/alexverboon) worked on a query to identify the number of missing patches a device that's onboarded into Microsoft Defender for Endpoint.
Below is the query that was used to identify the number of missing patches for Windows Servers. You can easily adjust the querie to look for missing patches on Windows 10 devices.
### Check last AzureAD sign-in date from users that are member of a sepcific AzureAD Group.
This month - [Alex](https://twitter.com/alexverboon) worked on a query to identify the users last sign-in date into AzureAD based on a specific AzureAD Group.
Scenario: You are deploying MFA and created an exclude group, after a while you want to cleanup that group, i.e. start removing users. To identify users that haven't logged on to AzureAD for a while, use the following query.
KQL Challenge of the month
This month we invite you to join the #365DaysofKQL Scavenger Hunt 1 created by Matt Zorich
#365DaysofKQL Scavenger Hunt 1
Entries will close midnight April 8th (UTC time)