PowerShell – BEMCLI – BackupExec Gui Crashes on DotNet 4.8

December 23, 2020

When we upgraded to the latest DotBet 4.8 version, we encountered a painful event.

Where all our servers that ran BackupExec 2012 / 2014 / … suddenly start crashing when opening the GUI to check the backup status Sad smile

image

If this drama was not enough we could in no way uninstall the DotNet 4.8 version anymore Sad smile

And since the BackUp GUI crashed, we could not restore the servers to a previous date !!

So the scripting knowlegde was here to save our day ! Smile

SOLUTION :

When you look at the BackupExec installation you will find a command line interface that is actually a PowerShell Module called BEMCLI

You can find an extensive help file in the Root Folder called BEMCLI_en.chm.

image

image

To RESTORE a file from a backupset use this Powershell command :


import-module BEMCLI

$Server = Get-BEAgentServer

Submit-BEFileSystemRestoreJob -FileSystemSelection C:\Temp -AgentServer $Server -RedirectToPath C:\1 | Wait-BEJob

image

If you add the Wait-BEJob command it will let you know when the job was done

image

Or you can add this parameter to trigger an e-mail notification when the job is done

-NotificationRecipientList yourname@company.com

Being a hardcore scripter I created an small application using AutoIT to list the JobHistory, for my colleagues to monitor the backups each day.

I used the .Net Common Language Runtime CLR Framework for this

See here if you want to know more about this.

image

image

It is based on this command :

import-module BEMCLI

$Server = Get-BEAgentServer

Get-BEAgentServer $Server | Get-BEJobHistory | Sort-Object -Descending 

If you want to list the JOBS executed use this command :

import-module BEMCLI

Get-BEJOb

image

If you want to view the ALERTS  use this command :

import-module BEMCLI

Get-BEAlert

image

Enjoy!



.NET 4.8 Upgrade – BackupExec 2012 – 2015 not compatible !

December 6, 2020

We had an old server running using a BackupExec 2012 to offload backups to tapes.

But I noticed that after upgrading .Net from 4.7 to 4.8 that the GUI did not start up anymore

It crashed telling that there is a problem signature with MSCORLIB 4.8 !

image

image

Hmmm, BackupExec 2012 and 2015 not compatible with .NET 4.8 anymore Sad smile

Problem weI saw that when I tried to uninstall .NET 4.8 and revert back to the previous settings, it was NOT POSSIBLE ?

And since we did not have access to the BE GUI interface I could NOT RESTORE a backup either ? Sad smile Sad smile

The good news was that the backups and scheduled jobs still ran fine Smile

Solution :

It not a real solution but rather a workaround Winking smile

BE also has a command line management tool

image

And I discovered that the tool is actually a PowerShell Module !

This is good enough for us scripters Smile

image

Run this command to a list of options :

get-command | out-gridview

image

So now knowing this, I created a small application using AutoIT .NET Common Language Runtime (CLR) Framework

https://www.autoitscript.com/forum/topic/188158-net-common-language-runtime-clr-framework/#comments

Took me less than 30 minutes to build this application :

image

This way we have again a GUI interface based on .NET Powershell  to check the backup status !


Power BI – Get Cube VERTIPAQ Metrix in PowerShell

October 14, 2020

I needed to compare 2 versions of a Power BI model using the same dataset.

Because the result in both versions where different.

I needed to get a quick view on the METADATA of the each TABLE & ROWS

As I made already some blog post about how to retrieve METADATA of a CUBE using DMV queries.

See here for more info.

We are going to use the same technique.

Alternatively you can uses the easy way and run the queries in DAXSTUDIO as well.

Nevertheless you can export the VERTIPAQ Metrix easily but it in a VPAX file format.

image

And use other tools to analyse.

But since we are hardcore scripters Winking smile we create our own tools !

SOLUTION :

You can use the DMV queries to get the tables # of records

First build your connection string :

CLS

