hfleitas

// https://aka.ms/adx.partner.hol 

.drop table RawMetrics ifexists 

//Get total number of records in 'RawMetrics' table
RawMetrics
| count 


//Sample 10 records from 'RawMetrics' table

hfleitas

//Advanced KQL queries for analytics over 'TransformedMetrics' table


//Create function with transformation logic
.create-or-alter function  Transform_RawMetrics {
RawMetrics
| mv-apply kv=fields on
(
    mv-expand kind=array kv
    | extend SQLMetrics = tostring(kv[0]), Value=todouble(kv[1])

hfleitas

#connect cluster('trd-8122hx8pm0kqwn7cmp.z1.kusto.data.microsoft.com').database('ThermostatIoTDB')

//***********************************************************************************************************
// Here are two articles to help you get started with KQL:
// KQL reference guide - https://aka.ms/KQLguide
// SQL - KQL conversions - https://aka.ms/sqlcheatsheet
//***********************************************************************************************************

.create-or-alter function  ExtractThermostatData {
StageIoTRawData

hfleitas

#connect cluster('kvc43f0ee6600e24ef2b0e.southcentralus').database('KustoDetectiveAgency')

// Catchy Run

// Krypto who runs in Barcelona 3-4/week, 8-12km
// we're given a decrypted msg

// Everywhere in Barcelona, you'll find buildings by famous architects like Antoni Gaudi and Joseph Cadafalch
// The real gem of the city is La Sagrada Familia

hfleitas

#connect cluster('kvc43f0ee6600e24ef2b0e.southcentralus').database('KustoDetectiveAgency')

// End Game

.execute database script <|
.create table KuandaLogs (Timestamp:datetime, DetectiveId:string, Message:string)
.ingest async into table KuandaLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c10adminlogs/log_00000.csv.gz')
.ingest async into table KuandaLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c10adminlogs/log_00001.csv.gz')
.ingest into table KuandaLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c10adminlogs/log_00002.csv.gz')

hfleitas

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

// Email of receipient?
Email
| where link == 'http://madelvesnorthpole.org/published/search/MonthlyInvoiceForReindeerFood.docx'
// [email protected]

hfleitas

#connect cluster("demo12.westus").database("Datasets")


//                    [\
//               .----' `-----.
//              //^^^^;;^^^^^^`\
//      _______//_____||_____()_\________
//     /212    :      : ___              `\
//    |>   ____;      ;  |/\><|   ____   _<)
//   {____/    \_________________/    \____}

hfleitas

cluster('ade.loganalytics.io/subscriptions/e4e06275-58d1-4081-8f1b-be12462eb701/resourcegroups/adxiotanalytics10774/providers/microsoft.operationalinsights/workspaces/sentinel10774').database('sentinel10774').Heartbeat
| take 10

Heartbeat
| take 10


#connect cluster('adxpm10774.eastus').database('sentinel')

sr_heartbeat

hfleitas

sr_heartbeat
| take 10

sr_heartbeat
| count

.show table sr_heartbeat policy ingestionbatching 


#connect cluster('adxpm10774.eastus').database(IoTAnalytics)

hfleitas

#connect cluster('help').database('Samples')

//timechart panels
let min_t = datetime(2017-01-05); 
let max_t = datetime(2017-02-03 22:00); 
let dt = 2h; 
demo_make_series2 
| make-series num=avg(num) on TimeStamp from min_t to max_t step dt by sid 
| where sid == 'TS1'   //  select a single time series just to get cleaner visualization 
| extend (anomaly, deviation, seasonal) = series_decompose_anomalies(num)