{"id":43,"date":"2022-08-26T08:56:13","date_gmt":"2022-08-26T08:56:13","guid":{"rendered":"https:\/\/themeger.shop\/wordpress\/katen\/personal-alt\/?p=43"},"modified":"2023-10-14T10:19:09","modified_gmt":"2023-10-14T08:19:09","slug":"3-easy-ways-to-make-your-iphone-faster","status":"publish","type":"post","link":"https:\/\/www.mutlu.ch\/?p=43","title":{"rendered":"Kusto Detective Agency: Chall #3"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>Bank robbery<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>We have a situation, rookie.<br>As you may have heard from the news, there was a bank robbery earlier today.<br>In short: the good old downtown bank located at 157th Ave \/ 148th Street has been robbed.<br>The police were too late to arrive and missed the gang, and now they have turned to us to help locating the gang.<br>No doubt the service we provided to the mayor Mrs. Gaia Budskott in past &#8211; helped landing this case on our table now.<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Here is a precise order of events:<\/em><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><em><strong>08:17AM<\/strong>: A gang of three armed men enter a bank located at 157th Ave \/ 148th Street and start collecting the money from the clerks.<\/em><\/li>\n\n\n\n<li><em><strong>08:31AM<\/strong>: After collecting a decent loot (est. 1,000,000$ in cash), they pack up and get out.<\/em><\/li>\n\n\n\n<li><em><strong>08:40AM<\/strong>: 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 &#8211; all vehicles are checked, robbers can&#8217;t escape. Witnesses tell about a group of three men splitting into three different cars and driving away.<\/em><\/li>\n\n\n\n<li><em><strong>11:10AM<\/strong>: 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.<\/em><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Police gave us a data set of cameras recordings of all vehicles and their movements from 08:00AM till 11:00AM. Find it below.<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Let&#8217;s cut to the chase. It&#8217;s up to you to locate gang\u2019s hiding place!<br>Don&#8217;t let us down!<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Again you have to import the sata in you Cluster:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>.execute database script &lt;|\n\/\/ Create the table with the traffic information.\n\/\/ The data loading process estimated to take ~3-4min to complete (114M+ rows of data).\n\/\/ Notes: VIN - is Vehicle ID \n.create-merge table Traffic (Timestamp:datetime, VIN:string, Ave:int, Street:int)\n.ingest async into table Traffic (@'https:\/\/kustodetectiveagency.blob.core.windows.net\/digitown-traffic\/log_00000.csv.gz')\n.ingest async into table Traffic (@'https:\/\/kustodetectiveagency.blob.core.windows.net\/digitown-traffic\/log_00001.csv.gz')\n.ingest async into table Traffic (@'https:\/\/kustodetectiveagency.blob.core.windows.net\/digitown-traffic\/log_00002.csv.gz')<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Chall #3 Solution<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>let PossibleCars = Traffic| where Timestamp between (datetime(2022-10-16T08:31:00Z)..datetime(2022-10-16T08:40:00Z))| where ((Ave == 157 and Street == 148)) or (Ave == 156 and Street == 148) or (Ave == 158 and Street == 148) or (Ave == 157 and Street == 147) or (Ave == 157 and Street == 149)| summarize VINS = make_set(VIN);\n\nlet ParkedCars = Traffic| where Timestamp &gt; datetime(2022-10-16T08:40:00Z)| summarize arg_max(Timestamp, *) by VIN| summarize ParkedVINS=make_set(VIN) by Ave, Street;\n\nPossibleCars | extend dummy=1 | join kind=inner (ParkedCars | extend dummy=1) on dummy| project Ave, Street, set_intersect(VINS, ParkedVINS)| where array_length(Column1) &gt; 2\nThe gang is located? Avenue: &#91;42] Street: &#91;258]<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-1 is-layout-flex wp-block-gallery-is-layout-flex\">\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"600\" height=\"600\" data-id=\"480\" src=\"https:\/\/www.mutlu.ch\/wp-content\/uploads\/2022\/08\/image-2.png\" alt=\"\" class=\"wp-image-480\" srcset=\"https:\/\/www.mutlu.ch\/wp-content\/uploads\/2022\/08\/image-2.png 600w, https:\/\/www.mutlu.ch\/wp-content\/uploads\/2022\/08\/image-2-300x300.png 300w, https:\/\/www.mutlu.ch\/wp-content\/uploads\/2022\/08\/image-2-150x150.png 150w, https:\/\/www.mutlu.ch\/wp-content\/uploads\/2022\/08\/image-2-60x60.png 60w, https:\/\/www.mutlu.ch\/wp-content\/uploads\/2022\/08\/image-2-360x360.png 360w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure>\n<\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.credly.com\/org\/microsoft-azure-data-explorer\/badge\/kusto-detective-agency-case-3-badge.1\">Kusto Detective Agency &#8211; Case #3 Badge &#8211; Credly<\/a><\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>The gang is located? \nAvenue: 42\nStreet: 258<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Bank robbery 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 [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":480,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,25],"tags":[24,23],"class_list":["post-43","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-kql","category-microsoft","tag-kql","tag-microsoft"],"_links":{"self":[{"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=\/wp\/v2\/posts\/43","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=43"}],"version-history":[{"count":3,"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=\/wp\/v2\/posts\/43\/revisions"}],"predecessor-version":[{"id":715,"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=\/wp\/v2\/posts\/43\/revisions\/715"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=\/wp\/v2\/media\/480"}],"wp:attachment":[{"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=43"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=43"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mutlu.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=43"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}