Skip to content

posts🔗

  • 4 min read

Getting Started with Stream Analytics

Resources

Resources
If you want a schema reference for the json Application Insights produces // Azure Application Insights Data Model // Microsoft Docs
If you want to visualize last 90 days of App Insight Data with Grafana // Monitor Azure services and applications using Grafana // Microsoft Docs

The Scenario

Application insights is integrated into your application and is sending the results to Azure. In my case, it was blob storage. This can compromise your entire insights history.

Application Insights has some nice options to visualize data, Grafana included among them. However, the data retention as of this time is still set to 90 days. This means historical reporting is limited, and you'll need to utilize Continuous Export in the Application Insights settings to stream out the content into blob storage to

The process

  1. Install Visual Studio Azure Plugin
  2. Initialize a new Stream Analytics project in Visual Studio
  3. Import some test data
  4. (Optional) If using SQL Server as storage for stream analytics then design the schema.
  5. Write your stream analytics sql, aka asql.
  6. Debug and confirm you are happy with this.
  7. Submit job to Azure (stream from now, or stream and backfill)
  8. Configure Grafana or PowerBI to connect to your data and make management happy with pretty graphs.

Install Visual Studio Azure Plugin

I don't think this would have been a feasible learning process without having run this through Visual Studio, as the web portal doesn't provide such a smooth experience. Highly recommend using Visual Studio for this part.

Learning the ropes through the web interface can be helpful, but if you are exploring the data parsing you need a way to debug and test the results without waiting minutes to simply have a job start. In addition, you need a way to see the parsed results from test data to ensure you are happy with the results.

New Stream Analytics Project

stream analytics project

Setup test data

Grab some blob exports from your Azure storage and sample a few of the earliest and the latest of your json, placing into a single json file. Put this in your solution folder called inputs through Windows Explorer. After you've done this, right click on the input file contained in your project and select Add Local Input. This local input is what you'll use to debug and test without having to wait for the cloud job. You'll be able to preview the content in Visual Studio just like when you run SQL Queries and review the results in the grid.

Design SQL Schema

Unique constraints create an index. If you use a unique constraint, you need to be aware of the following info to avoid errors.

When you configure Azure SQL database as output to a Stream Analytics job, it bulk inserts records into the destination table. In general, Azure stream analytics guarantees at least once delivery to the output sink, one can still achieve exactly-once delivery to SQL output when SQL table has a unique constraint defined. Once unique key constraints are set up on the SQL table, and there are duplicate records being inserted into SQL table, Azure Stream Analytics removes the duplicate record. Common issues in Stream Analytics and steps to troubleshoot Using the warning above, create any unique constraints with the following syntax to avoid issues.

