Using LINQPad with Azure Table Storage

First, we need to load up LINQPad (I’m using V5) and creation a new connection. We will use the Azure Storage Driver for this.

2016-04-13_12-12-36

Then we will enter the required credentials in order to create the connection with our storage account.

2016-04-13_12-14-18

Once we’ve completed this process we can easily run C# expressions again our table using LINQ syntax. Let’s look at a few examples.

Viewing all entries:

2016-04-13_12-08-35

Viewing a specific entry by a simple where criteria:

2016-04-13_12-16-37

Now if we wanted to do something more complex, say delete all of the entries in a table or just a specific set of entries then we need to expand our process a bit. We can use the language type of “C# program” to build an actual C# program in LINQPad.

First, for this to work we need to add some DLL and Namespace references using the query properties.

Once we’ve added these references we can write a simple void Main() program and pretend like we are working with the table in C#.

In this example we are creating a storage account variable by using the CloudStorageAccount.Parse command to parse the connection string that Azure gives us.

Then we create a table client, pull in our table, and query it for all the entries with a specific partition key.

Finally, we run all the discovered queries through a loop and delete them.

Deleting an entry using a C# program layout:2016-04-13_12-09-02

Keep in mind this is a very simple example and does not take into account batches, continuation tokens, or any sort of API limits (of which there are a few you should be aware of).

Hope this helps get you started!

Advertisements

Power BI Desktop + SCSM 2012

I enjoy visualizing data. I believe it gives a different layer of insight to the data that we already have and bubbles up information we may have either felt like we knew already, or demonstrate to us something new entirely. It’s really a matter of bringing broad understanding to something that is otherwise foreign and nearly unusable.

For these exact reasons I’m really excited about the potential of Power BI and Power BI Desktop, but even more so the combination between the two.

Continue reading “Power BI Desktop + SCSM 2012”

Automation Series 1, Part 4: Azure Automation

Summary

If you’ve been following along with our series you now have a beautiful Power BI dashboard for your incidents that is using real data injected directly into Power BI from SCSM using Powershell and the Power BI API.

All that to say, we are missing one very important capability, and that’s how we update the data regularly without the SCSM admin having to log into the system in the middle of the night to refresh the data for the CIO currently in the UK. This is where Azure Automation comes in.

Continue reading “Automation Series 1, Part 4: Azure Automation”

Automation Series 1, Part 3: Power BI Reporting

Summary

In the third post of our automation series we will focus on report and dashboard creation from within the Power BI web app. The intent of our dashboard today will be to show only a few key metrics:

  1. Incident creation by date
  2. Count of Incidents by classification
  3. Count of Incidents by Status

Continue reading “Automation Series 1, Part 3: Power BI Reporting”

Automation Series 1, Part 2: Powershell and Power BI

Summary

In the second part of our series we will be focusing on Power BI and how to access the API through Powershell, as well as the benefits of using the API. We will also discuss a few of the limitations that exist within the API currently that could limit what you want to do with the API.

This is part 2 in the series, if you are looking for the first post you can find it here:

Automation Series 1, Part 1: Service Manager Dashboards in Power BI through Azure Automation

Continue reading “Automation Series 1, Part 2: Powershell and Power BI”

Automation Series 1, Part 1: Service Manager Dashboards in Power BI through Azure Automation

Summary

This blog is the first in a series that I’ll be writing, starting today and continuing through the next few weeks, that will focus on how IT shops can easily automate the process of dashboard creation within Power BI and then present this information to their users in a very mobile friendly fashion through the Power BI app. Going a step further, we will use Microsoft’s Azure Automation in the cloud to run on-premise runbooks to collect the data from Service Manager and upload the data into Power BI using Powershell and the Power BI API.

Continue reading “Automation Series 1, Part 1: Service Manager Dashboards in Power BI through Azure Automation”