The Addicted to KQL series is an ongoing, advanced series for KQL. For beginning topics don’t start here. Instead, see the original Must Learn KQL series.
The series TOC along with the currently completed chapters, sample queries, series images, and even the series eBook will always be located at the following shortlink: https://aka.ms/Addicted2KQL
=======================
I have a bunch of KQL queries in my Microsoft Sentinel repo on GitHub (https://github.com/rod-trent/SentinelKQL) that pulls in information about table costs. Even though it may not be evident in some of them, the Usage table is where most of the data comes from.
However, some of the data (like the _IsBillable column) doesn’t come from the Usage table, but instead is pulled from the Standard Columns. I regularly get asked where these columns come from, so I thought I’d share since its good information to know. But also as I start building out the Advanced series for Must Learn KQL (https://aka.ms/Addicted2KQL), these will be used periodically, and I’ll point back to this reference instead of recapping constantly.
Azure Monitor Logs provides several columns of data that is available to access in any query or any table.
The current list is as follows:
- TenantId – contains workspace ID for the Log Analytics workspace
- TimeGenerated – contains the date and time that the record was created
- _TimeReceived – contains the date and time that the row of data was received through ingestion
- Type – stores the name of the table
- _ItemId – the row’s (record) unique identifier
- _ResourceId – contains the unique identifier for the resource that the record is associated with
- _SubscriptionId – contains the subscription ID of the resource that the record is associated with
- _IsBillable – true or false – contains data to show if the data is billable or not
- _BilledSize – if _IsBillable is true, contains the size in bytes that will be billed
Want to test these for yourself? Use the KQL Playground (https://aka.ms/LADemo) used through the Must Learn KQL series and just replace the Project operator option in the following query with each standard column from the list.
SecurityEvent //table name
| project _BilledSize //showing standard column: _BilledSize
Read more: Standard columns in Azure Monitor Logs
=========================
[Want to discuss this further? Hit me up on Twitter or LinkedIn]
[Subscribe to the RSS feed for this blog]
[Subscribe to the Weekly Microsoft Sentinel Newsletter]
[Subscribe to the Weekly Microsoft Defender Newsletter]
[Learn KQL with the Must Learn KQL series and book]