How to store audit data in Azure

Question

We are in the design phase to create an audit trail in an existing web application. The application runs on Windows Azure and uses the Azure SQL Database.

Audit logs should be filtered by the user or by type of object (for example, show all user actions or show all actions performed on an object).

We have to choose how to store data, should we use SQL Azure or use table storage? We prefer table storage (cheaper).

however, the problem with table memory is how to determine the partition key. We have several thousand clients (application users) in our SQL database, each of which is in its own tenant. Using the tenant ID as a section key is not specific enough, so we need to add something to the section key. So, the problem: given the filtering requirements, we can add the user ID to the partition key to make filtering easy for the user, or we can add the object ID to make filtering by object easy.

So, we see two possible solutions:
- use SQL Azure instead of storing tables
- use table storage and use two tables with different partition keys, which means that we duplicate all records

What ideas are best for our situation? Are there any other better solutions?

Answer 1

Documenture on Azure might be worth considering. https://azure.microsoft.com/en-us/documentation/articles/documentdb-use-cases/ You can have the audit trail stored in DocDB as JSON documents (user, activity, object fields and index by all fields)

Answer 2

Azure table storage is suitable for storing log data. Because Azure App services use Azure table storage to store diagnostic logs.

, PartitionKey -, RowKey - . , :

PartitionKey RowKey

:

, -

, https://azure.microsoft.com/en-us/documentation/articles/storage-table-design-guide/#overview Azure Table Storage.

, , .

Answer 3

- . . , . , /, (, ), ( , json) .

{
     PurchaseId: 9485,
     CustomerId: 138,
     StaffId: 509,
     ProductId: 707958,
     Quantity: 20
     Price: 31.99,
     Date: '2017-08-15 15:48:39'
 }

: Product_707958, Customer_138, Staff_509. : Purchase_9485. , , , , . , , ?

In addition, the idea that you think that you have several tenants, you can make a table name Tenant_[SomeId]. There are other problems that you will have to deal with, but in a way, this is another key to getting data without a circuit.