cosh’s gists

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 / 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 / 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 / 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 / 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 / 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 / map.geojson

Created June 14, 2021 20:53

Sorry, something went wrong. Reload?

Sorry, we cannot display this file.

Sorry, this file is invalid so it cannot be displayed.

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 / 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 / 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

Henning Rauch cosh