Trender.kql

Trender.kql

// https://aka.ms/kusto.trender 

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

.show database Trender principals 

.add database Trender viewers ('aadapp=aa7e9f10-1035-4684-b10a-097bb4948f95') 'kustotrender1appreg'

.add database Trender viewers ('aadapp=b5f3b2a2-0ec3-4c9d-adfd-447e528f24ac') 'Kusto Trender Sample App'


// https://github.com/Azure/azure-kusto-trender/blob/master/kusto/trender-schema.kql

.drop tables (Timeseries, TimeseriesHierarchy, TimeseriesMetadata) ifexists ;

.drop functions (GetAggregates, GetChildrenByPath, GetHierarchies, GetMetaData, GetPathToTimeseriesId, GetTimeseriesIdByPath, GetTotalAvailability, Search, Search_ExactPath, Suggest) ifexists;

.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 


.show version 

| project IsFree = toint(ServiceOffering has 'Personal') 


// https://github.com/Azure/azure-kusto-trender/blob/master/kusto/trender-sample-data.kql

.execute database script <|
//
// ingest data
//
.create table ['Timeseries'] ingestion csv mapping 'Timeseries_mapping' '[{"column":"TimeseriesId", "Properties":{"Ordinal":"0"}},{"column":"Timestamp", "Properties":{"Ordinal":"1"}},{"column":"Value", "Properties":{"Ordinal":"2"}}]'
//
.ingest async into table Timeseries (
    h'https://trendersampledata.blob.core.windows.net/data/Timeseries_1.csv.gz',
    h'https://trendersampledata.blob.core.windows.net/data/Timeseries_2.csv.gz',
    h'https://trendersampledata.blob.core.windows.net/data/Timeseries_3.csv.gz'
) with (format='csv',ingestionMappingReference='Timeseries_mapping',ingestionMappingType='csv')
//
.create table ['TimeseriesMetadata'] ingestion csv mapping 'TimeseriesMetadata_mapping' '[{"column":"TimeseriesId", "Properties":{"Ordinal":"0"}},{"column":"TimeseriesName", "Properties":{"Ordinal":"1"}},{"column":"Description", "Properties":{"Ordinal":"2"}},{"column":"EngUnits", "Properties":{"Ordinal":"3"}},{"column":"DataStreamTags", "Properties":{"Ordinal":"4"}}]'
//
.ingest async into table TimeseriesMetadata (
    h'https://trendersampledata.blob.core.windows.net/data/TimeseriesMetadata_1_2cb9453c338f44a6b9263f254f89cec3.csv.gz'
) with (format='csv',ingestionMappingReference='TimeseriesMetadata_mapping',ingestionMappingType='csv')
//
.create table ['TimeseriesHierarchy'] ingestion csv mapping 'TimeseriesHierarchy_mapping' '[{"column":"TimeseriesId", "Properties":{"Ordinal":"0"}},{"column":"DisplayName", "Properties":{"Ordinal":"1"}},{"column":"Path", "Properties":{"Ordinal":"2"}}]'
//
.ingest async into table TimeseriesHierarchy (
    h'https://trendersampledata.blob.core.windows.net/data/TimeseriesHierarchy_1_814905e48a164222926254296d280149.csv.gz'
) with (format='csv',ingestionMappingReference='TimeseriesHierarchy_mapping',ingestionMappingType='csv')


.show commands  
// | where StartedOn >ago(5m) 
| project Text, State, Duration 
| where Text contains_cs "Timeseries"

Timeseries
| take 3

TimeseriesMetadata
| take 3

TimeseriesHierarchy
| take 3



Timeseries
| where TimeseriesId in ("283eebcb-d267-4a76-8179-a9b33fb87cf2", "0607cdea-9dd3-4c39-b076-96d020442bca")
| count

cluster('help').database('Trender').Timeseries
| where TimeseriesId =="df4412c4-dba2-4a52-87af-780e78ff156b"

Timeseries
| where TimeseriesId =="283eebcb-d267-4a76-8179-a9b33fb87cf2"
| count