Watching the Watchers: Monitoring Azure Sentinel Query Activity for Malicious Activity.

Pete Bryan · ‎Oct 01 2020

Given the richness and potential sensitivity of data stored in security solutions such as Azure Sentinel they can become important targets for external threat actors who gain access to the environment as well as malicious insiders and therefore should be monitored for specific threats. To assist in this a new feature was recently added to Log Analytics (the technology the underpins Azure Sentinel’s log storage and query capabilities), that allows you to capture an audit log of all queries run against the workspace. In this blog we will look at how we can use this new audit feature to monitor and hunt in these audit logs to find potentially malicious activity.

My colleague Sarah Young has shared a great blog covering some aspects of the audit log and how it can be combined with Azure Activity data to give you a wider perspective on user activity. In this blog I am going to take it a step further and deep dive into the details of the logs and look at some hunting and detection queries for this log source that we recently released.

Details of how to set up this audit logging can be found in the audit log documentation. For this blog we are assuming you are writing the audit logs into an Azure Sentinel workspace. Whilst it is possible to write the logs to other sources such as Azure Storage it is recommended for monitoring that you store in an Azure Sentinel Workspace so that you can leverage its capabilities to support hunting and detections.

Anatomy of the Audit Log

Before we start hunting in the logs we need to understand the structure of it and where to find the most valuable data. From a security perspective there are a few fields of particular interest which the table below details. Once you start collecting these audit logs you should spend some time to ensure that you fully understand what they contain.

Column Name	Details
Time Generated	This is the UTC time that the query was submitted.
AADEmail	This is the email/UPN of the user who submitted the query.
RequestClientApp	This is the name of the client type used to run the query, for queries made via the Azure Sentinel UI it will be populated with “ASI_Portal” – this is broadly analogous to a UserAgent.
QueryText	This is as string of the query text that is being executed. It often includes elements not present in the UI, for example you may see “set query_take_max_records=10001;set truncationmaxsize=67108864;” at the start of a query, this is automatically added by the client and was not necessarily entered by the user themselves.
RequestContext	This is the Azure resource the query was run against. This field is useful if you have the audit log from multiple workspaces aggregated in one workspace as this helps you identify the target workspace for the query. If the query covers multiple workspaces there will be multiple values here.
ResponseCode	The is the HTTP response code from submitting the query i.e. 200 indicates the query executed without issue.
ResponseRowCount	The number of result rows the query returned.
ResponseDurationMs	How long the query took to execute and return a response.

Hunting and Detection Opportunities

Users searching for specific VIP user identifiers.

ATT&CK T1530, T1213, T1020

One of the most obvious and easy detection opportunities with these audit logs is to look for specific key words in the queries being run. Given that security logs often contain an abundance of personal detail a malicious user may want to search the data for specific user activity. We can easily identify this by looking for queries that contains unique identifiers for those users. Below is an example of a query that looks for queries that contains the email addresses of specified VIP users. You could also modify this to include searches for display names or other identifiers. Whilst a security analyst may have legitimate reason to search based on a specific user identifier such as this it would not be expected to be a regular search unless analysts were given specific tasking.

let vips = dynamic(['vip1@email.com','vip2@email.com']);
  let timeframe = 1d;
  LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  | where QueryText has_any (vips)
  | project TimeGenerated, AADEmail, RequestClientApp, QueryText, ResponseRowCount, RequestTarget

User searching for secrets.

ATT&CK T1530, T1213

In a similar manner as the query above we can also look for users querying for secrets within logs. It is not uncommon for passwords and other secrets to leak into logs and a malicious actor could easily use KQL to search for these. There have been several high profile incidents where plain text credential’s appeared in logs, such as the incident Twitter reported in 2018, and attackers are likely to take advantage of access to a SIEM solution such as Azure Sentinel to check for this. For more on hunting for credentials in datasets please take a look at this RSA blog.

Given the numerous cases where a user might legitimately search of strings such ‘password’ you will need to tailor the keyword and exclusion lists for your environment.

 // Extend this list with items to search for
  let keywords = dynamic(["password", "pwd", "creds", "credentials", "secret"]);
  // To exclude key phrases or tables to exclude add to these lists
  let table_exclusions = dynamic(["AuditLogs","SigninLogs", "LAQueryLogs", "SecurityEvent"]);
  let keyword_exclusion = dynamic(["reset user password", "change user password"]);
  let timeframe = 7d;
  LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  | where RequestClientApp != 'Sentinel-General'
  | extend querytext_lower = tolower(QueryText)
  | where querytext_lower has_any(keywords)
  | project TimeGenerated, AADEmail, QueryText, RequestClientApp, RequestTarget, ResponseCode, ResponseRowCount, ResponseDurationMs, CorrelationId
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail
  | join kind=leftanti ( LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  | where RequestClientApp != 'Sentinel-General'
  | extend querytext_lower = tolower(QueryText)
  | where QueryText has_any(table_exclusions) or querytext_lower has_any(keyword_exclusion))
  on CorrelationId

