KQL was developed to take advantage of the power of the cloud through clustering and compute. Using this capability, KQL is designed as a well-performing tool to help surface critical data quickly. This a big part of why it works so well and outshines many other query languages like it. KQL was built for the cloud and to be used against large data sets.
Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL’s: databases, tables, and columns.
The query we write in KQL is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate. Kusto queries are made of one or more query statements.
Here is an example query:
StormEvents | where StartTime between (datetime(2007-11-01) .. datetime(2007-12-01)) | where State == "FLORIDA" | count
I will show you below how to run this code and also any code that I put in these blog posts – for FREE!!
Where did I first discover Kusto Query Language? In Azure using Log Analytics – as I was collecting diagnostic data and needed to analyse it:
Where can you discover and try out Kusto Query Language? Well Microsoft have some free demo sites you can use:
KQL is named after Jacques Cousteau. Even today, you can find evidence of this in the Azure Monitor Docs. If you go to the datatable operator page right now, you’ll still find a reference to him in an example that lists his date of birth, the date he entered the naval academy, when he published his first book entitled “The Silent World: A Story of Undersea Discovery and Adventure,” and the date when he passed.
So the puns about deep diving into data, exploring the depths of data to find and present information – basically we are going to be explorers in a sea of data…
This blog post is about a new query language that I have learnt and I really think you need to learn it too. Especially if you are doing ANYTHING in Azure.
Basically about 3.5 years ago I was building some things in Azure and I realised that I need to get diagnostics and metric information – from all the data that I was capturing.I found that Log Analytics had everything I needed from an infrastructure side…
Then I worked with some awesome frontend developer who were using KQL and Application Insights to get telemetry information about their web services hosted in Azure.
I started learning KQL and realised that I needed to start sharing the brilliance of it.
I have felt so strongly about this that I’ve done a number of talks and even videos on it.
The idea behind it is pretty much the same:
Azure is the leading platform for many companies, and to manage your databases and other infrastructure you need to have insights into what is happening. You need to be able to query the Azure platform, to not only understand your infrastructure but also to leverage monitoring and analytics to react to operational changes occurring in it. This session will introduce you to the Kusto Query Language (KQL) which will allow you to query a variety of Azure resources.
This blog post is about YAML pipelines in Azure DevOps.
I had a repo called InfrastructureAsCode for a client. I have been transitioning them to use YAML for their build pipelines in Azure DevOps – because now we can source control the build itself.
If you do not know what a YAML pipeline is in Azure DevOps check it out here:
I had created a pipeline using YAML – which was called InfrastructureAsCode as the YAMP file was in the root directory.
However I wanted to move it into a folder .\InfrastructureAsCode\pipelines\… and run the YAML file from there – as I would have a non-prod and PROD version of them (as the schedule was different for each).
That went well – but I didn’t like the name InfrastructureAsCode as my pipeline name. And it got worse when I added the PROD version of the YAML file – I now had InfrastuctureAsCode(1). That’s even uglier.
So I needed to rename the pipeline – but how do you do this with YAML??
Well – click your pipeline and edit it and then up in the right hand corner is the 3 dots – click that and choose Triggers:
This post is about code which is an online editor you can use with cloudshell.
I live in the Azure platform all day (almost) every day. What this means is I need to be able to run scripts from just about any device – there are times where I will be scaling a Hyperscale database at 10:30pm from my phone….
The best part of cloudshell is the online editor – which you can access within cloudshell by clicking on the {} in the toolbar:
Or you can access it by typing code . in the commandline:
which brings up the online editor:
You can now browse the directory structure and click on a script that you might have written before. In the example below I have clicked on scalePREPROD.sh to edit it:
This blog post is about a situation where you are capitalising an argument in bash and you get the following error:
bash: ${state^^}: bad substitution
In this example I was capitalising a variable called state to STATE. After some quick googling I found that the reason I am getting this error is not because of my bad coding – but because I might not be using a version of bash greater than 4.
When I checked the version I am using on my mac it was:
$ bash --version GNU bash, version 3.2.57(1)-release (x86_64-apple-darwin20) Copyright (C) 2007 Free Software Foundation, Inc.
and running the same command in Visual Studio showed the same – that I was running 3.2.57 (??!!??)
Which seemed quite old – that copyright of 2007 gives it away. I am running Big Sur – version 11.1 of macOS and from some more googling it appears there is some history around Apple and Bash and GPL licenses and <yawn>…… here are some links http://meta.ath0.com/2012/02/05/apples-great-gpl-purge/ and
And I realised that many macOS users probably don’t know is that they are using an outdated version of Bash shell.
How to upgrade Bash on macOS.
For macOS you should use homebrew to install things:
To verify the installation, you can check that you now have two versions of Bash on your system:
$ which -a bash /usr/local/bin/bash /bin/bash
The first one is the new version, and the second one is the old version:
$ /usr/local/bin/bash --version GNU bash, version 5.1.4(1)-release (x86_64-apple-darwin20.2.0) Copyright (C) 2020 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> This is free software; you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law.
$ /bin/bash --version GNU bash, version 3.2.57(1)-release (x86_64-apple-darwin20.2.0) Copyright (C) 2007 Free Software Foundation, Inc.
Since the directory of the new version (/usr/local/bin) comes by default before the directory of the old version (/bin) in the PATH variable, the version used when you just type bash is the new one:
$ bash –version GNU bash, version 5.1.4(1)-release (x86_64-apple-darwin20.2.0) Copyright (C) 2020 Free Software Foundation, Inc.
We now need to whitelist our shell as Unix systems restrict this, so to use the newly installed Bash shell as the default shell, it must be able to act as a login shell. So we will add it to the /etc/shells file. We will edit this file as the root user:
$ sudo vim /etc/shells
and enter in your admin password.
You will now be in vim editor:
Use the i key to go into INSERT mode, paste in /usr/local/bin/bash down the bottom then hit th ESC key and type
:wq!
using the vim editor
Which will write and save our changes.
Set Default Shell
Because /bin/bash is still set as the default shell – you will still be using version 3.2.57. To change this to the new shell, execute the following command:
$ chsh -s /usr/local/bin/bash
The default shell for your user is now set to the new version of Bash. If you close and reopen the terminal window, you should now be using the new version already:
$ echo "$BASH_VERSION"
5.1.4(1)-release
The chsh command changes the default shell only for the user who executes the command. If you want to change the default shell for other users too, so run the following (which will ask for your admin password):
$ sudo chsh -s /usr/local/bin/bash
All done – right? So I went back into Visual Code and ran the original script – but still had the issue!!
Updating Visual Code to use the new Bash version
I found you need to go to Preferences and change Settings:
In the search field type in bash
click on “Edit in settings.json” for Osx and enter in “/usr/local/bin/bash” as shown below
Exit the Terminal and a new bash session will have
This blog post relates to where you might be doing scale operations of your app services or VMs in Azure and get the following error after doing quite a few scale up and down operations.
The exact error I received was:
Failed to update configuration for 'Api'. {"Code":"Conflict","Message":"You have exceeded the maximum amount of scale changes within the past hour(56 changes and limit is 40). Please retry later.","Target":null,"Details":[{"Message":"You have exceeded the maximum amount of scale changes within the past hour(56 changes and limit is 40). Please retry later."},{"Code":"Conflict"},{"ErrorEntity":{"ExtendedCode":"03024","MessageTemplate":"You have exceeded the maximum amount of scale changes within the past hour({0} changes and limit is {1}). Please retry later.","Parameters":["56","40"],"Code":"Conflict","Message":"You have exceeded the maximum amount of scale changes within the past hour(56 changes and limit is 40). Please retry later."}}],"Innererror":null}
Where I was scaling my app service plan called Api and went past some arbitrary limit.
It is hard to find the exact limits within Azure for app scale operations but by a process of elimination I have determined:
SKU
Limit
Premium/PremiumV2/Isolated
40
Standard
20
Basic
15
Unfortunately there isn’t much we can do here – you just have to wait until your hour is up….
This blog post is about a situation where writing an IF THEN ELSE statement with variables fails with something like
./scalePREPROD.sh: line 26: [[1: command not found
This was in a script called scalePREPROD that scaled app services if they were not a certain value – so I am checking to see IF they are a value of 15 and if they are not then scale up to 15.
TL;DR
You need a space in the IF statement when variables are used.
The code looked something like this:
for i in "${appServiceArray[@]}"; do appcapacity=<bash query to check app services> if [[$appcapacity -ne $scaleAPPWorkersUp]]; then <stuff to do> else <echo something back that we're all good> fi done;
So in the example above – I wanted to see if for my array of 6 app services ${appServiceArray[@]} any were not equal to a value of $scaleAPPWorkersUp.
When I was appointed to serve as a Director at Large for the PASS Organization a year ago, it was with great passion, excitement and enthusiasm. I had some great ideas about how I could help globalize, diversify and improve our wonderful community that I’ve been a member of since 2013.
The PASS Community has provided me with friends and the opportunity to connect, share and learn. I wanted to serve our community but have realised sadly that I cannot.
It is with a heavy heart that I am delivering this message today. On Monday 7th December 2020 (Pacific Time) I resigned from the PASS Board.
I had considered resigning from the board back in July this year, but wanted to continue and try and serve our community by continuing on the board. I wanted to bring change – I campaigned on it when running for the board this year. Ultimately I could not bring the change that I feel we needed and so this is the reason I am stepping down. To let others bring what they feel our community needs.
I want to thank the community for the support they have given me. This was a very hard decision to make, but I’m a give it a 100% person and I tried.
At the recent PASS Virtual Summit I and the other candidates took part in a Ask Me Anything (AMA) session – where the community posed us questions.
It was a great experience for me – of the 3 days it ran I had time to participate in 2 of them. Current board activities and client requirements didn’t allow me to take part in the final session.
It was on reflection of the AMA session that I thought some key things need to be looked at.
Namely:
— how the community can be engaged in the future of PASS
— how we can align as a global team
— what the event(s) on 2021 might look like
Community Engagement The Board needs to look to the leaders within our community. We need their help – because to go forward we all need to be on the same page. We need to work cohesively to save the community platform that is PASS. This cannot be a top down approach. We need your help. I have worked in many situations where people have said “it can’t be done” or people are siloed in their approach. One of the reasons I got into DevOps was I realised early on that we needed a goal that could align disparate groups of people. It worked wonders in my corporate career and I’ve excelled in it whilst running a consultancy company. In short – we need to listen to our community, we need to work with that community and take the positive actions and measure the not-so positive actions. Measure them against the goal of PASS – a platform that helps the community learn, grow and connect.
Alignment The alignment of a global team is related to the above action item. We need to work actively with our Regional Mentors – because they represent our regions. They should be actively working with their community leaders and actively telling us the board what works and doesn’t. We need this alignment, we need this communication and we need people who are passionate about making a difference for their community. I appreciate these are volunteer positions, that it can take a bit of personal time – however – time can be allocated if something is important enough and I found helping a community to be somewhere people felt safe, could learn and become a better person. I was recognised by the community in 2019 for being relentless when it came to helping others:
In my life I have a pragmatic approach to disparity – one of the my early mantras was “make stuff go” and when placed in situations where groups of people wouldn’t agree on action items – I’d measure our opinions on whether it made stuff go or not. It is a very simple thing to do, the hard part is actually listening and distilling down to the fundamental problems at hand. Not up-talking or complicating things for our own ego…. That is what is needed for both items 1 & 2 above. I can help here, I have a proven corporate record in doing that.
Basically this is about running out of $$… We need to take a pragmatic approach to the survival of PASS – and events may be smaller and more regional in 2021 – purely because of the covid situation we are experiencing right now. In saying that the Virtual Summit that we’ve just had was better than I personally thought it would be. I’m interested to see what the number/percentages look like for attendee experience. Certainly compared to other events I’ve been on it was better, yes there are improvements to be made/had. There were aspects that I didn’t like – but we have a voice and constructive feedback is far better than just saying something is bad. It will be interesting to see what the balance sheet looks like when all things are done – I feel that some hard decisions have to be made to ensure the longevity of the platform known as PASS. Some of those decisions will involve how we engage with the community, how we manage events as well as what strategic decisions we can make around content delivery. Not easy at all, but my passion for our community means I’m ready to stand up and fight for what PASS really needs to be,
PASS has changed my life – dramatically. I used to be a mediocre DBA. I thought locally rather than globally and the personal growth I have experienced by meeting passionate mentors has made me a far better person. It has resulted in my own mentoring of people and also having a wider sense of community – not just a data platform community – but helping others:
The Blog of The Hybrid DBA 