KQL – Subscription Name

I needed to pull some information from the Azure Activity Table for a Sentinel Report. The issue that I came across was the SubscriptionID are in the logs, but the display name is not. Here is a quick solution to show you both the SubscriptionID and Subscription Name. Enough of that, let’s get into this.

First so we are all on the same page, below is quick print out of one of my subscriptions. You will notice it has the ID and also the Name when you pull it from CLI.

If you go into Sentinel and do a basic query you can see you only get SubscriptionID

In order to address this issue, I decided to utilize Watchlist. Take a look at the article if you want to know more how to create them. My watchlist looks like the following. I have 2 columns one for the ID and one to reference the name.

Here is my KQL I ended up with.

| project tostring(subscriptionid), subscriptionname
| join AzureActivity on $left.subscriptionid == $right.SubscriptionId
| project subscriptionname, SubscriptionId

The one issue encountered was the subscriptionid. The CSV when it was uploaded as a watchlist the value was converted GUID type. When trying to do my join it would not allow me to due to type mismatch. My solution was to utilize the “tostring” and covert that value into a string. After getting over that hurdle this was the final result

Now you can see the Subscription Name and also the SubscriptionID. There might be other methods to accomplish this, but it does fit the need and enriches the information coming out of Sentinel.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Connecting to %s