Tag: #opentowork

Using KQL with Azure AD

Understanding Azure data and how it can be reviewed using Kusto Query Language queries is necessary for any security minded person. This blog post shows how KQL can be used to track newly synced identities for suspicious activity.

If you work with Microsoft cloud services very much you will have noticed that the service telemetry is often available using Kusto Query Language via Azure Data Explorer. The online documentation explains that Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. Kusto queries use schema entities that are organized in a hierarchy like SQLs: databases, tables, and columns. What is a Kusto query? A Kusto query is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate. Kusto queries are made of one or more query statements.

KQL has been my favorite tool for years since it allowed me to identify service problems, gauge feature performance against desired results, and condense the information so that it made sense when put into a visual display like a chart. KQL allows for concise, quantifiable answers from large sets of data from one or more databases which can be tough to do using other methods.

As you would expect, KQL’s extensibility and ease of use is one reason it is used so much by Azure services. Another big selling point is how easy it is for a service to set up and use. KQL is prevalent throughout Azure due to that ease of ingesting service telemetry from Cosmos databases. Azure Cosmos DB in turn is the typical database used due to its versatility as a static data store for basic telemetry or as part of an event-driven architecture built with Azure Data Factory or Azure Synapse. KQL (via Azure Data Explorer or Azure Log Analytics) and Cosmos DB technologies fit together very well for a solution which can help handle large sets of data in a performant way and still allow for insights into service specific and even answer cross-functional questions. We’ll talk in a later blog about how important planning service telemetry is when creating a new software product or service.

Azure Sentinel provides KQL access for performing advanced analysis of activity from multiple sources combined with User and Entity Behavior Analytics (UEBA). If you are not lucky enough to have Sentinel Azure Active Directory by itself allows you to review tenant-specific telemetry using KQL once it is published to an Azure Log Analytics workspace (albeit without the UEBA analytics). The data alone can be useful for understanding what ‘normal’ looks like in your environment or in threat hunting. The steps to sending the telemetry to your workspace and configuring retention and other settings can be found at this link.

Now that we know KQL is used with Azure AD let’s go over how to use it with a few real-world security scenarios.

One of the better-known avenues for exploitation of Azure AD is via AD on premises. AD is 23 years old now and though Microsoft does a great job of security updates and recommended controls the fact is that the AD attack surface is very large-especially if organizations do not maintain good security posture. It is a large and attractive target which means it is important to review the actions of newly synchronized users.

KQL can be used to query Azure AD and identify newly synced users and what those newly synced users have changed recently. This is a simple technique to use if you have a suspicion or just want to do a spot check. For routine matters I highly recommend using a solution which uses machine learning or AI to sift through the data and identify suspicious activity.

Figure 1 Log Analytics query for interesting audit events. Blurring and cropping to protect the innocent. Query text at bottom of article.

The data is exportable for preservation and review. When reviewing note that there are three rows in the query which will indicate that a new user was synced and the identity of that new user: Actor, OperationName, and TargetObjectUPN. We can use the Actor field since Azure AD hybrid sync automatically creates an AD on premise identity named something with SYNC-HOST in the string. The other indicators are OperationName is AddUser and of course the TargetObjectName for the identity.

Note that if your organization uses another principal for their sync service account you could add a line to select only that service principal name in the query like | where Actor = ‘actorstring’.

KQL’s real power comes into play when you can combine two or more databases together and query that data to gain a broader picture of a scenario. This is essentially what Sentinel and other services do albeit with the added special sauce of behavioral analytics and scoring algorithms.

For example, if you have a suspect or suspects you can also see which SaaS applications the identity has signed into recently to help gauge their sketchiness by reviewing the AAD audit logs with the sign in logs for the same identities. In the example below we are not filtering on a specific identity-though we could add a where statement to do that-but are querying for what any user who has been recently added via sync is signing into. You can perform a more targeted search by removing the remark on line 16 and looking to see if they sign into specific applications. For example, a user signing into “Azure Active Directory Powershell” immediately after sync could mean shenanigans.

Figure 2 Log Analytics query for interesting signin events. Blurring and cropping to protect the innocent. Query text at bottom of article.

Understanding Azure data and how it can be reviewed using Kusto Query Language queries is necessary for any security minded person. It can help to better understand information which is already present by filtering out data which is not necessary, extract relevant information from results spanning multiple databases, or even spot trends or anomalies. The ability to construct KQL queries is a valuable skill, and hopefully one that this blog post will has helped you strengthen.

Audit Log query

