Part 1 and Part 2 of this blog focused on enabling you to create an asset audit report that shows when Media Assets were created, deleted and copied to media processing VMs. In Part 3, I will cover how you can enhance your report with data regarding when individual files were added to an asset. The good thing is that, this is possible without writing any code.
Asset Files in Storage
Asset Files are stored as blobs under the asset container in storage. As mentioned in Part 1 of this blog, the asset containers start with “asset-“. We will utilize this to import the asset and asset file data in to Excel using Excel Power Query. If you have never used Excel Power Query, you can download it from “Download Microsoft Power Query for Excel” web page. Once installed you can start Excel and you will see a tab called “POWER QUERY”. Click on that tab and then click on “From Other Sources” button and you will see a menu item called “From Windows Azure Blob Storage” as shown in the screenshot below.
Click on that menu item and then enter the Storage account name (associated with your Media Services account) and click OK. Enter the Account Key on the next screen and click OK. At this stage, you will see a “Navigator” pane on the right hand side as shown in the screenshot below.
Right click on the Storage account name in the “Navigator” pane and click “Edit”. A separate window will open with all the containers loaded in a column as shown below
Click on the drop-down button next to the “Name” column and then select “Text Filters” followed by “Begins With…” as shown below.
Enter “asset-“ for the “begins with” clause (as shown below) and click OK.
Now you will see only the asset containers loaded in the table. Next click on the button, next to the “Data” column and click OK. You will now see data for all the blobs under the container. Next, click on the button next to the Data.Attributes column and click OK. The end result will look something like below.
At this stage, you can click on “Apply & Close”. This will cause all the data to be loaded in an excel worksheet. You can now load the data in a Pivot Table and generate a view which looks as follows.
In other words, you now have a view that shows all the files that are in the asset along with the last modified timestamp for the asset.
Considerations
Finally, please note the following
- Asset file delete operations cannot be determined using the above. For that you will have to look for the DeleteBlob operation in the Storage logs. You can enhance the sample code provided in Part 1 of this blog to look for that operation and log an entry in the AssetAudit table.
- Many asset files may have the same last modified timestamp and so be mindful of that if you consider logging an entry for each asset file (with LastModified timestamp as the RowKey) in to the AssetAudit table.