hfleitas

// https://detective.kusto.io/sans2023

////////////////////
// CASE 1 - Alert //
////////////////////
// the alerts says the user clicked the malicious link
// 'http://madelvesnorthpole.org/published/search/MonthlyInvoiceForReindeerFood.docx'

// Email of receipient?
Email

hfleitas

ingestionLogs
| where Timestamp between (datetime(2014-03-08T00:00:00) .. datetime(2014-03-08T10:00:00))
| summarize count() by Level
| render piechart 

//my RLS query
let IsManager = false; //let IsManager=current_principal_is_member_of("aadgroup=managers@company.com");
let OnlyErrors = ingestionLogs | where Level == "Error" and not(IsManager);
let allData = ingestionLogs | where IsManager;
union OnlyErrors,allData

hfleitas

.create table logsRaw(
    Timestamp:datetime, 
    Source:string, 
    Node:string, 
    Level:string, 
    Component:string, 
    ClientRequestId:string, 
    Message:string, 
    Properties:dynamic
) 

hfleitas

.show tables details 

.show database extents partitioning statistics 

.show table BIAzureAdditionalRawCounterFiveMinuteMsitScus_partitioned policy partitioning

{
  "PartitionKeys": [
    {
      "ColumnName": "CounterName",

hfleitas

.create table telemetryraw (TimeseriesId:string, TimeseriesName:string, ServerTimeStamp:long)

// TimeseriesId == '1d4037bc-xxxx-xxxx-xxxx-xxxxxxxxx'

.create table telemetry (TimeseriesId:guid, TimeseriesName:string, ServerTimeStamp:datetime)

.create-or-alter function transformtelemetryraw() { 
  telemetryraw
  | project TimeseriesId=toguid(TimeseriesId), TimeseriesName, ServerTimeStamp=unixtime_seconds_todatetime(ServerTimeStamp)
}

hfleitas

datatable(id:datetime, Event:string, counter:string, MoreData:dynamic) [
    datetime(1910-06-11), "Born", '', dynamic({"key1":"value1", "key2":"value2"}),
    datetime(1930-01-01), '',"Enters Ecole Navale", dynamic({"key1":"value3", "key2":"value4"}),
    datetime(1953-01-01), "Published first book",'' ,dynamic({"key1":"value5", "key2":"value6"}),
    datetime(1997-06-25), '', "Died", dynamic({"key1":"value7", "key2":"value8"})
]
| extend Event1 = iff((isnotempty(counter) == true), counter, Event)

hfleitas

.show tables details 

.show database extents partitioning statistics 
//note, it takes the DM 45mins - 1hr to start the partitioning process + however long to generate the heterougenious extents. 
//so partitioning can take a while to finish creating the extents.

//alert if the partitioning percentage drops:
.show diagnostics | project TableWithMinPartitioningPercentage, MinPartitioningPercentageInSingleTable

//get more partitioning info on the extent level: 

hfleitas

// https://aka.ms/adxinaday

.create table logsRaw(
    Timestamp:datetime, 
    Source:string, 
    Node:string, 
    Level:string, 
    Component:string, 
    ClientRequestId:string, 
    Message:string, 

hfleitas

iphone_raw
| take 10

iphone_raw
| summarize count() by component, messageSource

iphone_raw
| where isempty(component)

iphone_raw 

hfleitas

let _startTime = datetime(2016-01-01T17:38:00Z);
let _endTime = datetime(2023-09-19T18:38:00Z);
// baseQuery results
let results = () {
demo_make_series1
| where TimeStamp between (['_startTime'] .. ['_endTime'])
};
results
| make-series Cardinality=count() on TimeStamp step 1h by OsVer
| extend (anomalies, score, baseline) = series_decompose_anomalies(Cardinality, 2.0)