AuditLogs

    | where ActivityDateTime >= ago(3d) //Range in time from now to query.

    | extend Actor = tostring(InitiatedBy.user.userPrincipalName) //Add a row for the identity which requested the change.

    | extend TargetedObject = tostring(TargetResources[0].displayName) //Add a row for the object displayname which was changed.

    | extend TargetObjectUPN = tostring(TargetResources[0].userPrincipalName) //Add a row for the object UPN which was changed.

    | extend ObjectType = tostring(TargetResources[0].type) //Add a row for the type of object which was targeted.

    | where OperationName != “Update agreement”
and OperationName != “Import”
and OperationName != “Update StsRefreshTokenValidFrom Timestamp”
//Remove operational events which are not interesting.

    | project ActivityDateTime, Actor, ObjectType, OperationName, TargetObjectUPN, TargetedObject, ResultDescription //Display only the information which helps understanding of what happened for the scenario.

Query to see what recently added users (via AAD Connect sync) have signed into

//Query to find what recently synced users are signing into

AuditLogs

    | where ActivityDateTime >= ago(14d) //Range in time from now to query.

    | extend Actor = tostring(InitiatedBy.user.userPrincipalName) //Add a row for the identity which requested the change.

    | extend TargetedObject = tostring(TargetResources[0].displayName) //Add a row for the object displayname which was changed.

    | extend TargetObjectUPN = tostring(TargetResources[0].userPrincipalName) //Add a row for the object UPN which was changed.

    | extend ObjectType = tostring(TargetResources[0].type) //Add a row for the type of object which was targeted.

    | extend targetObjectId = tostring(TargetResources[0].id) //Extract the displayname of the target object to its own row.

    | extend InitiatedByUPN = tostring(InitiatedBy.user.userPrincipalName) //Extract the UPN of the actor object to its own row.

    | where OperationName != “Update agreement”
and OperationName != “Import”
and OperationName != “Update StsRefreshTokenValidFrom Timestamp”
//Remove operational events which are not interesting.

    | where OperationName == “Add user”
and InitiatedByUPN contains
“SYNC-HOST”

    | project ActivityDateTime, Actor, ObjectType, OperationName, TargetObjectUPN, TargetedObject, targetObjectId, ResultDescription //Display only the information which helps understanding of what happened for the scenario.

| join kind = rightsemi   //Join kind to show only the sign-in data related to out AuditLogs entries filtered above.

    (SigninLogs

    | extend operatingSystem = tostring(DeviceDetail.operatingSystem) //Place the client OS in its own row

    //| where AppDisplayName == “Graph Explorer” or AppDisplayName == “Azure Active Directory PowerShell” or AppDisplayName == “Microsoft Office 365 Portal” or AppDisplayName == “”

    ) on
$left.targetObjectId == $right.UserId

| project TimeGenerated, operatingSystem, Identity, AlternateSignInName, AppDisplayName, AuthenticationRequirement, ResultType, ResultDescription

On Organizational Maturity and Cybersecurity

Malicious compromise is a profitable industry with constant innovation. This has resulted in a repeating cycle of new cybersecurity risks generating new mitigations.
While the cycle is understood and mitigations may be available they are only useful if the organization knows they need them, have people who can use them, and have processes in place to ensure they are used. In other words, security tools are only useful if the organization has the maturity to ensure cybersecurity is important. In this post I go over the importance of understanding the maturity level of your cybersecurity preparedness.

In the not-so-distant past the prevailing concept for corporate cybersecurity was the application of the principle of least privilege, construction and maintenance of network firewall solutions, and stringent mail security. Cybersecurity has evolved as elevation of privilege and lateral attacks became common and it became obvious that networks were not the boundary they once were. Email became just one more SaaS service which must be secured. The common routine now is that bad people see opportunities to make money or do mischief which drives them to think of new ways to compromise environments. Malicious compromise is a profitable industry with constant innovation. This has resulted in a repeating cycle of new cybersecurity risks coming on the scene, security products and improvements needing to be identified and created, companies applying threat mitigations. In essence, innovation by bad people drives reactive security innovation by software companies and cybersecurity organizations. At this point, this cycle is generally understood by most as a fundamental truth of the state of security and the cloud.

Malicious compromise is a profitable industry with constant innovation.

While the cycle is understood by many there is a lot of work still needed to improve overall postures of organizations. The availability of security suites and tools are integral to that improvement, but security tools are only handy if the organization knows they need them, have people who can use them, and have processes in place to ensure they are used. In other words, security tools are only useful if the organization has the maturity to ensure cybersecurity is important. Assessing cybersecurity maturity is important whether you are gauging your environment, preparing your organization to do better, or considering a new group to work with.

