This article has not been completed yet. However, it may already contain helpful Information and therefore it has been published at this stage.

Setting up the environment:

Return to the Dataexplorer and the Cluster, you created in the onboarding challange:

https://dataexplorer.azure.com/home

Script to be executed:

.execute database script <|
// Create the table with the traffic information.
// The data loading process estimated to take ~3-4min to complete (114M+ rows of data).
// Notes: VIN - is Vehicle ID 
.create-merge table Traffic (Timestamp:datetime, VIN:string, Ave:int, Street:int)
.ingest async into table Traffic (@'https://kustodetectiveagency.blob.core.windows.net/digitown-traffic/log_00000.csv.gz')
.ingest async into table Traffic (@'https://kustodetectiveagency.blob.core.windows.net/digitown-traffic/log_00001.csv.gz')
.ingest async into table Traffic (@'https://kustodetectiveagency.blob.core.windows.net/digitown-traffic/log_00002.csv.gz')

The Challenge:

.execute database script <|
//Create table for the data
.create-merge table PrimeNumbers(Number:long)
//Import data
.ingest into table PrimeNumbers ('https://kustodetectiveagency.blob.core.windows.net/prime-numbers/prime-numbers.csv.gz')

let check_special_primes = (PrimeNumbers
| sort by Number asc
| extend previous_number = prev(Number)
| extend check_special_prime = toint(Number)+toint(previous_number)+1
| where check_special_prime < 100000000 and check_special_prime > 99000000);
PrimeNumbers
| where Number < 100000000 and Number > 99000000
| join kind=inner (check_special_primes) on $left.Number == $right.check_special_prime
| project special_prime_highest = Number
| order by special_prime_highest desc 
| take 1

aka.ms/99999517

.execute database script <|
// The data below is from https://data.cityofnewyork.us/Environment/2015-Street-Tree-Census-Tree-Data/uvpi-gqnh 
// The size of the tree can be derived using 'tree_dbh' (tree diameter) column.
.create-merge table nyc_trees 
       (tree_id:int, block_id:int, created_at:datetime, tree_dbh:int, stump_diam:int, 
curb_loc:string, status:string, health:string, spc_latin:string, spc_common:string, steward:string,
guards:string, sidewalk:string, user_type:string, problems:string, root_stone:string, root_grate:string,
root_other:string, trunk_wire:string, trnk_light:string, trnk_other:string, brch_light:string, brch_shoe:string,
brch_other:string, address:string, postcode:int, zip_city:string, community_board:int, borocode:int, borough:string,
cncldist:int, st_assem:int, st_senate:int, nta:string, nta_name:string, boro_ct:string, ['state']:string,
latitude:real, longitude:real, x_sp:real, y_sp:real, council_district:int, census_tract:int, ['bin']:int, bbl:long)
with (docstring = "2015 NYC Tree Census")
.ingest async into table nyc_trees ('https://kustodetectiveagency.blob.core.windows.net/el-puente/1.csv.gz')
.ingest async into table nyc_trees ('https://kustodetectiveagency.blob.core.windows.net/el-puente/2.csv.gz')
.ingest async into table nyc_trees ('https://kustodetectiveagency.blob.core.windows.net/el-puente/3.csv.gz')
// Get a virtual tour link with Latitude/Longitude coordinates
.create-or-alter function with (docstring = "Virtual tour starts here", skipvalidation = "true") VirtualTourLink(lat:real, lon:real) { 
	print Link=strcat('https://www.google.com/maps/@', lat, ',', lon, ',4a,75y,32.0h,79.0t/data=!3m7!1e1!3m5!1s-1P!2e0!5s20191101T000000!7i16384!8i8192')
}
// Decrypt message helper function. Usage: print Message=Decrypt(message, key)
.create-or-alter function with 
  (docstring = "Use this function to decrypt messages")
  Decrypt(_message:string, _key:string) { 
    let S = (_key:string) {let r = array_concat(range(48, 57, 1), range(65, 92, 1), range(97, 122, 1)); 
    toscalar(print l=r, key=to_utf8(hash_sha256(_key)) | mv-expand l to typeof(int), key to typeof(int) | order by key asc | summarize make_string(make_list(l)))};
    let cypher1 = S(tolower(_key)); let cypher2 = S(toupper(_key)); coalesce(base64_decode_tostring(translate(cypher1, cypher2, _message)), "Failure: wrong key")
}
nyc_trees
| where spc_common == "American linden"
| extend h3cell = geo_point_to_h3cell(longitude,latitude,10)
| where h3cell == "8a2a100dec9ffff"
| sort by tree_dbh asc
| project tree_id, tree_dbh, latitude,longitude
print Message=Decrypt(@"20INznpGzmkmK2NlZ0JILtO4OoYhOoYUB0OrOoTl5mJ3KgXrB0[8LTSSXUYhzUY8vmkyKUYevUYrDgYNK07yaf7soC3kKgMlOtHkLt[kZEclBtkyOoYwvtJGK2YevUY[v65iLtkeLEOhvtNlBtpizoY[v65yLdOkLEOhvtNlDn5lB07lOtJIDmllzmJ4vf7soCpiLdYIK0[eK27soleqO6keDpYp2CeH5d\F\fN6aQT6aQL[aQcUaQc[aQ57aQ5[aQDG", "ASHES to ASHES")

References:

Kusto Detective Agency: Challenge 4 – Ready to play?
Kusto Detective Agency Case 4
El Puente - Ready to play? Hello. I have been watching you, and I am pretty impressed with your abilities of hacking and cracking little crimes. Want to play big? Here is a prime puzzle for you. Find what it means and prove yourself worthy. 20INznpGzmkmK2NlZ0JILtO4OoYhOoYUB0OrOoTl5mJ3KgXrB0[8LTS…
Yingting HuangUnofficial Azure Club
Kusto Detective Agency — Ready to Play (Part 4 of 5)
While browsing twitter, I came across Kusto Detective Agency — a gamified way of learning Kusto Query Language (KQL). There are a set of five challenges that participants are required to solve using…
Soumyadeep BasuMedium