PowerShell – MS Azure database BLOB data viewer

May 31, 2020

Since everyone is going more into the Cloud, where data is stored MS Azure databases.

There is a bigger need to have the right tools to get control over it.

I had a need to view BLOB data stored in an MS Azure database.

I could find directly what I needed; so I created my own tools.

1. AutoIT Application to convert binary to a binary string to upload in the MS SQL BLOB Tables 

2. PowerShell Application to extract the BLOB data from the MS SQL database and visualize the content.

SOLUTION :

1. Create a BLOB Database table

First I had to make sure I could load the database with the BLOB data.

So I created a TEST database and Table that hold a BLOB field

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NULL,
	[BLOB] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test] ADD PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

The TEST table contains 3 fields : ID, Name and BLOB

2. Create a Binary String

Using AutoIT it is a piece of cake to create simple app that creates a binary string from any binary.

Whether it is an executable or a graphic or a word or excel file…

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>

; Create a constant variable in Local scope of the message to display in FileOpenDialog.
    Local Const $sMessage = "Select a single file of any type."

    ; Display an open dialog to select a file.
    Local $sFileOpenDialog = FileOpenDialog($sMessage, "C\", "All (*.*)", $FD_FILEMUSTEXIST)
    If @error Then
        ; Display the error message.
        MsgBox($MB_SYSTEMMODAL, "", "No file was selected.")

        ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
        FileChangeDir(@ScriptDir)
    Else
        ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
        FileChangeDir(@ScriptDir)

        ; Replace instances of "|" with @CRLF in the string returned by FileOpenDialog.
        $sFileOpenDialog = StringReplace($sFileOpenDialog, "|", @CRLF)

        ; Display the selected file.
        MsgBox($MB_SYSTEMMODAL, "", "You chose the following file:" & @CRLF & $sFileOpenDialog)
    EndIf


Global $hFile = FileOpen($sFileOpenDialog, 16)

If @error Then Exit

Global $bBinary = FileRead($hFile)
FileClose($hFile)


Global Const $MAX_LINESIZE = 4095 ; Max Line Size
Global $iNewLine, $j
Global $iChinkSize = 2000000 ; Max String Length
Global $sBinary


For $i = 1 To BinaryLen($bBinary) Step $iChinkSize

    $j += 1

    If 4*($j * $iChinkSize) > $MAX_LINESIZE - 129 Then
        $iNewLine = 1
    EndIf

    If $iNewLine Then
        $iNewLine = 0
        $j = 0
;~         $sBinary = StringTrimRight($sBinary, 5)
        $sBinary &= BinaryMid($bBinary, $i, $iChinkSize) & @CRLF & @CRLF
        ContinueLoop
    EndIf

    If $i = 1 Then
        $sBinary &= '$bBinary = "' & BinaryMid($bBinary, $i, $iChinkSize) & '" & _' & @CRLF
    Else
        $sBinary &= '       "' & StringTrimLeft(BinaryMid($bBinary, $i, $iChinkSize), 2) & '" & _' & @CRLF
    EndIf

Next

$sBinary = StringTrimRight($sBinary, 4)

ClipPut($sBinary)

ConsoleWrite("Binary String From " & $sFileOpenDialog & @CRLF & @CRLF & $sBinary & @CRLF & @CRLF & "Added to Clipboard !! " & @CRLF & @CRLF )

With a few lines of code you can get it in you clipboard or Notepad.

image

Next connect to your Azure database where the TEST table is residing.

image

And copy and paste the Binary String in the BLOBL field

3. PowerShell BLOB Viewer

So now that we have all BLOB Data in the Azure DB.

I need a tool to visualize it and potentially download it again as file.

I found a good starting point here.

Which I converted in an ONLINE BLOB Viewer without writing BLOB data to a local file.

All is handled “in memory” which is lightning fast Smile

image

I input the query to visualize the BLOB data I get it in a GUI for displaying.

And I can download it as a local file again Winking smile

The script logic connects to the MS Azure Database using a main GUI.

And selects the record with the BLOB field and passes the output to the VIEWER GUI.

   $w_form = 400 # form width
    $h_form = 250 # form height

    $b = [int]$img.Size.Width
    $h = [int]$img.Size.Height
    $b_thumb = $b
    $h_thumb = $h
    # portrait picture higher than 500px or landscape picture wider than 700px?
    if ( ($b -gt $h) -and ($b -gt 700) ) {
        $b_thumb = 700
        $h_thumb = [int](700 * $h / $b)
        $imgthumb = $img.GetThumbnailImage($b_thumb, $h_thumb, $null, 0) # create bitmap with 700px width
    }
    elseif ( ($b -le $h) -and ($h -gt 500) ) {
        $b_thumb = [int](500 * $b / $h)
        $h_thumb = 500
        $imgthumb = $img.GetThumbnailImage($b_thumb, $h_thumb, $null, 0) # create bitmap with 500px heigth
    }
    else {
        $imgthumb=$img
    }

    $form1 = New-Object Windows.Forms.Form

    $form1.Text = "Picture"
    $form1.Size = New-Object System.Drawing.Size($w_form,$h_form) # minimal size
    $form1.StartPosition = "CenterScreen"
    $form1.AutoSize = $True
    $form1.AutoSizeMode = "GrowOnly" # or "GrowAndShrink"
    $form1.Topmost = $true
    $form1.BringToFront()

    $font_normal = New-Object System.Drawing.Font("Tahoma",13,[Drawing.FontStyle]::Regular)
    $font_bold = New-Object System.Drawing.Font("Tahoma",16,[Drawing.FontStyle]::Bold)

    $PictureBox = New-Object Windows.Forms.PictureBox
    $PictureBox.Location = New-Object System.Drawing.Point(5,35)
    $PictureBox.Size = New-Object System.Drawing.Size($b_thumb, $h_thumb)
    $PictureBox.Image = $imgthumb;
    $form1.Controls.Add($PictureBox)

    $LabelDescription = New-Object Windows.Forms.Label
    $LabelDescription.Location = New-Object System.Drawing.Point(5,5)
    #$LabelDescription.Size = New-Object System.Drawing.Size(375,25)
    $LabelDescription.Font = $font_bold;
    $LabelDescription.Text = "original size: $b x $h, display size: $b_thumb x $h_thumb"
    $LabelDescription.AutoSize = $True
    $form1.Controls.Add($LabelDescription)

    $OKButton = New-Object System.Windows.Forms.Button
    $OKButton.Size = New-Object System.Drawing.Size(75,45)
    # OKButton centered under the picture
    $OKButton.Location = New-Object System.Drawing.Point( (($form1.Size.Width - $OKButton.Size.Width) / 2),(50+$h_thumb) )
    $OKButton.Text = "OK"
    $OKButton.Font = $font_bold
    $OKButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
    $form1.Controls.Add($OKButton)
    $form1.AcceptButton = $OKButton

    $form1.ShowDialog()

# Closing & disposing all objects
$form1.Dispose()

The full PowerShell code is extensive to post here.

Enjoy!


PowerShell–Azure ‘Az’ Module

January 1, 2019

A few weeks ago there was a new Module released for Azure called, Azure PowerShell ‘Az’ Module version 1.0

Az runs on Windows PowerShell 5.1 and PowerShell Core (cross-platform)

It can be used side-by-side the existing AzureRM Module.

But new features will only be released in the Az module.

To install the new module run this command using Admin priviliges.

Install -Module -Name Az

image

To see if the installation was successful run this command

Get-Module -ListAvailable "Az*"

image

To list all the commands run this command

Get-Command -Name *Az*

image

The connection method has been changed compared to what you are used to.

The Get-Credential cmdlet is replaced with Connect-AzAccount

Once executed you will see this reply

image

Go to the URL https://microsoft.com/devicelogin and fill in the code you got using this command

Connect-AzAccount

image

For more info see here

How to migrate scripts from AzureRM to Az see here

Enjoy !!


PowerShell – Monitor MS Azure Status

February 22, 2015

If you are using MS Azure or MS Office 365 you are depending on the uptime of all the MS Cloud services.

Well to monitor this there is the Azure Status site.

image

This site offers for each service a RSS subscription to monitor the status.

In this case there was an issue, so we can kick in Powershell to grab the RSS output.

Here we go

 

CLS

$hsg = Invoke-WebRequest  "http://azure.microsoft.com/en-us/status/feed/"

$hsg.Content

[xml]$ret = $hsg.Content.TrimStart("")

#$ret.rss.channel.item | Select-Object *

write-host ""



if($ret.rss.channel.item.category -eq $null){

        write-host $ret.rss.channel.title " On : "  $ret.rss.channel.pubDate
        write-host "All services are working properly"
        write-host ""
    }
Else
    {

    write-host $ret.rss.channel.title " On : "  $ret.rss.channel.pubDate
    Write-Host "Issue Category : " $ret.rss.channel.item.category
    write-host ""
    Write-Host "Details : " $ret.rss.channel.item.title

    $ret.rss.channel.item.description
    write-host ""

}

rv hsg, ret
image

As you can see the raw XML data is prefixed (deliberately or not?) by a few strange characters which we have to eliminate before PowerShell can dig it.

So the final output is like this.

image

So you can create a job to have this run at a frequency to check for uptime issues.

Enjoy!