I characterize cybersecurity maturity into three stages (or postures) an organization can be in. The security stages are Beginning, Forming, and Established. Let’s go over some characteristics of those stages in the lists below.

Beginning

  • This stage benefits most from pervasive trends in new technology and security initiatives, and typically involves a lot of discovery effort to see the state of the environment.
  • No established processes for identifying flaws/weaknesses in org and patching them to reduce likelihood of events.
  • No established processes, plans, or dedicated owners for incident handling if an incident were to occur.
  • Nonexistent to minimal planning for post-incident recovery to get the business back on its feet.
  • Small or no dedicated team devoted to security preparedness and operations.
  • No security specific tooling to alert the organization of potential problems.
  • Potentially unaware of compliance obligations related to security and preparedness.
  • Not meeting compliance obligations for data security and business continuity.
  • Security controls and recoverability is put into place reactively during or immediately following an event or events (i.e. add MFA following an event)

Forming

  • This stage is characterized by team building, product selection and use, and new initiatives to add security functionality and more secure service hygiene.
  • Building/establishing processes to mitigate risks.
  • Identified people and teams who are accountable for security preparedness.
  • Dedicated people who are accountable to respond to incidents.
  • Security teams typically formed from admins who are familiar with authentication and authorization technologies, application behavior, or IT operations.
  • Endpoint (host, network endpoint) detections in use or beginning to be used.
  • BYOD control in progress or established.
  • In discovery mode to determine organization and/or industry specific compliance needs.
  • Inventory efforts for line business resources and services (applications, data, automation) underway.
  • Prioritization and documentation of business-critical configurations and services underway.
  • Deployments of privileged identity and access management solutions started.
  • Reactive end user training on security “does” and “donts”.
  • Multi- factor authentication requirements are defined, and deployments are underway.
  • SIEM use and analysis is routine.
  • Beginning use of privileged access management and privileged identity management.

Established

  • Established organizations are continually working to maintain and enhance the security of the business with dedicated experts, tools, and strategies.
  • Security preparedness and incident response have established processes.
  • Business interrupting events have established plans and action owners.
  • Established code repositories and pipelines for security settings and resource configurations.
  • Risk mitigation planning
  • Deep understanding of the organizational compliance needs.
  • Compliance needs met or plans on how to meet them underway.
  • Uses advanced security aggregators for monitoring and risk mitigation. Microsoft Defender for Cloud, for example.
  • Identity sourcing is cloud driven, and applications have standard security criteria they must adhere to.
  • Governance reviews and automation for compliance.
  • Routine privileged access management and privileged identity management.
  • Established privileged access management processes and tooling where needed.
  • End user training campaigns to continually bolster security by education and testing.

Organizations in the Beginning stages are those which are most likely to experience a sudden and profound revelation on the need for better security. The cause of the revelation often being the result of an audit which didn’t go well, something malicious which interrupts business services, or perhaps a security breach. As of the time of this writing, this stage is typically comprised of small to medium sized businesses (1000 or less seats) as the result of many larger organizations getting wise to the problems already.

In the Forming stages, there are plans or the beginnings of plans on what it will take to establish sufficient visibility and control. There is an awareness and investment into security at executive levels, albeit the investments are newer. In this stage, things will begin mobilizing to make the business secure and, while it is possible that an event could still occur, if an event occurs the organization has dedicated responders who and tools to deal with it. Most enterprise organizations are at least in the Forming stage at this point-in many cases after unfortunate event(s) to prod things along.

Those who are in an Established stage still have risks related to business interruptions and compromise. This is the stage all should be striving to achieve. Having reached the Established stage doesn’t mean the work is done, instead it means that breaches or business interruptions are likely to occur, and if they do occur the blast radius will be reduced and recovery fast.

It should be expected to see the organization mature faster in some areas and slower in others. However, if things are going well there will be a linear progression toward greater maturity and thus greater security and resilience. There is not a defined path to cybersecurity maturity, nor is there a clear indicator when an organization is mature. These are things which are subjective and must be assessed by the organization and against the business priorities.

Since there is not a defined path it should be expected that it will take time and effort to gain security maturity in an organization. It won’t happen overnight. Think of it as steering a cruise ship-turns are wide and slow and take time. And, to push the analogy further, you have to have the right crew to have the ship do what you want.

What is the cybersecurity maturity of your organization? A better question is, where do you need to invest to get security where you need it to be?