$Folder = ""
$File = ""
$Port = ""
$Catalog = ""

$Folder = Get-ChildItem -Path "$env:LOCALAPPDATA\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces*\Data" -Recurse -Directory -Force -ErrorAction SilentlyContinue | Select-Object FullName

If( ($Folder | Measure-Object | %{$_.Count}) -gt 0 ){ 

$File = Get-Childitem -Path $Folder.FullName -Include msmdsrv.port.txt -Recurse

$Port = Get-Content $File -Encoding Unicode

    If ($Port -is [array]){

        $Port = $Port[0]
        }
    Else
        {
            $Port
        }

$File = Get-Childitem -Path $Folder.FullName -Include *.db -Recurse

$Catalog = $File.Name.Split(".")

$Catalog = $Catalog[0]

$ConnectionString = "Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost:$Port;Initial Catalog=$Catalog"
$ConnectionString

}

You can see the PORT and CONNECTION STRING as a result

image

You need this in to connect to the local Power BI – SSAS database.

CLS
 
$Port = "62020"
$Catalog = "e2606a88-b1fb-4b18-82ea-aab29f69a73d"
 
$Query ="SELECT * FROM `$SYSTEM.DISCOVER_STORAGE_TABLES 
                  WHERE 
                        LEFT([TABLE_ID],2) <> 'H$' 

                  AND   LEFT([TABLE_ID],2) <> 'R$'"
        
 
$connectionString = "Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost:$Port;Initial Catalog=$Catalog"
  
$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
 
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet
$adapter.Fill($dataset)
 
$dataset.Tables[0] | Out-GridView
    
$connection.Close()

image

The ROWCOUNT is the number of records per TABLE.

As you have it in the GRID you can FILTER and COPY and PASTE directly in Excel

Or you can change the script to output directly to CSV file as well. Depends on what suits you best.

You can verify the result in DaxStudio as well running the DMV Query

image

In the Metrix Analysis you will see a column called RI Violation ?

This caught my attention. I found the explanation of this value here.

image

It means you have blank values in a table and this should never be the case in a cube !!

More info on Tabular Performance Analysis and the Vertipaq engine, see here

You can also use the Power BI Helper as well do optimize the DATA MODEL.

Example :

One of my models seemed to have a  BI DIRECTIONAL Relationship ?

image

This is not according to the best practices, so its needs fixing.

I hope this can help you all to get better insight in the data model and performance tuning.

The Power BI Helper is also very useful for documenting your PBI project.

Comments are welcome !

Enjoy !


PowerShell – GUI ListView ICONS Example

June 7, 2020

On internet there are not a lot of functional examples of a GUI ListView including ICONS.

Use cases are getting data from a MS Azure Database that contains BLOB images…

image

So this could be a good starting point …


SOLUTION :

CLS

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = "ListView ICONS"
$objForm.Height = 400
$objForm.Width = 500

$imageList = new-Object System.Windows.Forms.ImageList 
$imageList.ImageSize = New-Object System.Drawing.Size(30,30) # Size of the pictures

$bitm1=[System.Drawing.Image]::FromFile("C:\Temp\Pi.ico")
$bitm2=[System.Drawing.Image]::FromFile("C:\Temp\linux.ico")
$bitm3=[System.Drawing.Image]::FromFile("C:\Temp\Debian.ico")

$imageList.Images.Add("PI",$bitm1) 
$imageList.Images.Add("Linux",$bitm2)
$imageList.Images.Add("Debian",$bitm3)

$listView1 = New-Object System.Windows.Forms.ListView

$listView1.View = 'Details'
$listView1.Height = 200
$listView1.Width = 400


$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 12
$System_Drawing_Point.Y = 12

$listView1.Location = $System_Drawing_Point
$listView1.Name = "listView1"

$listView1.Columns.Add('Item',90)| Out-Null
$listView1.Columns.Add('First Name',90)| Out-Null
$listView1.Columns.Add('Last Name',120)| Out-Null

