forked from Azure/azure-kusto-trender
-
Notifications
You must be signed in to change notification settings - Fork 0
/
trender-schema.kql
111 lines (111 loc) · 4.96 KB
/
trender-schema.kql
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
.execute database script <|
//
// create tables
//
.create-merge table Timeseries (TimeseriesId:string, Timestamp:datetime, Value:real) with (docstring = "The raw timeseries data")
//
.create-merge table TimeseriesMetadata (TimeseriesId:string, TimeseriesName:string, Description:string, EngUnits:string, DataStreamTags:string) with (docstring = "The timeseries metadata")
//
.create-merge table TimeseriesHierarchy (TimeseriesId:string, DisplayName:string, Path:dynamic) with (docstring = "The timeseries hierarchy")
//
.alter-merge table Timeseries policy retention softdelete = 3650d recoverability = enabled
//
.alter-merge table TimeseriesMetadata policy retention softdelete = 3650d recoverability = enabled
//
.alter-merge table TimeseriesHierarchy policy retention softdelete = 3650d recoverability = enabled
//
//
// creating functions
//
.create-or-alter function with (docstring = "Get Timeseries By Path", skipvalidation = "true") GetTimeseriesIdByPath(InputPath:dynamic) {
let plant = tostring(InputPath[0]);
let factory = tostring(InputPath[1]);
let production= tostring(InputPath[2]);
let station = tostring(InputPath[3]);
TimeseriesHierarchy
| where tostring(Path[0]) == plant and tostring(Path[1]) == factory and tostring(Path[2]) == production and tostring(Path[3]) == station
| project TimeseriesId, DisplayName
}
//
//
.create-or-alter function with (docstring = "Get Children By Path", skipvalidation = "true") GetChildrenByPath(InputPath:dynamic) {
let InputPathLength = array_length(InputPath);
TimeseriesHierarchy
| extend SlicedArray = iff(InputPathLength > 0, array_slice(Path, 0, InputPathLength-1), dynamic([]))
| where array_length( SlicedArray) < array_length(Path)
| where strcat_array(SlicedArray,",") == strcat_array(InputPath,",")
| summarize Count = count() by Child = tostring(Path[InputPathLength])
}
//
//
.create-or-alter function with (docstring = "Search timeseries IDs in a specific hierarchy", skipvalidation = "true") Search(InputPath:dynamic, searchString:string) {
let InputPathLength = array_length(InputPath);
TimeseriesHierarchy
| extend SlicedArray = iff(InputPathLength > 0, array_slice(Path, 0, InputPathLength-1), dynamic([]))
| where array_length( SlicedArray) <= array_length(Path)
| where strcat_array(SlicedArray,",") == strcat_array(InputPath,",")
| where TimeseriesId contains searchString or DisplayName contains searchString
| project TimeseriesId, DisplayName, Path
}
//
.create-or-alter function with (docstring = "Suggest timeseries IDs", skipvalidation = "true") Suggest(searchString:string) {
TimeseriesHierarchy
| extend Match = case(
TimeseriesId contains searchString, TimeseriesId,
DisplayName contains searchString, DisplayName,
""
)
| where isnotempty( Match)
| project Match, TimeseriesId, DisplayName, Path
}
//
//
.create-or-alter function with (docstring = "Get path by timeseries id", skipvalidation = "true") GetPathToTimeseriesId(InputPath:dynamic, SearchString:string) {
let InputPathLength = array_length(InputPath);
TimeseriesHierarchy
| where TimeseriesId contains SearchString or DisplayName contains SearchString
| extend SlicedArray = iff(InputPathLength > 0, array_slice(Path, 0, InputPathLength-1), dynamic([]))
| where array_length( SlicedArray) < array_length(Path)
| where strcat_array(SlicedArray,",") == strcat_array(InputPath,",")
| project TimeseriesId, DisplayName, Path
}
//
//
.create-or-alter function with (docstring = "Search for timeseries IDs based on an exact path", skipvalidation = "true") Search_ExactPath(InputPath:dynamic, searchString:string) {
TimeseriesHierarchy
| where TimeseriesId contains searchString or DisplayName contains searchString
| where tostring(InputPath) == tostring(Path)
| project TimeseriesId, DisplayName, Path
}
//
.create-or-alter function with (docstring = "Get Total Availability", skipvalidation = "true") GetTotalAvailability() {
Timeseries
| where isnotempty( Timestamp)
| summarize Availability = count() by bin(Timestamp,1d)
}
//
//
.create-or-alter function with (docstring = "Get timeseries aggregates", skipvalidation = "true") GetAggregates(timeseriesIds:dynamic, startDate:datetime, endDate:datetime, timebucket:timespan) {
Timeseries
| where TimeseriesId in (timeseriesIds) and Timestamp between (startDate..endDate)
| summarize Average = avg(Value), Count = count(), Min = min(Value), Max = max(Value) by TimeseriesId, bin(Timestamp, timebucket)
}
//
//
.create-or-alter function with (docstring = "Get Meta Data", skipvalidation = "true") GetMetaData(TimeSeriesIds:dynamic) {
TimeseriesMetadata
| where TimeseriesId in (TimeSeriesIds)
| lookup TimeseriesHierarchy on TimeseriesId
| project-away DisplayName
}
//
//
.create-or-alter function with (docstring = "Get all hierarchies", skipvalidation = "true") GetHierarchies() {
TimeseriesHierarchy
| extend HierarchyName = tostring(Path[0])
| distinct HierarchyName
}
//
// CachingPolicyAlter, if Deployment Mode is not free adx clusters.
//
.alter tables (Timeseries, TimeseriesHierarchy, TimeseriesMetadata) policy caching hot = 3650d