Select Page

PowerBI, DAX and the Azure EA billing API

More fun this morning with grappling DAX to pull out the data I need.

We write multiple tags to Azure resources – from the resource group down to anything that takes them. Makes showback, accountability, support so much cleaner.

The tags are returned, in a non deterministic order, via the billing API – and look like this (data cleaned up), a list of tag/values. JSON-like ish kinda.

application“: “Shared SQL“,”project“: “Shared Database Engineering“,”costcenter“: “1234“,”itowner“: “Buckley, Martin“,”supportgroup“: “Database Engineering“,”businessowner“: “Buckley, Martin

I was looking at some DAX to pull this apart; PowerBI has a native “extract text between delimiters” function. It’s not going to build a tree of arbitary values; but I can now cleanly extract the tags I need to build this datamodel.

After I get this working, it might be time to pull all of the discrete DAX steps together to make it more efficient against a vast dataset.

Fun times on a Saturday.

Follow up to this. Extracting multiple pieces of insight from the tags – that drive KPIs for service improvement:

– resource groups with no costcenter tag (this is the cardinal sin – no costcenter and showback isn’t there, getting to health on this is easy)

– resource groups where itowner or businessowner don’t meet business rules (ICs can’t be owners)

– resource groups where the supportgroup is not valid (it doesn’t match a ServiceNow Assigment group)

Most of all – I can find everything tagged with “placeholder” or “set by policy” and sweep that broom through the tagging.

All in all – a good few hours of work.

Week 25 – 24 Aug – 30 Aug

Weekend – a handful of confirmed “re-infections” is a worrying sign. Will a vaccine even be effective?

Weekly reminder of the state of the State from The Seattle Times. Plateau – but not safe.

The state Department of Health has stopped releasing the number of tests that have come back negative. The agency, which initially cited technical difficulties, announced Aug. 12 it is changing its test-tracking methodology and won’t report testing totals or the state’s positivity rate again until its new data reporting system is operational. Also: On Aug. 28, the DOH announced it is ending the publication of COVID-19 death counts over weekends, starting Aug. 28-30. Regular publishing of death data will take place Mondays-Fridays, and the number of weekend deaths will be added to Monday and Tuesday reports.

Friday – across Europe travel restrictions are returning – similar to earlier in the year.

Thursday – “undeniable surge” in France, South Korea struggling to contain outbreaks. Second wave? Maybe. How these cases are suppressed is going to be vital learning.

Wednesday – the never ending flood of news is numbing. Trying to sustain empathy with 24M global cases, 5.8M US cases, 820K global deaths and 180k US deaths is hard. US politics are working to suppress the numbers and the news. Meanwhile Italy has surge of cases. Russia approves second vaccine.

Tuesday – Today India, Turkey, Spain again. US back to school is starting to prove a disaster.

Monday – it’s nearly September. Birmingham, UK continues towards lockdown. Boris says “go back to school”. We know how this ends. HK confirms re-infection.

The Seattle Times BBC The Guardian
24 Aug here here here
25 Aug here here here
26 Aug here here here
27 Aug here here here
28 Aug here here here
29 Aug here here here
30 Aug here here here

Week 24 – 17 Aug – 23 Aug

Weekend – was planning a late week camping trip; PNW National Parks are “over full, over flowing, over used” – there is no availability. Folk are heading to the mountains and lakes.

Sobering: 800k global deaths. 23 million cases. WHO says “pandemic over in two years”.

The Seattle Times weekly graphic:

The state Department of Health has stopped releasing the number of tests that have come back negative. The agency, which initially cited technical difficulties for the lack of data, announced Aug. 12 it is changing its test-tracking methodology and won’t report testing totals or the state’s positivity rate again until its new data reporting system is operational.

Friday – continued stories of “folk rushing back” to avoid lockdown and quarantine. It’s not the time to be heading on long haul trips for vacations tbh.

Thursday – Russia vaccine trials starting. Vast amounts of European cases. BBC saying “end of first wave” not a second wave.

WednesdayKing County reporting “lower ratio of positive tests” but spiking elsewhere.

Tuesday – more vaccines in clinical trials. Korea sees new cases.

Monday – reports of mutated Coronavirus strain. Will there be any chance of a vaccine? My guess is yearly variants like influenza. Much like the US, exam results are in disarray across the UK. Spain new cases spike.

here

The Seattle Times BBC The Guardian
17 Aug here here here
18 Aug here here here
19 Aug here here here
20 Aug here here here
21 Aug here here here
22 Aug here here
23 Aug here here here

Week 23 – 10 Aug – 16 Aug

Weekend – Weekend continues to report European second waves, US deaths.

The update from The Seattle Times for the week. Case count is trending down. Deaths still at low double digits. Too high for reopening.

The state Department of Health has stopped releasing the number of tests that have come back negative. The agency, which initially cited technical difficulties for the lack of data, announced Aug. 12 it is changing its test-tracking methodology and won’t report testing totals or the state’s positivity rate again until its new data reporting system is operational.

Friday – UK quarantine chaos. Europe second wave. 167k deaths in the US, 5.3M cases.

Thursday – US death toll highest since May.

Wednesday – New Zealand outbreak in Auckland. Will be interesting to see how the track and trace works for this – and how the cases emerged.

Tuesday – BBC have the daily updates on the front page again. Clearly second wave rolling through. Russia has a vaccine ready for test; unknown what the initial testing looks like.

