PosLogStreaming/kusto_queries.kql at master · jugi92/PosLogStreaming · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
// Management Queries:
.drop table PosLog;
.create table PosLog (xml:string);
.alter table PosLog policy streamingingestion enable;

// Total Sale
PosLog
| extend json=parse_xml(xml)
| mv-expand line_items = json.POSLog.Transaction.RetailTransaction.LineItem
| extend sales_price_str = line_items.Sale.RegularSalesUnitPrice
| extend sales_price = todecimal(sales_price_str)
| summarize total_sale = sum(sales_price)
| take 20

// Sales by product
PosLog
| extend json=parse_xml(xml)
| mv-expand line_items = json.POSLog.Transaction.RetailTransaction.LineItem
| extend sales_price_str = line_items.Sale.RegularSalesUnitPrice, product = tostring(line_items.Sale.Description["#text"])
| extend sales_price = todecimal(sales_price_str)
| summarize total_sale = sum(sales_price) by product
| take 20;

// Over Time
PosLog
| extend json=parse_xml(xml)
| mv-expand line_items = json.POSLog.Transaction.RetailTransaction.LineItem
| extend sales_price_str = line_items.Sale.RegularSalesUnitPrice, date_time = todatetime(line_items.DateTime["#text"])
| extend sales_price = todecimal(sales_price_str)
| where isnotnull(date_time)
//| project date_time, sales_price
| summarize total_sale = sum(sales_price) by bin(date_time, 5m)
//| sort by date_time asc


// Total Sale with time filter
PosLog
| extend json=parse_xml(xml)
| mv-expand line_items = json.POSLog.Transaction.RetailTransaction.LineItem
| extend sales_price_str = line_items.Sale.RegularSalesUnitPrice, date_time = todatetime(line_items.DateTime["#text"])
| extend sales_price = todecimal(sales_price_str)
| summarize total_sale = sum(sales_price)
| take 20

// Total Transaction count
PosLog
| extend json=parse_xml(xml)
| extend date_time = todatetime(json.POSLog.Transaction.EndDateTime[0]["#text"])
| where date_time between(_startTime .. _endTime)
| summarize total_transactions = count()