New user running query.

ATT&CK T1530, T1213

An attacker who compromises an account could leverage that account to access Azure Sentinel/Log Analytics even if that user does not regularly use the service. Therefore we can hunt for situations where we see a user executing queries who has not recently done so. This query is likely to be too false positive prone to be useful as detection logic but makes a useful hunting starting point.

let lookback = 7d;
  let timeframe = 1d;
  LAQueryLogs
  | where TimeGenerated   between(startofday(ago(lookback))..startofday(ago(timeframe)))
  | summarize by AADEmail
  | join kind = rightanti (LAQueryLogs
  | where TimeGenerated > ago(timeframe))
  on AADEmail
  | project TimeGenerated, AADEmail, QueryText, RequestClientApp, RequestTarget
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail

New Service Principal running query.

ATT&CK T1530, T1213

As with new user query we can also hunt for new Service Principals running queries. It is important to cover this perspective as well as Service Principals can also be given access to run queries. Whilst the number of Service Principals running queries is likely to be more static than with users this query is still likely to be false positive prone and therefore is better suited as a hunting query.

 let lookback = 7d;
  let timeframe = 1d;
  LAQueryLogs
  | where TimeGenerated between (ago(lookback)..ago(timeframe))
  | where ResponseCode == 200 and RequestClientApp != "AppAnalytics" and AADEmail !contains "@"
  | distinct AADClientId
  | join kind=rightanti(
  LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  | where ResponseCode == 200 and RequestClientApp != "AppAnalytics" and AADEmail !contains "@"
  )
  on AADClientId
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail

New client running query.

ATT&CK T1530, T1213, T1020

Often when an account is compromised by an external attacker there are indicators that the account is not being controlled by a legitimate user, one of these indicators can be when the account uses a technology not commonly associated with regular users. We can look for this in the audit log by monitoring for queries executed by a client type we have not previously seen. This query can also be combined with some of the anomaly detection queries detailed later in order to create a more refined detection or hunting query.

let lookback = 7d;
  let timeframe = 1d;
  LAQueryLogs
  | where TimeGenerated between (ago(lookback)..ago(timeframe))
  | where ResponseCode == 200
  | join kind= rightanti(
  LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  )
  on RequestClientApp
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail

Multiple large queries

ATT&CK T1030

A malicious actor with access to a data source may look to exfiltrate date from the source for persistent offline access. During the 2014 breach of Anthem attackers searched across datasets to find material of interest and then extracted these in bulk queries . We can monitor this activity such as this by looking for multiple large queries made by a single user. Most legitimate users are unlikely to run multiple queries that return many thousands for rows as data that size is unmanageable from an analysis perspective.

  let UI_apps = dynamic(['ASI_Portal','AzureMonitorLogsConnector','AppAnalytics']);
  let threshold = 3;
  let timeframe = 1d;
  LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  | where (ResponseRowCount == 10001 and RequestClientApp in(UI_apps)) or (ResponseRowCount > 10001 and RequestClientApp !in(UI_apps))
  | summarize count() by AADEmail
  | where count_ > threshold
  | join kind=rightsemi (
  LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  | where (ResponseRowCount == 10001 and RequestClientApp in(UI_apps)) or (ResponseRowCount > 10001 and RequestClientApp !in(UI_apps)))
  on AADEmail
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail

Data volume anomalies.

ATT&CK T1030

As well as just looking for large query results, we can use KQL’ timeseries capabilities to look for anomalies in the volume of data being returned by a user. Whilst this may generate some false positives for users who only use the service occasionally it is an effective tool for identifying suspicious changes in regular user’s activity.

 let lookback = 7d;
  let threshold = 0;
  LAQueryLogs
  | make-series rows = sum(ResponseRowCount) on TimeGenerated in range(startofday(ago(lookback)), now(), 1h)
  | extend (anomalies, score, baseline) = series_decompose_anomalies(rows,3, -1, 'linefit')
  | mv-expand anomalies to typeof(int), score to typeof(double), TimeGenerated to typeof(datetime)
  | where anomalies > threshold
  | sort by score desc
  | join kind=rightsemi (
  LAQueryLogs
  | summarize make_set(QueryText) by AADEmail, RequestTarget, TimeGenerated = bin(TimeGenerated, 1h))
  on TimeGenerated
  | project TimeGenerated, AADEmail, RequestTarget, set_QueryText
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail

