{"id":24194,"date":"2024-03-20T11:21:31","date_gmt":"2024-03-20T19:21:31","guid":{"rendered":"http:\/\/www.palada.net\/index.php\/2024\/03\/20\/news-17924\/"},"modified":"2024-03-20T11:21:31","modified_gmt":"2024-03-20T19:21:31","slug":"news-17924","status":"publish","type":"post","link":"http:\/\/www.palada.net\/index.php\/2024\/03\/20\/news-17924\/","title":{"rendered":"Remote Desktop Protocol: Executing the 4624_4625 Login Query"},"content":{"rendered":"<p><strong>Credit to Author: Angela Gunn| Date: Wed, 20 Mar 2024 16:11:40 +0000<\/strong><\/p>\n<div class=\"entry-content lg:prose-lg mx-auto prose max-w-4xl\" width=\"100%\" height=\"420\">\n<p><span data-contrast=\"auto\">The 4624_4625 login events query provides defenders, specifically analysts, with a useful tool for both identifying successful RDP logins (Windows Security Log Event 4624) and failed attempts (Windows Security Log Event 4625). These events can be generated by systems, domain controllers, and workstations.\u00a0<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">These Windows events are visible in Event Viewer, of course, but in this post (and in the companion video we\u2019ve put on our YouTube channel) we\u2019ll demonstrate our analysis using Sophos Central. The SQL query we\u2019ll use below is available to all on our Github.<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe loading=\"lazy\" class=\"youtube-player\" width=\"100%\" height=\"420\" src=\"https:\/\/www.youtube.com\/embed\/YpvAnSEk8HU?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\" frameborder=\"0\"><\/iframe><\/span><\/p>\n<p><strong>Building and executing the query<\/strong><\/p>\n<p>The SQL query we\u2019ll be working with looks like this:<\/p>\n<pre>SELECT  strftime('%Y-%m-%dT%H:%M:%SZ',datetime) AS date_time,  eventid AS EventID,  CASE  WHEN eventid = 4624 THEN eventid || ' - Successful Login'  WHEN eventid = 4625 THEN eventid || ' - Failed login'  END AS Description,  'Security' AS Source,  JSON_EXTRACT(data, '$.EventData.TargetUserName') AS Target_User,  JSON_EXTRACT(data, '$.EventData.WorkstationName') AS Source_Machine_Network,  JSON_EXTRACT(data, '$.EventData.IpAddress') AS Source_IP,  JSON_EXTRACT(data, '$.EventData.ProcessName') AS Process_Name,  JSON_EXTRACT(data, '$.EventData.LogonType') AS Logon_Type,  JSON_EXTRACT(data, '$.EventData.TargetUserSid') AS Target_User_SID,  JSON_EXTRACT(data, '$.EventData.Status') AS Logon_Status_Code,  JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS Target_Domain_Name,  JSON_EXTRACT(data, '$.EventData.AuthenticationPackageName') AS Authentication_package,  NULL AS SessionID,  NULL AS Session_ID,  'Security EVTX' AS Data_Source,  'Logins.01.1' AS Query  FROM sophos_windows_events  WHERE source = 'Security'  AND (eventid = 4624 OR eventid = 4625)  AND JSON_EXTRACT(data, '$.EventData.TargetUserName') LIKE '$$username$$'  AND JSON_EXTRACT(data, '$.EventData.IpAddress') LIKE '$$source_ip$$'  AND JSON_EXTRACT(data, '$.EventData.WorkstationName') LIKE '$$workstation$$'  AND time &gt; 0<\/pre>\n<p>To execute this in Sophos Central, navigate to<\/p>\n<p>Threat Analysis Center &gt; Live Discover &gt; Designer Mode<\/p>\n<p>and click the Create new query button, as shown in Figure 1.<\/p>\n<p><a href=\"https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-01.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-954236\" src=\"https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-01.png\" alt=\"A screen capture showing the creation of a new query in Live Discover\" width=\"640\" height=\"295\" srcset=\"https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-01.png 1651w, https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-01.png?resize=300,138 300w, https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-01.png?resize=768,354 768w, https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-01.png?resize=1024,471 1024w, https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-01.png?resize=1536,707 1536w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/a><\/p>\n<p><em>Figure 1: Creating a query on the Designer Mode screen; the Create new query button is on the right near the middle of the screen<\/em><\/p>\n<p>Clicking the button leads to a screen with a SQL box, into which you\u2019ll paste the query.<\/p>\n<p><span data-contrast=\"auto\">Note that this query has two variables \u2013 one for the targeted username(s) (username) and one for the IP address of the entity attempting access (source_ip). Both of these are strings; to ensure the query returns the greatest possible number of results, these should include wildcards for those variables. (When our Incident Response investigators use this in the course of their work, as a rule they run it against every Windows device on the network; the first time you run it and occasionally thereafter, consider doing the same to get the broadest possible view of what\u2019s happening on your network.)<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">To do that, edit these two variables in the Variable Editor just above the box into which the query is pasted. Click Show Variable Editor, then click + Add variable. In the Descriptive name field, type the first variable name (username); the type (String) and SQL variable name ($$username$$) will auto-populate. On the next line, do the same for the second variable ($$source_ip$$). On the right, add the percentage sign (%) that indicates a wildcard, as shown in Figure 2:<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><a href=\"https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-02.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-954237\" src=\"https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-02.png\" alt=\"Editing the variable in the query\" width=\"640\" height=\"222\" srcset=\"https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-02.png 1411w, https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-02.png?resize=300,104 300w, https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-02.png?resize=768,266 768w, https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/RDP-6-figure-02.png?resize=1024,355 1024w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/a><\/p>\n<p><em>Figure 2: The variable editing is nearly complete \u2013 one more percentage sign and it\u2019s ready<\/em><\/p>\n<p><span data-contrast=\"auto\">Next, scroll down to the Filters area and select the machines on which the query should run. (Since this is a Windows event, there\u2019s no point running it on any macOS or Linux machines in your estate, so those should be filtered out.) Clicking the tickbox next to \u201cOnline status\u201d (at the top of the column) selects all; click \u201cUpdate selected devices list\u201d to confirm.<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">Once the variables and filters are set, click Run Query, and click it again when the system asks if you want to run this untested query. The execution begins; the time to completion will of course depend on the size of your estate, your connection speed, and above all how large the event log is. However, it will eventually conclude, returning a table of results. It is of course possible to review these right in Sophos Central, but it\u2019s also possible (and perhaps a bit more pleasant) to export the whole thing to a CSV file for perusal in your preferred spreadsheet editor.<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><b><span data-contrast=\"auto\">Understanding the results<\/span><\/b><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">However you prefer to view the returned results, a few fields stand out:<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">In the table above, we\u2019ve noted a number of potential grounds for further investigation based on the results this query returns; here\u2019s one more. Though this query can spot potential RDP exposure issues, it may spot something else that needs your prompt attention &#8212; for instance, SMB.\u00a0<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><span data-contrast=\"auto\">There is, in the year 2024 (a full <\/span><i><span data-contrast=\"auto\">seven years<\/span><\/i><span data-contrast=\"auto\"> since EternalBlue and WannaCry dropped), no good reason to leave a shared folder or drive hanging out on the internet in this fashion \u2013 and yet one in five of the cases handled in 2022 and 2023 by the Incident Response team had evidence of SMB abuse. That statistic doesn\u2019t hold a candle to RDP\u2019s cursed dominance in our IR findings, where over 90 percent of the cases include RDP-related findings, but it\u2019s a good reminder that keeping an eye on your organization\u2019s logs with regularly run queries such as this can return some eye-opening results indeed.<\/span><span data-ccp-props=\"{&quot;335559739&quot;:360}\">\u00a0<\/span><\/p>\n<p><strong>Remote Desktop Protocol: The Series<\/strong><\/p>\n<p>Part 1: Remote Desktop Protocol: Introduction (<a href=\"https:\/\/news.sophos.com\/en-us\/2024\/03\/20\/remote-desktop-protocol-the-series\/\">post<\/a>, <a href=\"https:\/\/youtu.be\/dmzfBSs02lA\">video<\/a>)<br \/> Part 2: Remote Desktop Protocol: Exposed RDP (is dangerous) (<a href=\"https:\/\/news.sophos.com\/en-us\/2024\/03\/20\/remote-desktop-protocol-exposed-rdp-is-dangerous\/\">post<\/a>, <a href=\"https:\/\/youtu.be\/XyWnKaLwmkQ\">video<\/a>)<br \/> Part 3: RDP: Queries for Investigation (<a href=\"https:\/\/news.sophos.com\/en-us\/2024\/03\/20\/remote-desktop-protocol-queries-for-investigation\/\">post<\/a>, <a href=\"https:\/\/youtu.be\/-Vo_Gok59WE\">video<\/a>)<br \/> Part 4: RDP Time Zone Bias (<a href=\"https:\/\/news.sophos.com\/en-us\/2024\/03\/20\/remote-desktop-protocol-how-to-use-time-zone-bias\/\">post<\/a>, <a href=\"https:\/\/youtu.be\/gaq46NUkOyA\">video<\/a>)<br \/> Part 5: Executing the External RDP Query (<a href=\"https:\/\/news.sophos.com\/en-us\/2024\/03\/20\/remote-desktop-protocol-executing-the-external-rdp-query\/\">post<\/a>, <a href=\"https:\/\/youtu.be\/hGKvzkb47JA\">video<\/a>)<br \/> Part 6: Executing the 4624_4625 Login Query ([you are here], <a href=\"https:\/\/youtu.be\/YpvAnSEk8HU\">video<\/a>)<br \/> GitHub query repository: <a href=\"https:\/\/github.com\/SophosRapidResponse\/OSQuery\/tree\/main\/Artefacts\/Logins\">SophosRapidResponse\/OSQuery<br \/> <\/a>Transcript repository: <a href=\"https:\/\/github.com\/sophoslabs\/video-transcripts\">sophoslabs\/video-transcripts<br \/> <\/a>YouTube playlist: <a href=\"https:\/\/www.youtube.com\/playlist?list=PLW9m2f_dtUOVVita1zUhzv4T-VSzxUAUZ\">Remote Desktop Protocol: The Series<\/a><\/p>\n<\/p><\/div>\n<p><a href=\"https:\/\/news.sophos.com\/en-us\/2024\/03\/20\/remote-desktop-protocol-executing-the-4624_4625-login-query\/\" target=\"bwo\" >http:\/\/feeds.feedburner.com\/sophos\/dgdY<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p><img decoding=\"async\" src=\"https:\/\/news.sophos.com\/wp-content\/uploads\/2024\/03\/rdp-hero-06.jpg\"\/><\/p>\n<p><strong>Credit to Author: Angela Gunn| Date: Wed, 20 Mar 2024 16:11:40 +0000<\/strong><\/p>\n<p>Keeping an eye on who\u2019s trying to get onto your network \u2013 whether or not they\u2019re successful \u2013 can pay off on multiple fronts<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[10378,10377],"tags":[12657,27362,25038,18324,24552,27030],"class_list":["post-24194","post","type-post","status-publish","format-standard","hentry","category-security","category-sophos","tag-incident-response","tag-incident-response-tools","tag-mdr","tag-rdp","tag-security-operations","tag-sophos-x-ops"],"_links":{"self":[{"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/posts\/24194","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/comments?post=24194"}],"version-history":[{"count":0,"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/posts\/24194\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/media?parent=24194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/categories?post=24194"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.palada.net\/index.php\/wp-json\/wp\/v2\/tags?post=24194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}