$listView1.SmallImageList = $imageList
$listView1.Items.Add("PI",0) #for first image need index 0

$listView1.SmallImageList = $imageList
$listView1.Items.Add("Linux",1) #for second image need index 1

$listView1.SmallImageList = $imageList
$listView1.Items.Add("Debian",2) #for second image need index 2
$objForm.Controls.add($listView1)

$objForm.ShowDialog()| Out-Null

$listView1.Dispose()
$imageList.Dispose()
$objForm.Dispose()

Enjoy !


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 Core – Running GUI Scripts

May 11, 2020

DotNet Core is the new standard … But how compatible is this compared to the .NET

I Tested a few GUI scripts using PowerShell Core 7.0

 

image

Most of them where running fine.

But again not all of them did work a without errors …

 

Use this command to test you PS Scripts :

 

pwsh - file "yourfile.ps1"

 

I am also using the new MS Terminal to test the PowerShell Core scripts :

image

 

image

 

image

Looks good ! Smile

Even if this scrips uses a custom .Net Assembly (being the Charting Assembly)

 

Next Barcode example script works fine as well Thumbs up

image

 

But unfortunately not all scripts are working flawlessly Winking smile

image

Bottom line is that the compatibility is generally OK, but not quite perfect …

But the main goal is to make it cross platform compatible as possible.

Running the PowerShell Core on WSL (Windows 10 Linux Subsystem)

image

It is not yet quite a Party Sad smile

Makes sense because the Windows Forms Assembly is either not available on my machine or it is not yet cross platform ?

Of course I first need to install the .Net CORE on WSL Ubuntu

I will make a separate blog post about this.

This is where you need to start

 

Enjoy !

 

 

 

 

 

 


Office365 – How to Block Self Service Purchase Apps

December 5, 2019

Microsoft is enabling all O365 user to use self service Apps.

This means everyone on your tenant can start purchasing certain apps by default …

If you want disable this behaviour than you need to use PowerShell.

SOLUTION :

1. Download and install the PS MSCommerce Module

Install-Module -Name MSCommerce 

image

2. Next import the module and connect

Import-Module -Name MSCommerce

Connect-MSCommerce

image

3. Run these commands to see the status

Get-MSCommercePolicy -PolicyId AllowSelfServicePurchase 

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase 

image

4. To disable 1 or all the products run this commands

Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0KP0P -Enabled $False  
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0L3PB -Enabled $False  
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0KP0N -Enabled $False 

5. To disable all in Batch run this command

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase | `

Where { $_.PolicyValue -eq “Enabled”} | `

forEach { 

Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId $_.ProductID -Enabled $false  }

image

You can check the status again :

image

Enjoy !


PowerShell Core – Power BI Gateway Management CmdLets

November 6, 2019

Keep in mind that these cmdlets are PowerShell CORE only !

First you need to check the availability of the PS CORE version on your system.

I had  6.1 preview 3 installed

image

So I opened the Cmd line using Admin privileges

Next started PWSH.exe

Next I run this command :

Install-Module -Name DataGateway

image

Next Check the available cmdlets

Get-Command -Module DataGateway*

image

Next I ran this command

Import-Module DataGateway

image

But it says my version of PS Core needs to be minimum 6.2.2 Sad smile

So first get an upgrade from here :

https://github.com/PowerShell/PowerShell

image

After upgrading your can check the version using

$PSVersionTable

image

Next run these commands again :

import-module DataGateway
import-module DataGateway.profile

image

No errors now Smile

Run this command to login :

Login-DataGateway

It opens the browser and return this message

image

image

Next run this command to get your Cluster ID

Get-DataGatewayCluster

Next check the Gateway Status like this

Get-DataGatewayClusterStatus

image

So far so good Smile 

Enjoy !!


PowerShell – Monitor CPU Cores Temperatures

October 19, 2019

