Tag Archive: Kusto

Quick tip – Country Codes

All countries has an ISO code, described in ISO 3166 is an international standard.
These codes are used throughout the IT industry by computer systems and software to make it easier to identify a country.

It has multiple formats and they country codes are presented in the following formats: Alpha-2 (2 characters), Alpha-3 (3 characters) and Numeric (3 digits).

In the data from some logs like SigninLogs and IdentityLogonEvents the country is presented as Alpha-2. We realized pretty quick is that some 2-characters country codes are difficult to remember. As in below, picture it could be difficult to know all these countries.

We have been using this for a long time and thought it might be something others can use as well.

So to solve this I created a csv file and placed on github:

https://raw.githubusercontent.com/mattiasborg82/Hunting/main/General/cc.txt

To be able to join our data with this file we can use the external data operator in Kusto

Since it’s a CSV file, we can make it more usable by split the rows on comma

To to build the full use-case for this, we join it with our SigninLogs (or other logs that uses the country code)

Copy friendly code

let CountryCodes = externaldata (CountryCode:string)
[
 @"https://raw.githubusercontent.com/mattiasborg82/Hunting/main/General/cc.txt"
]
with(ignoreFirstRecord=true);
SigninLogs
| where isnotempty(Location)
| join kind=leftouter (
    CountryCodes
    | extend Country = tostring(split(CountryCode, ",")[0]),
              Location = tostring(split(CountryCode, ",")[1])
    | project-away CountryCode)
on Location
| summarize count() by Country,UserDisplayName

This can be used further to combine with conditional access blocks showing potential credential leak

Happy Hunting!

Graph semantics in Kusto

Earlier this month, the Kusto team announced graph semantics feature in Kusto.

This Kusto extension makes it possible to contextualize data in graphs which consists of Nodes and Edges that can be connected. These connections can visualize the relationships between the Nodes.

To describe graph semantics in a none-tech scenario, the best way is to look at social connections where people have connections, one to many, many to many and many to one

let Users = datatable (UserId:string , name:string)[
"1","Mattias",
"2","XboxController",
"3","Stefan",
"4","XBOX"
]; // nodes
let Knows = datatable (FirstUser:string , SecondUser:string)[
"1","2",//Mattias knows xbox controller
"2","4",//Xbox controller knows xbox
"1","3"//Mattias knows Stefan
]; // edges
Knows
| make-graph FirstUser --> SecondUser with Users on UserId
| graph-match (user)-->(middle_man)-->(friendOfAFriend)
    project SecLabs_person = user.name, middle_man = middle_man.name, kusto_friend_of_friend = friendOfAFriend.name

What can it use it for?

  • Many-to-many relations
  • Hierarchical data
  • Networked relationships
  • Social Networks
  • Recommendation systems
  • Connected assets
  • Knowledge graphs

From a hunting perspective, we can connect systems in a unspecified amounts of times. Since we can’t to recursive join, we can use graph to connect unknown number of systems.

Time-aware

In a scenario where we have lateral movement, we could connect all devices involved and at what time (of course depending on the data source, but if we have data from Microsoft Defender XDR data with network data from the endpoint sensor and know about activities involving certain ports, 3389 for example) we could draw all show how and when the threat actor moved laterally.

For further information, please visit: https://learn.microsoft.com/en-us/azure/data-explorer/graph-overview

For Kusto training, CTF mode, Kusto Detective: https://detective.kusto.io/

Microsoft Sentinel Parsing tips – Whitespace control

This post will be a part of a multiple posts to cover data parsing in Microsoft Sentinel.

Intro

Kusto is a powerfull query language and easy to adopt.

Even if Kusto is very powerfull, working with custom log sources is, sometimes, a mess. Some parsers requires more effort and some are very simple.

In general, when it’s possible to use operators like “parse” (link) function or “parse-kv” (link) it’s very welcome. However, the reality has a different challenge for us.

In this post we want to share a quick pro tip to solve the mystic of hidden whitespaces

The challenge of whitespaces

Whitespaces ” ” exists everywhere, the challenge is how it’s presented in log analytics.

Log analytics does a lot for the user in terms of nicely present data. It actually removes duplicate whitespaces, as well as leading and trailing whitespace.

This could result in problems like failing parsers, regex and string operators like “==”, “startswith”, “endswith” etc will fail. Especially if it’s not consistent.

Marking the string in the output view does not show the extra whitespaces

Copying the text and paste into a text-editor will not show it either like in below example where we copied the output into VS Code (we can only see one dot to show one whitespace between foo and bar)

However, the double whitespaces are interpreted during execution, and it’s only in the presentation view the extra is removed. As in below example, we used split on ” ” to show the existence of the double whitespace.

