Skip to content

Instantly share code, notes, and snippets.

View cosh's full-sized avatar

Henning Rauch cosh

44 followers · 43 following
View GitHub Profile
@cosh
cosh / config.json
Last active November 9, 2023 07:33
VNet injection migration for ADX
{
"clusters": [
{
"subscription_id": "sid",
"resource_group_name": "kusto-workload",
"cluster_name": "adx-in-vnet"
},
{
"subscription_id": "sid",
"resource_group_name": "kusto-workload",
@cosh
cosh / GraphPlotly.kql
Last active February 28, 2024 17:07
A stored function that uses the evaluate python operator to execute a Python script that uses the plotly, networkx and pandas libraries to create a plotly viz object from the input tables. The function also adds some styling and annotations to the graph object, such as colors, sizes, hover texts, etc. It uses the replace_string function to inser…
.create-or-alter function with (skipvalidation = "true") VisualizeGraphPlotly(
E:(sourceId:long,targetId:long), N:(nodeId:long),
pLayout:string="spring_layout", pColorscale:string="Picnic", pTitle:string="Happy kraphing!") {
let pythonCodeBlueprint = ```
import numpy as np
import pandas as pd
import plotly.graph_objects as go
import networkx as nx
G = nx.Graph()
@cosh
cosh / knn-geo.kql
Last active May 24, 2023 09:29
let telemetryData = datatable (deviceid: string, ts: datetime, latitude: real, longitude: real) [
"alice",datetime(2022-11-07T23:11:19Z),real(47.6423771),real(-122.1267281),
"alice",datetime(2022-11-07T23:11:20Z),real(47.6424771),real(-122.1277281),
"alice",datetime(2022-11-07T23:11:21Z),real(47.6425771),real(-122.1287281),
"bob",datetime(2022-11-07T23:11:19Z),real(47.6424771),real(-122.1287281),
"bob",datetime(2022-11-07T23:11:20Z),real(47.6424771),real(-122.1287281),
"carol",datetime(2022-11-07T23:11:22Z),real(47.6423771),real(-122.1267281),
"carol",datetime(2022-11-07T23:11:22Z),real(47.6523771),real(-122.1267281),
"carol",datetime(2022-11-07T23:11:24Z),real(47.6623771),real(-122.1267281),
"dave",datetime(2022-11-07T23:11:24Z),real(47.644205),real(-122.1412026),
@cosh
cosh / LightIngestCommand.kusto
Last active June 6, 2023 22:31
How to partition data for ingestion to Kusto
LightIngest.exe "https://ingest-<clusterName>.<region>.kusto.windows.net/;Fed=True" -database:"<databaseName>" -table:"tableName" -source:"https://<storageAccountName>.blob.core.windows.net/<containerName><sasToken>" -format:"parquet" -prefix:"<folderPrefix>" -pattern:"*.parquet" -ingestionMappingRef:"<mappingName" -creationTimePattern:"day='yyyyMMdd'/" -dontWait:true
@cosh
cosh / iotcontext.kql
Created November 12, 2021 15:55
KQL queries to contextualize timeseries data
//The top level request
GetRelevantTimeseriesData('3000000004')
//Creates a function to joins data from SQL, ADT and ADX
.create-or-alter function with (folder = "Analytics/IoT", skipvalidation = "true") GetRelevantTimeseriesData(purchaseOrder:string) {
let productionOrder = GetProductionOrderData(purchaseOrder);
let plantID = toscalar (productionOrder | project tostring(Plant_Code));
let lineID = toscalar (productionOrder | project tostring(Mfg_Line));
let productionDate = toscalar (productionOrder | project todatetime(Date));
let startDate = productionDate - 24h;
@cosh
cosh / map.geojson
Last active October 15, 2022 06:18
datatable(lng:real, lat:real)
[
-73.956683, 40.807907,
-73.916869, 40.818314,
-73.989148, 40.743273,
]
| project h3_hash = geo_point_to_h3cell(lng, lat, 6)
| project h3_hash_polygon = geo_h3cell_to_polygon(h3_hash)
| summarize h3_hash_polygon_lst = make_list(h3_hash_polygon)
| project pack(
@cosh
cosh / map.geojson
Created June 14, 2021 20:53
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@cosh
cosh / geohash.kql
Created June 14, 2021 20:51
// Geohash GeoJSON collection
datatable(lng:real, lat:real)
[
-73.975212, 40.789608,
-73.916869, 40.818314,
-73.989148, 40.743273,
]
| project geohash = geo_point_to_geohash(lng, lat, 5)
| project geohash_polygon = geo_geohash_to_polygon(geohash)
| summarize geohash_polygon_lst = make_list(geohash_polygon)
@cosh
cosh / kustoH3.kql
Last active June 14, 2021 20:08
let k = dynamic({"type":"Polygon","coordinates":[[[-68.02734375,27.137368359795584],[-65.91796875,27.059125784374068],[-65.830078125,33.797408767572485],[-61.34765625,26.980828590472107],[-58.27148437499999,27.059125784374068],[-63.6328125,34.95799531086792],[-58.18359375,42.032974332441405],[-60.46875,42.09822241118974],[-65.91796875,34.95799531086792],[-65.91796875,42.16340342422401],[-68.115234375,42.032974332441405],[-68.02734375,27.137368359795584]]]});
let u = dynamic({"type":"Polygon","coordinates":[[[-53.0859375,27.137368359795584],[-50.2734375,27.137368359795584],[-47.548828125,28.3],[-46.40625,30],[-46.142578125,42.16340342422401],[-48.33984375,42.16340342422401],[-48.7,31],[-50.009765625,29.611670115197377],[-52.64648437499999,29.38217507514529],[-53.701171875,29.611670115197377],[-55.634765625,30.600093873550072],[-55.810546875,42.16340342422401],[-57.83203125,41.96765920367816],[-58.095703125,29.7],[-56.51367187499999,28.2],[-55.37109374999999,27.7],[-53.0859375,27.137368359795584]]]});
let s = d
@cosh
cosh / geospatial.kql
Created June 2, 2020 20:56
Sample queries for geospatial analytics on Azure Data Explorer
#connect cluster('help.kusto.windows.net').database('Samples')
//Show random storm events
StormEvents
| where isnotempty( BeginLat) and isnotempty( BeginLon)
| project BeginLon, BeginLat, EventType
| take 300
| render scatterchart with (kind = map)
//Show nearby storm events