create table dbo.Example (
...
,constraint uq_TableName_internal_id_dimension_name
          unique ( internal_id, dimension_name ) with (IGNORE_DUP_KEY  = on)

Stream Analytics Query

warning "Design Considerations" Pay attention to the limits and also to the fact you aren't writing pure T-SQL in the asaql file. It's a much more limited analytics syntax that requires you to simplify some things you might do in TSQL. It does not support all TSQL features. Stream Analytics Query Language Reference

Take a look at the query examples on how to use cross apply and into to quickly create Sql Server tables.

Backfilling Historical Data

When you start the job, the default start job date can be changed. Use custom date and then provide it the oldest data of your data. For me this correctly initialized the historical import, resulting in a long running job that populated all the historical data from 2017 and on.

Configure Grafana or PowerBI

Initially I started with Power BI. However, I found out that Grafana 5.1 > has data source plugins for Azure and Application insights, along with dashboard to get you started. I've written on Grafana and InfluxDB in the past and am huge fan of Grafana. I'd highly suggest you explore that, as it's free, while publishing to a workspace with PowerBI can require a subscription, that might not be included in your current MSDN or Office 365 membership. YMMV.

Filter Syntax

Filter Syntax Reference

I had to search to find details on the filtering but ended up finding the right syntax for doing partial match searches in the Filter Syntax Reference linked above. This also provides direct links to their ApiExplorer which allows testing and constructing api queries to confirm your syntax.

If you had a custom metric you were grouping by that was customEvent\name then the filter to match something like a save action could be:

startswith(customEvent/name, 'Save')

This would match the custom metrics you had saved that might provide more granularity that you'd normally have to specify like:

customEvent/Name eq 'Save(Customer)'
customEvent/Name eq 'Save(Me)'
customEvent/Name eq 'Save(Time)'
customEvent/Name eq 'Save(Tacos)'

Wrap-up

I only did this one project so unfortunately I don't have exhaustive notes this. However, some of the filter syntax and links were helpful to get me jump started on this and hopefully they'll be useful to anyone trying to get up and running like I had too.

  • 1 min read

setting default open with on macOS

It should be easy to pick a default program to open a file. On macOS, I was surprised at how poor the design was. Seriously, how is this intuitive? Open With > Set this as default. Apparently this only set it for an individual file. This means, every different csv file required me to do this again.

Instead, I had to Get Info > Unlock settings and then choose the default Open With setting, and further select Use this application to open all documents like this.

I enjoy most of my development experience with macOS.

Don't try and tell me that it is the pinnacle of usability though, some of this stuff is just quirky and over complicated. In what world, should my default behavior be set on a specific file and not the the file type?

  • 2 min read

Assume a role with AWS PowerShell Tools

Assume A Role

I've had some issues in the past working with AWS.Tools PowerShell SDK and correctly assuming credentials.

By default, most of the time it was easier to use a dedicated IAM credential setup for the purpose.

However, as I've wanted to run some scripts across multiple accounts, the need to simplify by assuming a role has been more important.

It's also a better practice than having to manage multiple key rotations in all accounts.

First, as I've had the need to work with more tooling, I'm not using the SDK encrypted json file.

Instead, I'm leveraging the ~/.aws/credentials profile in the standard ini format to ensure my tooling (docker included) can pull credentials correctly.

Configure your file in the standard format.

Setup a [default] profile in your credentials manually or through Initialize-AWSDefaultConfiguration -ProfileName 'my-source-profile-name' -Region 'us-east-1' -ProfileLocation ~/.aws/credentials.

If you don't set this, you'll need to modify the examples provided to include the source profilename.

{{< gist sheldonhull "e73dc7689be62dc7e8946d4ab948728b" "aws-cred-example" >}}

Next, ensure you provide the correct Account Number for the role you are trying to assume, while the MFA number is going to come from the "home" account you setup. For the Invoke-Generate, I use a handy little generator from Install-Module NameIt -Scope LocalUser -Confirm:$false.

{{< gist sheldonhull "e73dc7689be62dc7e8946d4ab948728b" "aws-sts-assume-role-example.ps1" >}}

Bonus: Use Visual Studio Code Snippets and drop this in your snippet file to quickly configure your credentials in a script with minimal fuss. 🎉

{{< gist sheldonhull "e73dc7689be62dc7e8946d4ab948728b" "vscode-snippet.json" >}}

I think the key area I've missed in the past was providing the mfa and token in my call, or setting up this correctly in the configuration file.

Temporary Credentials

In the case of needing to generate a temporary credential, say for an environment variable based run outside of the SDK tooling, this might also provide something useful.

It's one example of further reducing risk vectors by only providing a time-limited credential to a tool you might be using (can limit to a smaller time-frame).

{{< gist sheldonhull "e73dc7689be62dc7e8946d4ab948728b" "generate-temporary-credentials.ps1" >}}

AWS-Vault

Soon to come, using aws-vault to improve the security of your AWS sdk credentials further by simplifying role assumption and temporary sessions.

I've not ironed out exactly how to deal with some issues with using this great session tool when jumping between various tools such as PowerShell, python, docker, and more, so for now, I'm not able to provide all the insight. Hopefully, I'll add more detail to leveraging this once I get things ironed out.

Leave a comment if this helped you out or if anything was confusing so I can make sure to improve a quick start like this for others. 🌮

  • 1 min read

Go R1 Day 1

Day 1 of 100

progress

  • Cloned learning-go-with-tests to ensure a nice structured start, even though I've already done hello-world
  • Setup fresh gotools updates - Ran golangci-lint through docker to ensure improved linting options ready for further tests
  • Fixed default debug template in vscode to use workspacefolder instead of file directory. Strange that it defaulted to the wrong path.
  • 7 min read

Compiling a Custom Provider and Including for Terraform Cloud

Assumptions

  • You are familiar with the basics of setting up Go and can run basic Go commands like go build and go install and don't need much guidance on that specific part.
  • You have a good familiarity with Terraform and the concept of providers.
  • You need to include a custom provider which isn't included in the current registry (or perhaps you've geeked out and modified one yourself 😁).
  • You want to run things in Terraform Enterprise ☁.

Terraform Cloud

For Terraform Cloud, bundling is not allowed.

