Adding a Timestamp to a dbachecks Data Source

Words: 656

Time to read: ~ 4 minutes

Adrenaline is a funny thing.

As a Database Administrator, I try and aim for three personality traits;

• Stoic under pressure,
• reliability, and
• confidentiality.

But last Tuesday, the 22nd January, I gave my first ever presentation up in Belfast and that first trait was tested!

Keeping a History

One of the questions that came out of that presentation was to do with Update-DbcPowerBiDataSource and whether or not we could maintain a history of those files.

If you want to know about the command, remember to use Get-Help!

Get-Help -Name Update-DbcPowerBiDataSource -Full

This seems like a relatively simple goal to accomplish. We add a timestamp and we have a record of when the file was created.

Should be simple

Here is where the adrenaline kicked in. If we check the syntax of that Update-DbcPowerBiDataSource command we can see that it has two parameters that we are particularly interested in.

• -FileName, and
• -WhatIf

Get-Command -Name Update-DbcPowerBiDataSource -Syntax

WhatIf

WhatIf is a parameter that gets added by the PowerShell engine when we satisfy certain conditions in our functions. Those conditions are out of scope of this blog post but they distill down to:

• Using [CmdletBinding()]
• Having a param() block,
• and the rest is in Joel Swallow’s ( blog | twitter ) post on ShouldProcess.

The WhatIf functionality was added to the latest release at the time of writing </humblebrag>

A new #dbachecks release 1.1.168

Update-Module dbachecks

Get-DbcReleaseNote -Latest

Thank you @SOZDBA and @jwmoss https://t.co/Z1GpzBHvEF pic.twitter.com/kK2GUnKhGY

— Rob Sewell (@sqldbawithbeard) January 30, 2019

FileName

-FileName is a parameter that allows you to specify the name of your JSON files.

With these two pieces of information, we can test out naming our JSON files

In the heat of the moment

When I tried to add a timestamp to the filename, the adrenaline kicked in and all I could think of was Get-Date. Unfortunately I had gotten into the habit lately of using sortable datetime.

Get-Date -Format 's'

However, that brings up an error message when you try to run it.

Invoke-DbcCheck -SqlInstance localhost -Check SuspectPages -Show None -PassThru |
    Update-DbcPowerBiDataSource -FileName "Test_$(Get-Date -Format 's').json"

Failure | The given path’s format is not supported.

Not wanting to waste time…

…I moved on but it’s something that annoyed me so, when I got a chance, I looked at it again.

It was because I wanted to see what was being created that I raised an issue on dbachecks GitHub about adding the -WhatIf parameter to the function and went about coding it.

Now that it’s in place I can add the -WhatIf parameter and see what is going to be created!

Invoke-DbcCheck -SqlInstance localhost -Check SuspectPages -Show None -PassThru |
    Update-DbcPowerBiDataSource -FileName "Test_$(Get-Date -Format 's').json" -WhatIf

Sight!

It’s more than likely those hyphen “-” and colons “:” that are in the file name. We can strip them using -replace to remove them.

# One per character
Invoke-DbcCheck -SqlInstance localhost -Check SuspectPages -Show None -PassThru |
    Update-DbcPowerBiDataSource -FileName "Test_$((Get-Date -Format 's') -replace ':' -replace '-').json" -WhatIf

# All together
Invoke-DbcCheck -SqlInstance localhost -Check SuspectPages -Show None -PassThru |
    Update-DbcPowerBiDataSource -FileName "Test_$((Get-Date -Format 's') -replace ':|-').json" -WhatIf

There’s an easier way though and it’s one of those tidbits that you find ages ago and then forget about:

Like adding dates to your file names? Got #PowerShell 5.0? Try "filename_$(Get-Date -Format FileDate).csv"

— Shane O'Neill (@SOZDBA) June 15, 2017

# Date only
Invoke-DbcCheck -SqlInstance localhost -Check SuspectPages -Show None -PassThru |
    Update-DbcPowerBiDataSource -FileName "Test_$(Get-Date -Format FileDate).json" -WhatIf

# Date and time
Invoke-DbcCheck -SqlInstance localhost -Check SuspectPages -Show None -PassThru |
    Update-DbcPowerBiDataSource -FileName "Test_$(Get-Date -Format FileDateTime).json" -WhatIf

Final words

What I realised when I was writing this out is that, when we’re using -WhatIf, we don’t check if the FileName is going to work or not.

We do in dbachecks, thanks to the fine work of Fred Weinmann ( blog | twitter ) and his PSFramework module, but this is really something that should be added to the command.

I highly encourage anyone to go to their issues page and log an issue!

Hit me up here or on twitter and I’ll try and help you code it as well 🙂