In order to Monitor the Temperature of your CPU Cores it is best to make use of OpenHardwareMonitorLib

 

image

You can download it here.

 

This nice application has a .Net Library that you can use to access the Hardware Sensors Data

It also exposes the values to WMI when the GUI is started, but that is not so convenient for monitoring

 

SOLUTION :

# Needs admin privileges and the .NET OpenHardwareMonitorLib.dll

#Requires -RunAsAdministrator

CLS

Add-Type -Path "C:\OpenHardwareMonitor\OpenHardwareMonitorLib.dll"

$Comp = New-Object -TypeName OpenHardwareMonitor.Hardware.Computer

$Comp.Open()

$Comp.CPUEnabled = $true

$Comp.RAMEnabled = $true

$Comp.MainboardEnabled = $true

$Comp.FanControllerEnabled = $true

$Comp.GPUEnabled = $true

$Comp.HDDEnabled = $true

ForEach ($HW in $Comp.Hardware) {

$HW.Update()
    $hw.HardwareType.ToString() + ' - ' + $hw.name.ToString()

    If ( $hw.HardwareType -eq "CPU"){
        ForEach ($Sensor in $HW.Sensors) {

        If ($Sensor.SensorType -eq "Temperature"){
            
            $Sensor.Name + ' - Temp : ' + $Sensor.Value.ToString() + ' C - Min. : ' + $Sensor.Min.ToString() + ' C - Max : ' + $Sensor.Max.ToString() + ' C'
        }
      }
    }
   
    # $hw.Sensors
    $hw.SubHardware
}
$Comp.Close()

 

image

 

If you add an Email Notifications when it reaches the MAX values, you have a nice Monitoring System Smile

Enjoy !


PowerShell – How to access Exchange Online Resource Mailbox Calendar

October 18, 2019

We are using the EVOKO Liso booking system, that is linked to Office 365 – Exchange Online

Where each meeting room has a tablet to book the rooms or equipment

image

Once booked on the Tablet it shows in Outlook, or the other way around.

If booked in Outlook it shows on the Tablet hanging on the wall beside the meeting room Smile

image

Fiarly easy to use and manage ..

But how to get access to the Exchange Online Resource Mailbox Resource Calendar

Or have a quick access to all bookings now and in the future Sad smile

SOLUTION :

First you need to check if the Resource Mailbox has the status PublishEnabled

Run this Powershell command and check the property : PublishedCalendarUrl


Get-MailboxCalendarFolder -Identity ResourceMailboxEmail@YourCompany.com:\calendar

image

If this is blank you need to set the Recourse Mailbox to PublishEnabled

Run this command to change it :

Set-MailboxCalendarFolder -Identity ResourceMailboxEmail@YourCompany.com:\calendar -PublishEnabled $true

image

Run the Get-MailboxCalendarFolder command again to check the result and copy the URL

You can also configure it using the OWA web interface


image

You can also set the permissions here, where you define the view permissions

image

Don’t forget the set the correct timezone / date and time format … for each resource calendar !

image

TIP :

The URL exposed is not 100% correct in order to use it, you need to change it to HTTPS://….

At least now you can have a nice DAY / WEEK or MONTH overview  Smile

image

Keep in mind that it can take up to 24 Hrs to sync all of the intermediate changes in the calendar.

Exchange Online Calendar in SharePoint Online :

This is not supported see here  Sad smile

When you configure it all looks OK but there is an error message shown on top.

– Outlook Web Access URL : See above MailboxCalendarFolder : https://outlook.office365.com/owa/calendar/cef36771d9a042ec9d683890b1902915@……

– Exchange Web Service URL: https://outlook.office365.com/EWS/Exchange.asmx

image

Error :

The HTTP request is unauthorized with client authentication scheme ‘Ntlm’.

The authentication header received from the server was ‘Basic Realm=””‘.


image

But if you click the Overlay button it works by jumping to the Exchange Calendar Web Page.

image

Enjoy !!