Instead, the "legacy" way of running this is to include the plugin directly in the directory that Terraform will be invoked on with terraform.d/plugins/linux_amd64 as the path containing the provider. See discussion: Using Community Providers With Terraform Cloud.

Part of my current walk-through (primarily using terraform-bundle) is relevant only for Terraform Enterprise, not Terraform Cloud. I missed the ending documentation section on the custom bundle requiring installation and not being supported in Terraform Cloud.

For the directions below, disregard the bundle aspect for Terraform Cloud, and instead focus on building the custom provider and including in the project directory as shown.

If you are willing to explore Atlantis, I bet something can be done with custom providers in there.

After following the custom provider build steps below, create a .terraformignore file in your project directory and put in the config below.

.terraform
.git
.gtm
*.tfstate

With a backend like below, I was actually able to get terraform cloud to run the custom provider and return the plan.

terraform {
  backend "remote" {
    hostname     = "app.terraform.io"
    organization = "myorg"

    workspaces {
      name = "terraform-artifactory"
    }
  }
}

If you get an error the first time you run this, see the troubleshooting section at the end.

Custom Providers Bundling

As of the time of this post, to include a custom provider with Terraform Enterprise, you need to create a custom terraform bundle bundle to package up the terraform package and any desired custom plugins.

This terraform bundle includes the terraform program, as well as any range of other providers that you want to include for running in the remote terraform workspace.

Before you go down this route, you should make sure that the Terraform Registry doesn't already include the provider you need.

Source Of Truth

For the most up to date directions, you can go through these directions:

Compiling the custom provider

In this example, I'm working with a provider for Jfrog Artifactory, which IMO has an atrocious management experience on the web. By compiling this custom provider, my goal was to provide a clean user, repository, and group management experience.

You need to target the platform for Go in the build step, as the Terraform Enterprise environment expects Linux and amd64 as the target.

git clone https://github.com/atlassian/terraform-provider-artifactory.git
git install .

# I use pwsh even on macOS 😁
#$ENV:GOOS='linux'
#$ENV:GOARCH='amd64'
#go build

#See troubleshooting section below. More robust than simple go build. This simplifies things and will generate all binaries for you

goreleaser build --snapshot

Get Stuff Setup

git clone https://github.com/hashicorp/terraform.git

To checkout a specific tagged version (recommended):

git checkout tags/<tag_name>

Quick little #devhack... Use Git Graph in Visual Studio Code to make working with busy repositories much easier. Yes, I'm no Vim magician. Sometimes a little visual help is much better than trying to do it all in cli. #heresy

Use Git Graph to Visually Navigate A Busy Repo and Checkout a Tagged Commit

Next, you'll want to install and validate your install worked. go install ensures that dependencies are downloaded, so once again the magic of Go wins the day. If you flip to a new tagged version, make sure to rerun the install so you have the correct version of the tooling available.

go install .
go install ./tools/terraform-bundle

For some reason, I had issues with the path picking it up in my current session, so for expediency, I just ran the next steps with the fully qualified path: /Users/sheldonhull/go/bin/terraform-bundle instead of terraform-bundle directly.

Grab an example of the configuration hcl file for the terraform-bundler from the link mentioned above. Then you can create this in the project directory or qualify it to a subdirectory if you want to save various bundle configuration files.

mkdir mybundles
New-Item -Path ./mybundles/terraform-bundle.hcl -ItemType File

Here is a trimmed down example config with what worked for me. See the bottom troubleshooting section for more details on why I did not adhere to the documented config from the README.

terraform {
  version = "0.12.28"
}
providers {
 artifactory = ["0.0.0"]
}

We need to include this plugin in a specific location for the bundle tool to do it's magic.

Also ensure you follow the naming convention for a provider.

To be recognized as a valid plugin, the file must have a name of the form terraform-provider-<NAME>

This is where PowerShell shines, and it's easy to make this path without issue using Join-Path in a way that also is fully cross-platform with macOS, Linux, or Windows (pick your poison)

try
{
    $version = terraform-bundle --version *>&1
    if ($version -notmatch '\d{1}[.]\d{2}[.]\d{1,2}') { throw "failed to run terraform bundle: $($_.Exception.Message)" }
}
catch
{
    Write-Host "Adding go bin/path to path so terraform-bundle can be resolved"
    $ENV:PATH += "${ENV:HOME}/go/bin/:$PATH"
}