Monday – work from home is going to be the normal for at least six months – so this is really a COVID news tracking page for me now. Week 23, it’s August, summer is passing, back to school is all virtual. I’ll keep aggregating the news stories – and try to remain above the political fray. As we approach six months I will try and reflect on what 2020 has looked like. BBC summary returned.

The Seattle Times BBC The Guardian
10 Aug here here here
11 Aug here here here
12 Aug here here here
13 Aug here here here
14 Aug here here here
15 Aug here here here
16 Aug here here here

COVID work from home – week 22 – 3 Aug – 9 Aug

Weekend – long read on the VIC, Australia outbreak. MLS season is restarting. Long read on Vietnam success and second wave. BBC summary is missing again (Saturday and Sunday). 5 million US cases; that’s 1.5% of the population confirmed with a positive test.

Weekly summary of Washington State from The Seattle Times.

Friday – good read on the US failure to contain and control coronavirus. Tests are down statewide and US wide. More UK lockdowns as a second wave rolls through.

Thursday – much discussion about track and trace, use of phone apps, national vs state. What a mess. US travel restrictions are lifted – but no one will let US travelers in.

Wednesday –

Tuesday – one bright spot – that the local positive test results are trending in the right direction. King County Public Health shows 1.6% positive cases in Issaquah between 13-27 July.

image

Monday – pretty much everyone I know who thought this would be over by “early summer” is now thing “early 2021 at the best”. Massive borrowing globally to prop up economies and keep people going. WHO say there may “never be a silver bullet” against COVID. As noted several weeks ago, this is going to become part of the annual flu/COVID shot discipline. UK lockdowns return. 18.2 MM cases confirmed, 691k deaths. US numbers may not be trustworthy.

The Seattle Times BBC The Guardian
3 Aug here here here
4 Aug here here here
5 Aug here here here
6 Aug here here here
7 Aug here here here
8 Aug here here
9 Aug here here

COVID work from home – week 21 – 27 July – 2 Aug

Weekend – Russia planning mass vaccinations. FA Cup with no crowd. Hong Kong delays elections. India as the newest hotspot. European cases continue to pick up. Looks like a second wave is really rolling in now across Europe.

A fresh look for the data from The Seattle Times; maybe six months of data needs it. The 14 day average death rate has picked up; cases are still climbing.

Friday – UK pauses coming out of lockdown/quarantine. 300k new cases globally today. UK beaches are “packed” – look for another bump in numbers from this mid-August. Good read on numbers in Europe.

Thursday – almost 4.5MM US cases; 152k deaths. I know more and more people who have “had” COVID-19 and are completely wiped out afterwards. Staying home, staying safe. England worst excess deaths in Europe between Feb – June. Shocking. Spain heading back to lockdown. US economy crashed 32% in Q2.

Wednesday – WA State positive cases still running >5%; target is 2% which shows measures are working. Graphic from The Seattle Times.

image

Tuesday – US daily cases – as reported by JHU dashboard – seems to have flattened. Whether this is because cases have flattened, or a result of the changes in reporting (CDC to HHS) remains to be seen. Pandemic economic costs are vast – including $320B for tourism alone. I do wonder what will be left after this. Spain, Iran, Italy – all reporting renewed surge of cases.

image

Monday – heading into August this week. US is still struggling with 4.2M cases; almost 150k deaths. Good “how to control it” thread from Andy Slavitt – unpopular, political U turns, acknowledgement and humility that it’s not a hoax. I don’t see any of this happening. WHO: ‘most severe health emergency WHO has faced’. New travel restrictions in Europe. More positive cases in the White House.

Seattle Times BBC The Guardian
27 Jul here here here
28 Jul here here here
29 Jul here here here
30 Jul here here here
31 Jul here here here
1 Aug here here here
2 Aug here here here

COVID work from home – week 20 – 20 July – 27 July

Weekend – European talk of second wave; is this poor isolation/masks or mutation? Quarantine measures being reintroduced. Seattle Times has a longer form article on “six months since the first US case”.

Usual Sunday graphic. WA in the steady, steady growth in number of cases. Must be close to going back to absolute lockdown.

Friday – US back above 1000 daily deaths. 15 days to go from 3 to 4 million cases. COVID isn’t going away. My guess is a combined flu/COVID shot every year going forward.

Thursday – Trump starts to realise COVID is real; cancels GOP convention. UK planning in the Spring was “complete failure”. US hits 4M cases. 915,000 new cases in last two weeks in US.

Wednesday – 15M global cases. Hotspots returning in Europe. More school districts in the area go online only.

Tuesday – schools are starting to move to online only for September. Less than six weeks away – and cases are still increasing. Trump says “wear a mask”..

Monday – yes – twenty weeks. Past the middle of July. Summer is passing. Projects I initiated in the early part of the year are coming to fruition and close.

Potential good news on a vaccine. 3.7MM cases in the US, 140k deaths. I know more and more people who have had COVID-19.

The Seattle Times BBC Guardian
20 Jul here here here
21 Jul here here here
22 Jul here here here
23 Jul here here here
24 Jul here here here
25 Jul here here here
26 Jul here here here

Mid-month updates

Shopping list:

  • Windows Server patching was done early (critical DNS issue)
  • SLES 15 SP1 to SLES 15 SP2
  • PHP 7.4 now as standard (woo!)
  • WordPress plugins, WordPress themes, site content scans