When working with multiple log sources you don’t want to search and see if they exist (which may change during the log source life cycle), you rather want a way to always make the log to look good in your parser.

Solution

To properly address this (if there aren’t any good ways to change the audit settings of the system sending the logs)

To handle the duplicate white spaces we use the replace_regex function (link here) and use the whitespace “\s” with the quantifier “+” which means one or multiple times and replace it with a space ” “.

This will search for spaces (one or more) and replace it with a single, because we don’t want to remove single spaces. And by using the same column name “SyslogMessage” we will actually reuse the same column for our clean output.

Please note that this will not change the message in the database, only during execution.

Doing this gives us the following output.

The next step is that we want to remove the leading and trailing whitespaces. If we for instance expect the first character to be a value, the leading whitespace could make our parser to fail or an analytic rule.

We have seen occasions where this happens from time to time and not all messages in a log source.

To fix the leading and trailing whitespaces we use another regex to look for start of string and end of string. But this time we want to replace with “nothing/null” which is why we can’t use this regex in the first cleaning.

In the second run we use the same column name again to cleanup the SyslogMessage. There is a best practice to always keep the original message, however, this is to solve an error from the log source and not to alter the SyslogMessage.

The regex starts with an anchor “^” to define the start of the string and followed by a whitespace “\s” since we cleaned all double whitespaces we don’t need to use the quantifier. To handle the trailing whitespace we use the OR operand “|” and check for a whitespace “\s” followed by the anchor “$” to determine the end of the string. If we get any hits it will be replaced with null and we have a clean string.

By adding these 2 lines of code to the parser, we will avoid running into strange issues which could take some time to troubleshoot.

//Sample
CustomLogSource_CL
| extend SyslogMessage = replace_regex(SyslogMessage,@"\s+",@" ") //Remove duplicate whitespaces
| extend SyslogMessage = replace_regex(SyslogMessage,@"^\s|\s$",@"") //Remove leading and trailing whitespaces

Happy Hunting!

Use kusto to breakdown time stamps

Some times you might want to split the time stamp of an event into smaller pieces, like month, day, hour etc.

For instance, you might want to see if you have more alerts during some specific hours of the day or if anyone is using RDP in the middle of the night.

To achieve this we use the function datetime_part which can split the time stamp to the following parts

  • Year
  • Quarter
  • Month
  • week_of_year
  • Day
  • DayOfYear
  • Hour
  • Minute
  • Second
  • Millisecond
  • Microsecond
  • Nanosecond

This data could, of course, be used to further analysis and joined with other events.

//Sample query
AlertInfo
| extend alerthour = datetime_part("hour", Timestamp)
| summarize count() by alerthour, DetectionSource
| sort by alerthour asc
| render areachart   

For further reading about Kusto datetime_part, please visit
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/datetime-partfunction

#HappyHunting

Advanced Hunting – Defender ATP – Squirrel

When working with Advanced Hunting in Defender ATP, you tend to always want to update your queries as you learn. You will probably also notice that sometimes your query wasn’t broad enough or all information was not available at the time. And sometimes you just want to make it look better for others to use in a shared environment.

We have updated the Squirrel hunting query to adjust to more parameters which can be used. we simple remove the check for a parameter and focus on the http part instead.

There are also some legit domains which are used by some of the applications, slack and discord to mention some of them.

ProcessCreationEvents
| where (ProcessCommandLine has "update.exe") or (ProcessCommandLine has "squirrel.exe")
| where (ProcessCommandLine contains "http")
| extend URL=extract(@"((http:|https:)+[^\s]+[\w])", 1, ProcessCommandLine)
| where URL !in ("https://slack.com/desktop/update/windows_x64", "https://discordapp.com/api/updates/stable")
| sort by EventTime desc 
| project EventTime, 
          ComputerName,
          URL,
          FolderPath, 
          ProcessCommandLine, 
          AccountName, 
          InitiatingProcessCommandLine, 
          ReportId, 
          ProcessId, 
          InitiatingProcessId

Happy Hunting!

Hunt for nuget/Squirrel update vulnerability

A few days ago, a post on medium stated that an arbitrary code execution was possible in Squirrel which affected Teams and other applications which used Squirrel and Nuget for updates.

https://medium.com/@reegun/nuget-squirrel-uncontrolled-endpoints-leads-to-arbitrary-code-execution-80c9df51cf12

In the post, Teams is mentioned as example but other affected application were mentioned on twitter.

So, to see what our environment is up to with regards to this. Our favorite place to go to: Defender ATP – Advanced Hunting!

To explain the query, since there are other apps than teams which uses Squirrel, we aim to keep the query as broad as we can.

Since some applications uses Squirrel and web for updates we can’t simply say that all web requests are malicious. But we have done some verification and discovered many apps vulnerable to this.