$SOURCEHOST     ='github.com'  # any arbitrary value allowed per docs
$SOURCENAMESPACE='atlassian'    # any arbitrary value allowed per docs
$NAME           ='artifactory'
$OS             ='linux'
$ARCH           ='amd64'
$VERSION        = '0.0.0'
$PluginPath     = Join-Path plugins $SOURCEHOST $SOURCENAMESPACE $NAME $VERSION "${OS}_${ARCH}"
$null           = New-Item -Path $PluginPath -ItemType Directory -Force
Remove-Item -LiteralPath ./plugins -Recurse -Confirm:$false
New-Item plugins -ItemType directory -Force -ErrorAction silentlycontinue
Copy-Item ${ENV:HOME}/git/github/terraform-provider-artifactory/dist/terraform-provider-artifactory_linux_amd64/terraform-provider-artifactory -Destination (Join-Path plugins "terraform-provider-artifactory") -Force
terraform-bundle package -os=linux -arch=amd64 --plugin-dir ./plugins ./jfrog-bundle.hcl

Now to bundle this up

terraform-bundle package -os=linux -arch=amd64 jfrog-bundle.hcl

Troubleshooting

Problems Parsing the bundle configuration file

I ran into some issues with it parsing the configuration file as soon as I added the custom plugin. It reported unknown type for string *ast.ObjectType.

Here's what I looked at:

In the project, there is a tools/terraform-bundle/config.go that is responsible for parsing the hcl file.

First, the configuration looks correct in taking a string slice for the versions, and the source is a normal string.

type TerraformConfig struct {
    Version discovery.VersionStr `hcl:"version"`
}

type ProviderConfig struct {
    Versions []string `hcl:"versions"`
    Source   string   `hcl:"source"`
}

This seems to mean the configuration syntax of meets with the schema required by the configuration code.

terraform {
    version = "0.12.28"
}
providers {
    artifactory = {
        versions = ["0.1"]
        source = "example.org/myorg/artifactory"
    }
}

It looks like the configuration syntax from the example is a bit different from what is being successfully parsed. Instead of using the fully designated schema, I adjusted it to artifactory = ["0.0.0"] and it succeeded in parsing the configuration.

The help terraform-bundle package --help also provides an example indicating to just use the simple syntax and let it look for the provider in the default directory of ./plugins.

Failed to resolve artifactory provider 0.1: no provider exists with the given name

This next piece was a bit trickier to figure out. Once I enabled $ENV:TF_LOG = 'TRACE' I found some output showing an issue with the version of the provider.

2020/07/14 16:12:51 [WARN] found legacy provider "terraform-provider-artifactory"
plugin: artifactory (0.0.0)
- Resolving "artifactory" provider (0.1)...
- Checking for provider plugin on https://releases.hashicorp.com...

I went back to the provider project and installed goreleaser using: brew install goreleaser/tap/goreleaser which provided me the same tool to build the various packages for this provider. Build the provider by running goreleaser build --snapshot.

After reviewing the help in more detail, the following CLI content conflicts with the main README.md, so I had to experiment with various output methods and finally... success! 🎉

The message did provide a warning: found legacy provider "terraform-provider-artifactory-v2.0.0".

I tested and found it matched the local provider with 0.0.0 by running terraform providers and seeing the output:

2020/07/14 16:49:52 [TRACE] Meta.Backend: backend *remote.Remote supports operations
.
└── provider.artifactory

However, what to bundle correctly required simplifying the output to no nested directories.

What Actually Worked In Plugin Directory Was a simple flat directory

The output of the bundle was successful with

Fetching Terraform 0.12.28 core package...
2020/07/14 16:54:34 [TRACE] HTTP client HEAD request to https://releases.hashicorp.com/terraform/0.12.28/terraform_0.12.28_linux_amd64.zip
2020/07/14 16:54:35 [TRACE] HTTP client GET request to https://releases.hashicorp.com/terraform/0.12.28/terraform_0.12.28_linux_amd64.zip
Fetching 3rd party plugins in directory: ./plugins
2020/07/14 16:54:37 [DEBUG] checking for provider in "./plugins"
2020/07/14 16:54:37 [WARN] found legacy provider "terraform-provider-artifactory"
plugin: artifactory (0.0.0)
- Resolving "artifactory" provider (0.0.0)...
Creating terraform_0.12.28-bundle2020071421_linux_amd64.zip ...
All done!

Terraform Cloud Fails with terraform.tfstate detected

Since the local plugins seem to generate some tfstate for mapping the local plugin directory, I ensure you have a .terraformignore file in the root of your directory per the notes I provided at the beginning.

Terraform Enterprise detected a terraform.tfstate file in your working
directory: <VCS-REPO>/terraform.tfstate