Cross workspace query anomalies

ATT&CK T1530, T1213, T1020

As well as looking for anomalies in data volume we can also look for anomalies in the number of different workspaces that a user is querying. A user who regularly runs queries against a single workspace who then suddenly starts querying multiple workspaces is a potential indicators of malicious activity.

 let lookback = 30d;
  let timeframe = 1d;
  let threshold = 0;
  LAQueryLogs
  | where TimeGenerated between (ago(lookback)..ago(timeframe))
  | mv-expand(RequestContext)
  | extend RequestContextExtended = split(RequestTarget, "/")
  | extend Subscription = tostring(RequestContextExtended[2]), ResourceGroups = tostring(RequestContextExtended[4]), Workspace = tostring(RequestContextExtended[8])
  | summarize count(), HistWorkspaceCount=dcount(Workspace) by AADEmail
  | join (
  LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  | mv-expand(RequestContext)
  | extend RequestContextExtended = split(RequestTarget, "/")
  | extend Subscription = tostring(RequestContextExtended[2]), ResourceGroups = tostring(RequestContextExtended[4]), Workspace = tostring(RequestContextExtended[8])
  | summarize make_set(Workspace), count(), CurrWorkspaceCount=dcount(Workspace) by AADEmail
  ) on AADEmail
  | where CurrWorkspaceCount > HistWorkspaceCount
  // Uncomment follow rows to see queries made by these users
  //| join (
  //LAQueryLogs
  //| where TimeGenerated > ago(timeframe))
  //on AADEmail
  //| extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail

User query result volume variation.

ATT&CK T1530, T1213, T1020

As well as looking for timeseries based anomalies in data volume we can also look for situations where a specific users total returned rows of data for a day increases significantly from the last seven days. Whilst this may produce false positives in occasional users it can be a useful hunting query when looking at users who regularly run queries but who suddenly exhibit suspicious behavior.

let threshold = 10;
  let lookback = 7d;
  let timeframe = 1d;
  let baseline = 10000;
  let diff = 5;
  let anomolous_users = (
  LAQueryLogs
  | where TimeGenerated between(startofday(ago(lookback))..startofday(ago(timeframe)))
  | summarize score=sum(ResponseRowCount) by AADEmail
  | join kind = fullouter (LAQueryLogs
  | where TimeGenerated > startofday(ago(timeframe))
  | summarize score_now=sum(ResponseRowCount) by AADEmail)
  on AADEmail
  | extend hist_score = iif((score/29)*threshold > baseline, (score/29)*threshold, baseline)
  | where isnotempty(score)
  | where score_now > hist_score*diff
  | project AADEmail);
  LAQueryLogs
  | where TimeGenerated > ago(timeframe)
  | where AADEmail in(anomolous_users)
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail
  // Comment out the line below to see the queries run by users.
  | summarize total_rows = sum(ResponseRowCount), NoQueries = count(), AvgQuerySize = sum(ResponseRowCount)/count() by AADEmail

Multiple failed queries.

ATT&CK T1020

Another indicator of suspicious activity is the repeated failure of queries. Regular users are unlikely to have many queries that fail repeatedly. Instead this is likely be caused by misconfigured automated queries. Legitimate services are likely to catch and remediate such issues quickly so searching for repeated failures can help to identify malicious services.

let lookback = 7d;
  let timeframe = 1h;
  let threshold = 10;
  LAQueryLogs
  | where TimeGenerated > ago(lookback)
  | where ResponseCode != 200
  | summarize count() by AADEmail, bin(TimeGenerated, timeframe)
  | where count_ > threshold
  | join kind=rightsemi (
  LAQueryLogs
  | where TimeGenerated > ago(lookback)
  | summarize make_set(QueryText) by AADEmail, bin(TimeGenerated, timeframe))
  on AADEmail, TimeGenerated
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail

All of the queries detailed here are available via the Azure Sentinel GitHub, in addition we are continuing our work to look at these audit logs and we hope to publish additional material on how to use them to make you queries, and team operations more efficient in the near future.

Thanks to Ashwin Patil, Christopher Glyer and Shain Wray for thier input, and ideas on this topic.

Products (50)

Special Topics (27)

Video Hub (462)

Most Active Hubs

Most Active Hubs

Video Hub

Anatomy of the Audit Log

Hunting and Detection Opportunities