To make it more easy to overview we’re adding the URL to a column

To continue this we can count unique URL’s to find anomalies

Edit: An Updated Query can be found on the link below here http://blog.sec-labs.com/2019/07/advanced-hunting-defender-atp-squirrel/

ProcessCreationEvents
| where ProcessCommandLine has "update.exe"
| where (ProcessCommandLine contains "http") and (ProcessCommandLine contains "--update")
| extend exeURL = case(ProcessCommandLine has "=",split(ProcessCommandLine, "=", 1), 
                       ProcessCommandLine !has "=", split(ProcessCommandLine, "--update ",1), 
                       "Default")
| where exeURL != "Default"
| sort by EventTime desc 
|project EventTime, 
          ComputerName,
          exeURL,
          FolderPath, 
          ProcessCommandLine, 
          AccountName, 
          InitiatingProcessCommandLine, 
          ReportId, 
          ProcessId, 
          InitiatingProcessId

Defender Application Control would definitely block this attack and other mitigations in operating system will harden the clients in your environment.

Happy Hunting!

Hunting Windows Defender Exploit Guard with ATP

Alright, since I happen to be in a blog mode I keep the posts coming.

This post continue to explore the hunting capatibilities in Defender ATP by query for Exploit Guard detections.

So what’s this Exploit Guard?

Windows Defender Exploit Guard is a new set of intrusion prevention capabilities which are built-in with Windows 10, 1709 and newer versions.

Exploit Guard consists of 4 components which are designed to lock down the device against a wide variety of attack vectors and block behaviors commonly used in malware attacks, while enabling enterprises to balance their security risk and productivity requirements

ComponentDetails
Attack Surface Reduction (ASR)A set of controls that enterprises can enable to prevent malware from getting on the machine by blocking Office-, script-, and email-based threats
Network Protection Protects the endpoint against web-based threats by blocking any outbound process on the device to untrusted hosts/IP through Windows Defender SmartScreen
Controlled Folder AccessProtects sensitive data from ransomware by blocking untrusted processes from accessing your protected folders
Exploit ProtectionA set of exploit mitigations (replacing EMET) that can be easily configured to protect your system and applications

Example of ASR rules

• Block Office apps from creating executable content
• Block Office apps from launching child process
• Block Office apps from injecting into process
• Block Win32 imports from macro code in Office
• Block obfuscated macro code

Exploit Guard is configured through MDM (Intune) or SCCM or GPO’s or PowerShell.

If you have Microsoft 365 E5 license or Threat Protection license package, you don’t have to use Windows Event Forward to get the events in a central log solution. They will automatically be forwarded to your Microsoft 365 security portal https://security.microsoft.com where you have a nice looking dashboard where you can see alerts and configurations of ASR and other things.

This following dashboard is a part from the Monitor and Report section in the portal

Back to Defender ATP and the hunting which this post was supposed to be all about.

We have published some posts now about hunting custom alerts.

In the query console in Defender ATP we started to go backwards to find the ASR events. It’s simple. configure your client, run a few attacks which will trigger the alerts.

We looked in the MiscEvents for all events (filtered on computername and time). Which gaves us ideas of ActionTypes to use in the query.

Examples from the output:

AsrOfficeMacroWin32ApiCallsAudited
AsrPsexecWmiChildProcessBlocked
ControlledFolderAccessViolationBlocked
ExploitGuardAcgAudited
ExploitGuardChildProcessAudited
ExploitGuardNetworkProtectionBlocked
ExploitGuardNonMicrosoftSignedAudited
ExploitGuardWin32SystemCallBlocked
SmartScreenAppWarning
SmartScreenUrlWarning
SmartScreenUserOverride

Interesting note “SmartScreenUserOverride” is a separate event which you can query

When we had the raw Actiontypes we created the query to cover as much as we could.

//Happy Hunting
MiscEvents 
| where ActionType contains "asr" or
        ActionType contains "Exploit" or
        ActionType contains "SmartScreen" or
        ActionType contains "ControlledFolderAccess"
| extend JsonOut = parse_json(AdditionalFields)
| sort by EventTime desc 
| project EventTime, ComputerName, InitiatingProcessAccountName, ActionType,  
         FileName, FolderPath, RemoteUrl, ProcessCommandLine, InitiatingProcessCommandLine,
         JsonOut.IsAudit,JsonOut.Uri,JsonOut.RuleId,JsonOut.ActivityId
         

We are also parsing AdditionalFields to be able to add extra value to events which contained such data.

From this point we can do additional filters. For example, if you want to enable ASR enterprise wide, set them in auditmode and report on the alerts without affect user productivity, remediate and the do a enterprise wide block enrollment

Happy Hunting!