Wednesday, January 02, 2019

Email report of costly activities in Azure

Azure seemingly has endless possibilities and options. Sometimes it is difficult to see the wood through the trees.  The capabilities for techies are mouthwatering but the cost control (or lack thereof) is a constant headache for managers or those who hold the purse strings.

It is very easy to accumulate spend if resources are not being managed in the correct way e.g. keeping VMs on when they are not required, having resources set to the incorrect scale or resources being created for testing and never being deleted.

There are some quite in depth solutions to cost control in Azure but at an organisation which isn't cloud native, you need to learn to walk before you can run.  Quota management for Enterprise Agreements can help, but this will just report on issues after the problem has occurred.

Whilst transitioning support for some Azure Subscriptions to an internal IT team, an IT manager asked me whether there was a way to simple see the resources created, updated or started in a time period.  He could use this to check where fluctuations in costs may start from.

This seemed a simple ask, but there wasn't a simple answer.  You can use Activity Logs and filters to only see Administrative tasks in the portal, but there are just so many of them, it is difficult to produce something valid.  You cannot even use the proper Operation name, only the localizedstring value.

Only 50....

Here is a list of all of the operations which can show up in Azure

Just a snippet

To go through this list and pick every item which might incur cost was way too much work.  Instead, I decided to look for activities which have the following keywords

Start (as in starting a VM)
Update (changing a VM or PaaS Scale)
Create (as in creation of new VM)
Deallocate (turning off a VM)
Change (similar to update, some resources use Change and other Update)
Write (again similar to Change and Update)

I decided to go down the PowerShell route and use get-AzureRMlog.

I created a PowerShell Script which would produce this in a CSV and email it to my colleague on a nightly basis for events in the last 24 hours.  There is the potential that this would create some false positives, but having it in CSV means they can filter what they want.

To take it step further and remove the dependence on having a scheduled task running on my PC every night, I decided to look at Azure PowerShell RunBook.

This was also my first time of running any PowerShell scripts from within Azure.  This requires creating an Automation Account and then creating an PowerShell RunBook.  I had some problems getting my CMDlets to work, so I had to ensure they were all up to date.

Below is the final script. If you want to re-purpose it, you will need an SMTP server.  You will need to ensure the SMTP credentials are stored in your Automation Account.  This allows you to keep your credentials private by not having them in plain text.  Of course, plain text will work, but don't do that....seriously!

Friday, November 09, 2018

Gather SPF Records For Multiple Domain Names

You can use the following script to gather the SPF records for multiple domains.  You just need to populate a TXT file named DomainNames.txt which exists in the same directory.  Below is the PowerShell:

$SPFRecords = @()
$Counter = 0
$myDir = Split-Path -Parent $MyInvocation.MyCommand.Path

$DomainNames = Get-Content $myDir\DomainNames.txt
$SPFOutput = $myDir + "\SPFRecordsResults.csv"

ForEach ($Domain in $DomainNames) {
    Write-Progress -Activity "Querying SPF Records" -Status "Processing $($Counter) of $($DomainNames.count)" -CurrentOperation $Domain -PercentComplete (($Counter / $DomainNames.count) * 100)

    $SPFRecord = Resolve-DNSName -Name $Domain -Type TXT | Where {$_.Strings -like "*spf1*"}

    If ($SPFRecord -ne $NULL)   {
        ForEach ($SPF in $SPFRecord) {

            $SPFItem = new-object PSObject -Property @{
                Domain = $SPF.Name
                SPFTXTRecord = $SPF | Select -ExpandProperty Strings

            $SPFRecords += $SPFItem

$SPFRecords | Select Domain,SPFTXTRecord | Export-CSV $SPFOutput -NoTypeInformation
Write-Host "Check results in" $SPFOutput

Thursday, July 26, 2018

View who has logged into Dynamics 365 - across many environments

A licencing requirement was raised at work this week where we needed to know who out of our Dynamics 365 CRM users were actually using the application.  These licences are really expensive and having many of them unused would cost the company lots of money over the year.

We did think of looking at audit tables in CRM, but unfortunately this wasn't an option due to the fact we have many Dynamics 365 environments.  A single licence will allow you access (if you have permission) to access as many sandbox environments as you wish.  This would mean collating data from many environments into one dataset.

We needed something more overarching.

So I reviewed Azure AD to see if I could view signins for a particular application and low and behold I could see all logins to Dynamics CRM Online.  All we would is visit this page, add the filters and download a CSV file.  The resultant CSV file would then need filtering to remove duplicate users, because we don't care about when and how many times they have logged in, just the fact they have at least once in the time period.

This was a great resource and gave us the information we needed.  Naturally, we wanted to take it further and to provide this programmatically with the least amount of user interaction as possible.

Enter Microsoft Azure AD Graph API

This API allows developers to get access to all sorts of data within Azure AD including application signins.

The first step is to setup an application in Azure AD.

Once this is complete, you should have an application ID, Access Key and your tenant name.

Next you can write a script to interact with the API.  There are samples around and you can even use the Graph Explorer to play around against a dummy tenant

I found the following script which seemed like a good place to start.

I then needed to filter it by application too.  I did this by editing the following line

This then produces a CSV with all the signins to Dynamics CRM Online for the last 30 days, but seeing that we only care if someone as logged on at all, we only want to see one entry per user. 

We added the following line at the bottom of the script which will reimport the CSV selecting only the UPN and then removing any lines which are not unique.  It will then export this to a final csv output.

Here is the full script