Managing Azure VM Diagnostics Data in Table Storage

Windows Azure Diagnostics (WAD) data is stored in table storage of a storage account. There are many types of tables to store different kind of Azure Windows VM diagnostics data and some of them are mentioned below:

  1. WADMetrics*: Used to store metrics data. It follows a proper naming convention whose details can be found here
  2. WADDiagnosticInfrastructureLogsTable: stores diagnostics infrastructure data
  3. WADWindowsEventLogsTable: Stores event logs data
  4. WADPerformanceCountersTable: stores performance counters data

The issue is we don’t have any mechanism to apply retention policy to these tables or any way to directly delete the data. With time, the data grow exponentially and eventually you pay for all of it. As of August 2019, you have to manually delete the old data/tables. Now there are two ways to do it:

  1. Delete the tables altogether: This is simple and Azure re-creates them to store more WAD data. But the issue is, if your table has millions of records (like mine) it might take few days to delete the table completely. And during this time, any attempt to re-create the table will throw an error. So if your VM is writing constantly to these tables, not a good option
  2. Partially delete the data: Even though it is cumbersome and required writing custom code, I found this better. Basically you can run this code on schedule to delete all the data that is more than a month old (or any time frame). But notice, that you pay for all the operations including deleting the data, even though the cost is very low.

So I decided to go with the approach of making a utility to delete all the data that was more than a month old from logs and performance counters table. But for metrics table, since these tables are created thrice per month, every month, hence I deleted the tables more than 2 months old.

Below is the code to achieve this in normal console application, which you can use as web job too. Install “WindowsAzure.Storage” for it to work.

Delete All Metrics Tables more than two months old:

You have to run the below code twice for PT1H and PT1M tables. Also if your storage account has older data than current year’s, run the code for previous years too by looping through all the years (instead of months)

Delete logs data more than x days old:

You have to run the below code for all the storage logs table (Infrastructure, Events, Performance etc.)

You can use the above to delete data incrementally on a schedule (via web job, azure function etc) or deleting all of the old data.

P.S. Cloud Table Client uses default exponential retry policy which you can change too. If you do not want to use any retry mechanism, make sure to set it to none. Good thing is, you can set retry policy to a particular action (like Delete) instead of table client in all. For this check implementing retry policies

Hope this helps! Happy coding!

References:

  1. Effective way of fetching diagnostics data from Windows Azure Diagnostics Table
  2. Understanding Windows Azure Diagnostics Costs And Some Ways To Control It
Advertisements

Working with C# MIP SDK – I

Recently I got a chance to work with the MIP SDK which is the unification of Microsoft’s classification, labeling, and protection services. Now is the time for data, and its security is paramount for everyone. Microsoft provides some amazing tools to protect our data tenant wide (if you are using O365 Online) or across your farms (in case of on-premises). If you are not aware of the core concepts, I would urge you to read the following links, before jumping to the SDKs.

  1. Azure Information Protection
  2. Office 365 Security and Compliance

The MIP SDK helps you to create custom third-party apps to create, apply, remove labels from your files. It might come handy in cases when you, as a super admin wants to access a protected file, apply labels based on some sensitivity information or even remove labels to check the data. These SDKs are built in C++ and MS provides a C# wrapper over it. There are some good samples provided by Microsoft and they cover a lot of things. But my requirement was bit different. I wanted create a multi-tenant SAAS application which can access these labels. Not only this, it was going to be like a daemon service with no user interaction and hence I needed app-only permissions. Therefore, I used client credential flow to implement this.

Coming back to the challenges I faced:

  1. It took me some time to set up my machine for development
  2. Not much documentation is available for C# and hence I took a lot of time to understand it
  3. The github samples and the test code in the documentation are different. I have to go with code snippets provided in documentation and figure out the rest
  4. Spent some time exploring all the permissions needed and how will they work together for various operations

Here are the assumptions I took from my side:

  1. You have gone through the concepts of File Profiles and File Engines
  2. You already have idea of registering multi-tenant Azure AD app and how it works
  3. You have a basic idea of OAuth and various authorization flow it uses, like client credentials flow

Now coming to the setup of your machine, the exhaustive list could be found here. I am covering the things I did to set up my Windows 10 environment.

  1. Created a demo account which has AIP enabled for trial
  2. Configured few sensitivity labels with various settings in O365 Security and Compliance Center
  3. Make sure your Windows 10 has version 1709 or later
  4. My machine does not have “Developer Mode” enabled but it works
  5. Installed Universal Windows Platform development in VS 2017, which took like 2 hours
  6. Downloaded and installed MIP SDK for Windows
  7. And you are done!!!!!

