Microsoft has released a Kusto Query Language(KQL) challenge for us to solve. It consists of five different challenges.
We’ve encountered a few challenging tasks so far this series, lets crack on and solve the next one – “Ready to play?”
This challenge consists of two parts. The first part will lead you to a new clue in order to solve the whole challenge.
The description of this challenge is vague. We don’t get much useful information.
We’re supposed to learn about prime numbers and special prime numbers.
The task in hand is to find the largest special prime under 100M.
I have no past experience with special prime numbers, but let us have a look!
A normal prime number is a natural number greater than 1 that is not a product of two smaller natural numbers. Atleast according to Wikipedia. Basically a prime is a whole number above 1 that cannot be made by multiplying other whole numbers. But what about special prime numbers?
Special prime numbers are two neighbouring prime numbers that together with +1 equals a prime number. Example 5+7+1 = 13. 13 is a prime, hence a special prime number. With this in mind, lets start cracking!
After ingesting the data into a table (PrimeNumbers), I’ve got the colum Number. The table is just full of primes and nothing else.
First thought is sorting the numbers in an ascending order. This is easily done by the following query
PrimeNumbers | sort by Number asc
So the fun begins. I’m used to the PowerShell way of thinking and I’m not alone. It’s natural to think “foreach-loop”. The problem is that you’ll have to get rid of the mentality for a second.
We need to figure out each neighbouring number and create a column containing the neighbouring numbers. Here the prev() function comes in handy
PrimeNumbers | sort by Number asc | extend previousNumber = prev(Number)
This will give us two columns, one with “the current” number called “Number” and one with the neighbouring number called “previousNumber”. Perfect!
Our task in hand becomes calculating the special prime based on these two columns. We’ll use the extend function again
PrimeNumbers | sort by Number asc | extend previousNumber = prev(Number) | extend SpecialNumber = (Number + previousNumber + 1)
So, what are we doing here? We’re creating three columns now, one with the prime, neighbouring prime and a fictive special prime. The thing is that we don’t really know if the column SpecialPrime in fact is a special prime yet, we’re just applying the calculation to all rows. Since we’re working with loads of rows, I decided to limit our query results a bit by adding a where clause
PrimeNumbers | sort by Number asc | extend previousNumber = prev(Number) | extend SpecialNumber = (Number + previousNumber + 1) | where SpecialNumber between (99000000 .. 100000000)
Noting new, just limiting the result where the special prime is in the range 99000000 – 100000000. Remember, the requirement is to have the largest special prime possible bellow 100M. We’re now calculated the fictive special prime in each row, now I just want to project these numbers
PrimeNumbers | sort by Number asc | extend previousNumber = prev(Number) | extend SpecialNumber = (Number + previousNumber + 1) | where SpecialNumber between (99000000 .. 100000000) | project SpecialNumber
Why you might ask? Because my way of finding out if the SpecialPrime column is actually a prime is to join it back in with the column Number!
PrimeNumbers | sort by Number asc | extend previousNumber = prev(Number) | extend SpecialNumber = (Number + previousNumber + 1) | where SpecialNumber between (99000000 .. 100000000) | project SpecialNumber | join kind=inner (PrimeNumbers) on $left.SpecialNumber == $right.Number
So we’re joining the table PrimeNumbers back into our temporary table we’ve projected earlier matching SpecialPrime to Number. This will only give us the numbers from SpecialPrime that matches with Number! Hence only giving us the actual special prime numbers. But how do we find the largest possible number? There are many ways to accomplish, but an easy one is to sort…
PrimeNumbers | sort by Number asc | extend previousNumber = prev(Number) | extend SpecialNumber = (Number + previousNumber + 1) | where SpecialNumber between (99000000 .. 100000000) | project SpecialNumber | join kind=inner (PrimeNumbers) on $left.SpecialNumber == $right.Number | sort by SpecialNumber desc
There we go! We’ve got the largest possible special prime number bellow 100M. But, what’s next?
The description tells us we need to construkt an aka.ms link.
What do we get? A simple text file. To my suprise, we’re not done with the case… We have yet another task to solve the complete challenge. But what does the text mean?
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") }El Puente
I think below link is helpful to get final answer.
https://github.com/pthoor/KustoDetectiveAgencyHints