Once I added the .terraformignore the apparent conflict with uploading a local tfstate on the plugins was resolved and the plan succeeded.

  • 1 min read

2020-07-13T14:00:00-05:00

PowerShell has some functionality that can help in pre-processing data by grouping and aggregating. If you are using ImportExcel this might be useful to pre-aggregate the results prior to an Excel workbook. If you are working with PowerShell and needing to do some quick measurement of objects, maybe this will be useful.

{{< gist sheldonhull "af7e3355953a2f6533c813d9ca220a7d" >}}

  • 1 min read

2020-07-06T12:00:00-05:00

Windows users, nice little win for making the great git-town tool even more accessible. 🎉

  • install scoop: iwr -useb get.scoop.sh | iex
  • scoop install git-town

This is one of my favorite tools for git workflow. If you use GitHub flow to keep a simple workflow, it's a life saver.

For example, on a branch and need to start a new bit of work to keep your commits atomic? switch to master > stash pending work > pull latest with rebase > create new branch > push branch to remote OR git town hack feat/tacos. Need to squash commits and ship to master? git town ship What about prune all those remote branches that have been merged? git town prune-branches This is one of my favorite git productivity tools (and it's written in Go 👍 so cross platform and fast)

  • 1 min read

2020-06-29T18:29:03+00:00

Visual Studio Code has a pretty great way to browse through themes by just selecting installed themes and using arrow keys to preview without apply. However, browsing those themes isn't quite so good, as you need to install to see the changes.

Ran across Vscode Themes which provides a really nice experience if you feel like changing things up on your editor.

  • 2 min read

Azure Data Studio SQL Notebook for Diagnostic Queries

Diagnostic Queries

Glenn Berry has long been known for producing the definitive diagnostic query set for various SQL Server versions. Between his amazing work and my favorite Brent Ozar First Responder Kit, you are pretty much set.

One of the things that can be painful though about running diagnostic queries is that it's a lot of small individual queries that you want to run and ideally save the results for review.

You can do this with dbatools and running queries individually, which is actually what I did a while back for a special support tool that dynamically split those queries into files and exported to xml for later import and review.

Azure Data Studio

I'm a big fan of Azure Data Studio and as I'm not primarily focused right now on SQL Server administration, the feature-set perfectly fits my needs for running queries, doing some basic server administration, and overall just having a lighter weight solution to SSMS. Since I migrated to macOS, this provides me a nice cross-platform tool that I can use on Windows or macOS.

A great feature that has been continually improving is the Azure Data Studio notebooks. Not only can you run T-SQL notebooks now, but also PowerShell and python using whatever kernel you desire.

As part of this, you get the benefits of a nice intuitive structure to ad-hoc queries you might want to provide to someone with details on what it means and more. Additionally, the results are cached as part of the JSON so if you save the file and come back later you can review all the results that were pulled (and as a plus they render in GitHub viewer too).

Diagnostic Queries + Azure Data Studio + dbatools = 🎉

To merge the power of all 3 technologies, you can use dbatools to export the diagnostic queries for a targeted SQL server version as an Azure Data Studio Notebook. Pretty freaking cool.

To get started on this just make sure you have the latest dbatools: Install-Module dbatools -confirm:$false

Then generate a new Azure Data Studio Notebook like this:

# This will create the notebook in whatever location you currently are in
$Version = 2017
New-DbaDiagnosticAdsNotebook -Path "DiagnosticNotebook${Version}.ipynb" -TargetVersion $Version

Open up this new notebook and enjoy the result! To make reading easier, you can issue the command to "collapse all cells" and the queries will be minimized allowing you to read through all the query options.

Note that even the description of the queries is provided in the notebook, providing insight on the purpose of the query.

{{< admonition type="warning" title="Warning" >}}

As always, make sure you are careful before just running all queries by default against a production server. Some queries take heavy resources and might not be appropriate to run in the middle of a production workflow.

{{< /admonition >}}

Shows the diagnostic query view in Azure Data Studio

  • 1 min read

2020-06-24T16:03:49+00:00

After using Windows versions of launchers that tried to emulate Alfred, I finally took the plunge and bought Alfred's Powerpack. The buyer remorse of paying > $60 on a "shortcut" app hasn't really set in as I'm seeing such a wealth of great features that I think the gains as a developer and shortcut aficionado will be well worth the cost in my workflow. So far, highly recommend. The best part for me is the easy plugging in of bash/pwsh scripts to execute on demand for quick productivity tasks without having to navigate and open it up in the terminal.

Alfred Workflows