Calculating the largest special prime under 100M can be done in a variety of ways, the trick is working within the limited capacity of our free ADX cluster. KQL commands that are useful are serialize, prev, next and join

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[8LTSSXUYhzUY8vmkyKUYevUYrDgYNK07yaf7soC3kKgMlOtHkLt[kZEclBtkyOoYwvtJGK2YevUY[v65iLtkeLEOhvtNlBtpizoY[v65yLdOkLEOhvtNlDn5lB07lOtJIDmllzmJ4vf7soCpiLdYIK0[eK27soleqO6keDpYp2CeH5d\F\fN6aQT6aQL[aQcUaQc[aQ57aQ5[aQDG

Start by grabbing Prime Numbers from
https://kustodetectiveagency.blob.core.windows.net/prime-numbers/prime-numbers.csv.gz and educate yourself on Special Prime numbers (https://www.geeksforgeeks.org/special-prime-numbers), this should get you to
https://aka.ms/{Largest special prime under 100M}

Once you get this done – you will get the next hint.

Cheers,
El Puente.

//Method 1 – This query will calculate the largest prime under 100M using the Sieve of Eratosthenes method to test each prime

Challenge4
| serialize
| order by Primes asc
| extend prevA = prev(Primes,1)
| extend NextA = next(prevA,1)
| extend test =  prevA + NextA + 1
| where test % 2 != 0 // skip even numbers
| extend divider = range(3, test/2, 2) // divider candidates
| mv-apply divider to typeof(long) on
(
  summarize Dividers=countif(test % divider == 0) // count dividers
)
| where Dividers == 0 // prime numbers don’t have dividers
| where test < 100000000 and test > 99999000
| top 1 by test

//Method 2 – This query will calculate the largest prime under 100M by comparing special primes to the data set of all prime numbers

Challenge4
| serialize
| project specialPrime = prev(Primes) + Primes + 1
| join kind=inner (Challenge4) on $left.specialPrime == $right.Primes
| where specialPrime < 100000000
| top 1 by Primes desc

The number we want is 99999517 so we can now head over to http://aka.ms/99999517

Well done, my friend.
It's time to meet. Let's go for a virtual sTREEt tour...
Across the Big Apple city, there is a special place with Turkish Hazelnut and four Schubert Chokecherries within 66-meters radius area.
Go 'out' and look for me there, near the smallest American Linden tree (within the same area).
Find me and the bottom line: my key message to you.

Cheers,
El Puente.

PS: You know what to do with the following:

----------------------------------------------------------------------------------------------

.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")
}

Getting the right size area can be tricky, a KQL command that will be extremely helpful will be geo_point_to_h3cell

//This query will filter a specific size area until it makes the set of trees given in the clue

let locations =
nyc_trees
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where spc_common == “‘Schubert’ chokecherry”
| summarize count() by h3cell, spc_common
| where count_ == 4
| summarize mylist = make_list(h3cell);
let final =
nyc_trees
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where h3cell in (locations)
|where spc_common ==  “Turkish hazelnut” or spc_common == “American linden”
| summarize count() by h3cell, spc_common
| where spc_common == “Turkish hazelnut” and count_ ==1
| project h3cell;
nyc_trees
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where h3cell in (final)
| where spc_common == “American linden”
| top 1 by tree_dbh asc
| project latitude, longitude
| extend TourLink = strcat(‘https://www.google.com/maps/@’, latitude, ‘,’, longitude, ‘,4a,75y,32.0h,79.0t/data=!3m7!1e1!3m5!1s-1P!2e0!5s20191101T000000!7i16384!8i8192’)

https://www.google.com/maps/@40.71222313,-73.96452201,3a,90y,94.82h,77.84t/data=!3m7!1e1!3m5!1s-1P!2e0!5s20191101T000000!7i16384!8i8192

Using the mural the phrase we are looking for is “ASHES to ASHES”

The trick with this challenge is you need to be able to create a set of vehicles that weren’t moving during the robbery, of course the catch is that only moving vehicles have records in the traffic data. KQL commands that will be useful for this challenge are join, remember that there are different kinds of joins and arg_max

We have a situation, rookie.
As you may have heard from the news, there was a bank robbery earlier today.
In short: the good old downtown bank located at 157th Ave / 148th Street has been robbed.
The police were too late to arrive and missed the gang, and now they have turned to us to help locating the gang.
No doubt the service we provided to the mayor Mrs. Gaia Budskott in past – helped landing this case on our table now.

Here is a precise order of events:

• 08:17AM: A gang of three armed men enter a bank located at 157th Ave / 148th Street and start collecting the money from the clerks.
• 08:31AM: After collecting a decent loot (est. 1,000,000$ in cash), they pack up and get out.
• 08:40AM: Police arrives at the crime scene, just to find out that it is too late, and the gang is not near the bank. The city is sealed – all vehicles are checked, robbers can’t escape. Witnesses tell about a group of three men splitting into three different cars and driving away.
• 11:10AM: After 2.5 hours of unsuccessful attempts to look around, the police decide to turn to us, so we can help in finding where the gang is hiding.

Police gave us a data set of cameras recordings of all vehicles and their movements from 08:00AM till 11:00AM. Find it below.

Let’s cut to the chase. It’s up to you to locate gang’s hiding place!
Don’t let us down!

//This query will calculate a set of cars not moving during the robbery, which then started moving after it occurred and track vehicles heading to the same address

let Cars =
Traffic
| where Street == 148 and Ave == 157
| where Timestamp > datetime(2022-10-16T08:31:00Z) and Timestamp < datetime(2022-10-16T08:40:00Z) | join kind=leftanti ( Traffic | where Timestamp >= datetime(2022-10-16T08:17:00Z) and Timestamp <= datetime(2022-10-16T08:31:00Z)
) on VIN
| summarize mylist = make_list(VIN);
Traffic
| where VIN in (Cars)
| summarize arg_max(Timestamp, *) by VIN
| summarize count(VIN) by Street, Ave
| where count_VIN == 3

Onboarding //This is the name of the table we will be running our query against
| summarize sum(Score) //the sum command will add up all the numbers in the “Score” column

In order to solve this you’ll need to work with the weights of the books on the selves.
KQL commands that will be helpful are sum() and join

This was supposed to be a great day for Digitown’s National Library Museum and all of Digitown.
The museum has just finished scanning more than 325,000 rare books, so that history lovers around the world can experience the ancient culture and knowledge of the Digitown Explorers.
The great book exhibition was about to re-open, when the museum director noticed that he can’t locate the rarest book in the world:
“De Revolutionibus Magnis Data”, published 1613, by Gustav Kustov.
The mayor of the Digitown herself, Mrs. Gaia Budskott – has called on our agency to help find the missing artifact.

Luckily, everything is digital in the Digitown library:

• – Each book has its parameters recorded: number of pages, weight.
• – Each book has RFID sticker attached (RFID: radio-transmitter with ID).
• – Each shelve in the Museum sends data: what RFIDs appear on the shelve and also measures actual total weight of books on the shelve.

Unfortunately, the RFID of the “De Revolutionibus Magnis Data” was found on the museum floor – detached and lonely.
Perhaps, you will be able to locate the book on one of the museum shelves and save the day?

//This query will calculate the weight of the books on each shelf and compare that to the weight registered by the sensor, find the shelf with extra weight and we’ll find our book!
Shelves
| mv-expand rf_ids
| extend RID = tostring(rf_ids)
| join (Books) on $left.RID == $right.rf_id
| summarize sum(weight_gram) by shelf, total_weight
| extend diff = total_weight – sum_weight_gram
| order by diff