As our development environment is ready, lets fire up VS (I am using 2017)

  1. Create new .Net Web Application -> Choose MVC Template -> Change Authentication to “Work or School Accounts” -> “Cloud – Multiple Organizations”. Provide relevant domain and create a new project
  2. This will automatically create a registration in Azure AD with your project name. Login to portal -> Azure Active Directory -> App Registrations and search for the app you created
  3. For demo purpose I am using default localhost Redirect URI created by VS
  4. Navigate to “API permissions” tab and grant the following permissions. Make sure to click on “Grant admin consent” so that permissions are authorized by admin. Notice that all are application permission, except for MS Graph which is used for login
  5. Navigate to “Certificates and secrets” tab and create new client secret. Remember to copy and save it in web.config. This is not a secure practice and in production environment you should use Key Vault to store it securely
  6. Move to the project created in VS and update your web.config with following entries
  7. Install following Nuget in the project: Microsoft.InformationProtection.File and Microsoft.IdentityModel.Clients.ActiveDirectory
  8. That’s it!

You can find the complete source code at my github repo. Below are few issues I faced and how I solved it:

  1. Load Library failed for mip_dotnet.dll : To fix this, you need to provide the path of dll from the app folder. That’s why in HomeController line 41 you can see I have initialized MIP by providing the path
    MIP.Initialize(MipComponent.File, path);
  2. Also make sure to provide proper path while creating File Profile at line 54 as this is the place under which logging, telemetry, and other persistent state is stored.
    var profileSettings = new FileProfileSettings(mipPath, false, authDelegate, new ConsentDelegateImplementation(), appInfo, LogLevel.Trace);
  3. Make sure “ID Tokens” is enabled in AD registered app

I hope this helps in getting started with MIP SDK in C#. I have updated the code to remove protection from a file, you can try other similar operations.

In Part two we will go through using MIP SDK with SharePoint files. To make stuff more interesting, we will implement it in SharePoint Provider Hosted Add-In.

Happy Coding!

Auditing in MVC EF Project

Recently I worked on a requirement to audit the web application for CRUD operations. Yes, all of CRUD that means read operation too. I had a MVC web application which used Entity Framework DB first option and was using Azure AD Authentication. The complete set of requirements (mentioned below) was bit different than normal auditing.

  1. Log one row per table change instead of per property (column) change.
  2. Log for read operation too
  3. Generate a checksum and log it to along with the audit trail
  4. Apart from that, have to log table name, logged in user name, timestamp and other generic stuff

We decided to just store the new (updated) values in the audit trail instead of both old and new ones. If needed, we can get history of that object from audit table so not storing old values was not going to be an issue.

To achieve this, I decided to overwrite the SaveChanges method of DbContext. Entity Framework DB first creates partial classes for everything including the DbContext. I just used partial classes and overrode the SaveChanges method. Before jumping to the code, below is the approach I followed for all of the above points, respectively:

  1. I decided to create JSON of the updated row by using JSON.NET library. I could have used XML too but the issue with XML is, it fails on out of the box generated DB classes due to circular references and use of ICollection and virtual properties. To do this with XML, I would have to create POCO objects, which was just increasing the development time and effort. Hence I decided to use JSON.NET to create a valid json of updated row and store it in varchar(max) field of audit table in SQL. You can go ahead with XML too
  2. By overriding SaveChanges I can audit for create, update and delete (CUD) operation but not for read. Therefore, to simplify the process I just manually added audit rows for every call to functions where I fetch any table’s details. It was straightforward and easy but there was a catch. As I was adding audit row while reading an object, SaveChanges was called and hence for every read operation, there was an entry in audit table which denotes that an entry is added in the audit table. Basically the audit table was getting audited which I did not want. Hence, I have to exclude the “Audit” table manually from code
  3. This was easy to implement. I just combined the complete row using StringBuilder and generated checksum using MD5. You can use any other algorithm too. Besides that I converted the generated checksum to hex to store it in DB, you can use Base64 too
  4. I was using three tier architecture in my project with web layer, service layer and one data layer. Other things are straightforward but for user id I did not want to store complete email address for every row. That’s why, what I did was, I entered logged in user’s email id in database once someone logs in to the application for the first time and at the same time stored that table’s primary key as custom claim. I then read this claim value and use it for audit purpose
  5. Last thing, as I need to audit create operations too and their id is not generated until the call to SaveChanges, I have to collect create entities in different collection and audit them after an initial call to SaveChanges 

So below is the code to achieve all this: