Skip to content

AD FS Audit

September 20, 2014

Hello everyone! Hopefully you had a great summer, I had a good one, not even bothered to post anything on this blog. I do post almost daily on my travel blog, some cool photos from a few places I happen to visit, but this one does require a bit more thought about the technical stuff, not as easy as posting travel photos. Now summer is over, I’ll try to get back into the publishing mood with technical stuff as well.

Today we are going to review a few different ways we can do an audit and usage on the AD FS service.

By default when you install AD FS it does not provide any intelligence into how users use this service. For example if our AD FS is configured with couple Identity Providers (IdP) and a few Relying Parties (RP), and we want to know which users from what IdP are using what RP, and potentially what type of claims are being passed, there is no easy way to collect this data in manageable and usable way.

Let’s review a few different ways that we can do an audit on the AD FS.

1. Use AD FS Debug Tracing Log

See here how to enable it. While this log will collects the information about issued tokens, the way it is collected is not the most convenient way to analyze this data. It collects it in multiple events that would need to be used to extract relevant data. AD FS Debug Tracing logs are really designed for troubleshooting purposes and should not be used for any type of long term data collection.

2. Use Security Audit Logs 

See here how to enable it. We can enable Success Audit and Failure Audit in the AD FS Properties. This will log all activities in the Windows Security log. The key thing here to note is that it will log all information into the log. All information in the assertion will be logged into the Security log. If we have a farm of AD FS servers, it will log into the local log and there will be a need to push these logs to some central collection system and then figure out how to do some intelligent analysis against that data. As with the AD FS trace logs, each transaction is collected via multiple event entries, linked to each other by correlation ID.

Using Security Audit will allow to collect all necessary data, it has a few drawbacks:

  • Logs from each AD FS server will need to be pushed to a central collection system
  • To make any sense of the collected data, there will need to be a software that can comb through the events and pull out data that we are interested to analyze
  • As mentioned, these logs will contain all information in the security assertion, potentially including PII and other sensitive data.

So while it is possible to collect data and do an audit, making this type of collection on a long term basis might be fairly complicated to implement and manage.

3. Use SQL Attribute store

This is a custom way to collect information, but this will provide the most flexibility in what we can collect and then it will allow us to create custom reports against collected data in more streamlined way. The following diagram shows the concept of this solution:


1. User will attempt to access application and will be redirected to the AD FS (ADFS Server1 or 2) server.

2. They will be redirected to authenticate to their IDP (not shown) and come back to the AD FS to get a token for the application.

3. AD FS Server will process claims rules for the application and issue required claims for it. At the same time it will connect to the SQL Attribute Store and insert specified claims into the SQL DB.

4. SQL DB can be exposed to the administrator via different mechanisms, such as reporting web service or manual data transfer from DB into text format document.

The type of data collected and how it is collected is really up to you to design and implement. There are different ways to make this work. Let’s take a look at one of them. To use SQL Attribute store to collect data we’d need to perform the following configuration:

1. Create DB on the SQL server with a minimum of one table. You can use on-premises SQL or cloud based SQL. I use Azure SQL in my lab.

2. The table should have columns for the types of claims we want to collect. For example:


Create a stored procedure that will be used to inject data into this table:


Configure AD FS with SQL Attribute store and configure Claims rule on each RP to send claims to this attribute store. You’ll need to make sure that the account you use to connect to the SQL server have write permissions to the specified table.

Here is an example claims rule that works with the stored procedure and our table:

c1:[Type == “”%5D

&& c2:[Type == “”%5D

&& c3:[Type == “”%5D

&& c4:[Type == “”%5D

&& c5:[Type == “”%5D

&& c6:[Type == “”%5D

&& c7:[Type == “”%5D

&& c8:[Type == “”%5D

&& c9:[Type == “”%5D

&& c10:[Type == “”%5D

&& c11:[Type == “”%5D

=> add(store = “AuditLog”, types = (“”), query = “EXEC [dbo].[LogRequest] @RequestId = {0},@Inside = {1},@RpId = {2},@forwardIP = {3},@clientIP = {4},@AuthMethod = {5},@AuthInstance = {6},@AuthRef = {7}, @Upn = {8}, @AppName = {9}, @Company = {10}”, param = c1.Value, param = c2.Value, param = c3.Value, param = c4.Value, param = c5.Value, param = c6.Value, param = c7.Value, param = c8.Value, param = c9.Value, param = c10.Value, param = c11.Value);

For this rule to work you’d need to make sure that all eleven claim types are present in the pipeline. If one of them is missing then the rule will not trigger the “add” action and it will not run the stored procedure on the target SQL server.

If it is all properly configured, every time user getting a new token for the target application, AD FS will insert a new row into the SQL DB. Based on that entry we will have information about their IP address, their UPN, their Company, the application they have accessed, timestamp etc. You can add or remove required columns as needed.

Of course, the table can be designed differently as well, for example have only two columns: claim type and claim value.

Now we can do some targeted logging and do very intelligent reporting on who uses our applications, where they are coming from, their affiliation, the rate of access etc. There is really no limit to flexibility of what can be done via this logging mechanism.

  1. winne permalink

    thank you very much for your post.

    For using SQL Attribute store, is it possible to get fail login record?

    I think Claims rule will only be executed when user successfully login.


  2. Lucas permalink


    I am trying to get this working with ADFS on 2012 R2, this is the only claim rule I can get working:

    c:[Type == “”]
    => add(store = “AuditLog”, types = (“”), query = “EXEC [dbo].[LogRequest] @Upn = {0}”, param = c.Value);

    My server doesn’t like “%5D” or these URL’s:


    Any tips?

    Lucas Sawyer


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: