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
I am sorry how small this is….

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:

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

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"
Path’s format not supported

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
Them there bad characters!

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
Replace methods

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

Like…2.5 years ago
# 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
time with milliseconds and all

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 🙂

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

One thought on “Adding a Timestamp to a dbachecks Data Source”

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s