Cyber Campfire Necessities: Part 1

July 19, 2023 | 1 min read

Ian Hanley

Security Architect

Ian Hanley Square Calcite Duotone

It's warm enough to get out the camping gear for summer. This series is going to cover how to investigate both things around the campfire and also the tables in your Microsoft Sentinel.

This blog is about making the perfect s'mores and the art of marshmallow toasting. Some folks like them golden, others like them crispy. The important part is knowing how you want your s'more to taste. Like outcomes from queries, nothing is worse than getting a piece of burnt stick when you're expecting a gooey marshmallow.

So, whether you're just toasting your first query or a seasoned Sentinel Ninja, here are some basic queries I keep coming back to when investigating anything odd about my ingest patterns (and thus my overall cost).

My choice? Keep it crispy, and no burnt sticks, please.

KQL S’mores

So how do you know something is “odd” with your ingestion volume? I look for sudden changes when building my s’more. Let’s look at the ingest pattern over the past quarter and graph billable volume via the usage table with the following query:

=====================================================================

Usage

| where TimeGenerated > ago(90d)

| where IsBillable == true

| summarize TotalVolumeGB = sum(Quantity) / 1000 by bin(StartTime, 1d), Solution

| render columnchart

=====================================================================

How does one construct such a masterful s’more from scratch? Let’s break it apart line by line, like a s’more sandwich:

=====================================================================

Usage //<--tells us which table to apply this query to. In this case it’s the Usage log table.

| where TimeGenerated > ago(90d) //<-- how far back the query will look in the Usage table.

| where IsBillable == true //<-- filters out non-billable data (we’re only worried about

//data that incurs a cost).

| summarize TotalVolumeGB = sum(Quantity) / 1000 by bin(StartTime, 1d), Solution

// Convert to GB and return results by day, per ingest solution (LogManagement, Security, etc.)

| render columnchart //<-- graph results to a column chart

=====================================================================

BlueVoyant Summer Camp for Microsoft Sentinel