Office365 – How to Block Self Service Purchase Apps

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 

2. Next import the module and connect

Import-Module -Name MSCommerce

Connect-MSCommerce

3. Run these commands to see the status

Get-MSCommercePolicy -PolicyId AllowSelfServicePurchase 

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase 

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  }

You can check the status again :

Enjoy !

PowerShell Core – Power BI Gateway Management CmdLets

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

So I opened the Cmd line using Admin privileges

Next started PWSH.exe

Next I run this command :

Install-Module -Name DataGateway

Next Check the available cmdlets

Get-Command -Module DataGateway*

Next I ran this command

Import-Module DataGateway

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

So first get an upgrade from here :

https://github.com/PowerShell/PowerShell

After upgrading your can check the version using

$PSVersionTable

Next run these commands again :

import-module DataGateway
import-module DataGateway.profile

No errors now

Run this command to login :

Login-DataGateway

It opens the browser and return this message

Next run this command to get your Cluster ID

Get-DataGatewayCluster

Next check the Gateway Status like this

Get-DataGatewayClusterStatus

So far so good  

Enjoy !!

PowerShell – Monitor CPU Cores Temperatures

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

 

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()

 

 

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

Enjoy !

PowerShell – How to access Exchange Online Resource Mailbox Calendar

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

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

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

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

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

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

You can also configure it using the OWA web interface

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

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

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 

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 

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

Error :

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

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

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

Enjoy !!

PowerShell – VBA Read Outlook Emails and Export to Excel

Getting data out of Outlook using a VBA or a COM Script is not that difficult.

But when you want to reach the data in a Shared Mailbox that’s a different story !

 

There are different options :

1. VBA script in Outlook export to clipboard

2. VBA script in Excel and export to a sheet

3. PS script that that can handle both Outlook and Excel sheet Export

 

SOLUTION :

Outlook Script that can export to a clipboard.

But it runs quite slow and is depending on Microsoft Forms Library.

That is not by default available, so you need reference it.

The COM Library located here C :\WINDOWS\SysWOW64\FM2O. DLL

 

 

Sub ListMailsInFolder()
    Dim clipboard As New MSForms.DataObject
    Set clipboard = New MSForms.DataObject
    Dim txt As String
        
    Dim objNS As Outlook.NameSpace
    Dim objFolder As Outlook.MAPIFolder
    Dim olShareName As Outlook.Recipient
    Dim MailItems As MailItem
    Dim FmtToday As String
    
    FmtToday = Format(Date - 1, "DDDDD HH:NN")
    Debug.Print DateValue(Now()) - 1
    
    Set objNS = GetNamespace("MAPI")
    Set olShareName = objNS.CreateRecipient("outbound@yourcompany.com")
    'Set objFolder = objNS.Folders.GetFirst ' folders of your current account
    
'Shared Inbox
    Set objFolder = objNS.GetSharedDefaultFolder(olShareName, olFolderInbox) '// Inbox
    Set Items = objFolder.Items.Restrict("[ReceivedTime] > '" & FmtToday & "'")
    
    Debug.Print Items.Count
    
    For Each MailItems In Items
        'If TypeName(Item) = "MailItem" Then
            ' ... do stuff here ...
            Debug.Print MailItems.Subject & " " & MailItems.ReceivedTime
        'End If
        
        'Put some text inside a string variable
        txt = txt & MailItems.Subject & " " & MailItems.ReceivedTime & vbNewLine

        'Sleep (500)
    Next
    
    'Make object's text equal above string variable
     clipboard.SetText txt
    
' SubFolder
    Set objFolder = objNS.GetSharedDefaultFolder(olShareName, olFolderInbox).Parent.Folders("Shipments")  '
    Set Items = objFolder.Items.Restrict("[ReceivedTime] > '" & FmtToday & "'")
    Debug.Print "[ReceivedTime] > '" & FmtToday & "'"
    
    Debug.Print Items.Count
    
    For Each MailItems In Items
        'If TypeName(Item) = "MailItem" Then
            ' ... do stuff here ...
            Debug.Print MailItems.Subject & " " & MailItems.ReceivedTime
        'End If
        
        'Put some text inside a string variable
        txt = txt & MailItems.Subject & " " & MailItems.ReceivedTime & vbNewLine
        
        'Sleep (500)
    Next

    'Make object's text equal above string variable
     clipboard.SetText txt

    'Place DataObject's text into the Clipboard
    clipboard.PutInClipboard
End Sub

 

Initially it ran very SLOW, so I optimized it for speed using the RESTRICT method FILTERING.

Which runs a lot FASTER.

 

You can convert this an Excel Macro to EXPORT it to a workbook sheet.

I added a input box to select the date FROM and TO, as well as added a Calendar Control

See here on how to add a Calendar Control

https://www.ablebits.com/office-addins-blog/2016/10/12/insert-calendar-excel-datepicker-template/

 

Option Explicit

Sub Button1_Click()
    On Error GoTo ErrHandler
    
    Columns("A:C").Select
    Selection.ClearContents
    
    Dim dStartDate As Date
    Dim dEndDate As Date

'Enter the specific start date and end date
    dStartDate = InputBox("Enter the start date, such as 7/1/201x:", "Specify Start Date", Range("F2").Value)
    dEndDate = InputBox("Enter the end date, such as 8/31/201x:", "Specify End Date", Date)

If dStartDate <> #1/1/4501# And dEndDate <> #1/1/4501# Then
    
' Set Outlook APPLICATION OBJECTS.
    Dim Outlook As Object
    Set Outlook = CreateObject("Outlook.Application")
    
    Dim clipboard As New MSForms.DataObject
    Dim txt As String
        
    Const olFolderInbox = 6
    Dim objNS As Object
    Dim Items As Object
    Dim Item As Object
    Dim objFolder As Outlook.MAPIFolder
    Dim olShareName As Outlook.Recipient
    
    
    Set objNS = GetNamespace("MAPI")
    Set olShareName = objNS.CreateRecipient("outbound@yourcompany.com")
    'Set objFolder = objNS.Folders.GetFirst ' folders of your current account
    
'Shared Mailbox Inbox
    Set objFolder = objNS.GetSharedDefaultFolder(olShareName, olFolderInbox) '// Inbox
    Set Items = objFolder.Items
    
    Set clipboard = New MSForms.DataObject
    
    Dim iRows, iCols As Integer
    iRows = 2
    
    For Each Item In objFolder.Items
        'If TypeName(Item) = "MailItem" Then
            ' ... do stuff here ...
            Debug.Print Item.Subject
        'End If
        
    'Put some text inside a string variable
        txt = txt & Item.Subject & vbNewLine
        
        If Format(Item.ReceivedTime, "dd/MM/YYYY") >= dStartDate And Format(Item.ReceivedTime, "dd/MM/YYYY") <= dEndDate Then
        Cells(iRows, 1) = Item.Subject
        Cells(iRows, 3) = Item.ReceivedTime
        iRows = iRows + 1
        End If
    Next
    
    'Make object's text equal above string variable
     clipboard.SetText txt
   
' Shared Mailbox SubFolder(s)
    Set objFolder = objNS.GetSharedDefaultFolder(olShareName, olFolderInbox).Parent.Folders("Shipments")
    '.Parent.Folders("Shipments").Folders("_ToDo") ' Subfolders
    Set Items = objFolder.Items
    
    For Each Item In objFolder.Items
        'If TypeName(Item) = "MailItem" Then
            ' ... do stuff here ...
            Debug.Print Item.Subject
        'End If
        
    'Put some text inside a string variable
        txt = txt & Item.Subject & vbNewLine
        
'Debug.Print Format(Item.ReceivedTime, "dd/MM/YYYY") & " - " & dStartDate

        If Format(Item.ReceivedTime, "dd/MM/YYYY") >= dStartDate And Format(Item.ReceivedTime, "dd/MM/YYYY") <= dEndDate Then
            Cells(iRows, 1) = Item.Subject
            Cells(iRows, 3) = Item.ReceivedTime
            iRows = iRows + 1
        End If
        Next

'Object's text to Clipboard
    clipboard.SetText txt

'Place DataObject's text into the Clipboard
    clipboard.PutInClipboard
   
' Release Objects
    Set Outlook = Nothing
    Set objNS = Nothing
    Set olShareName = Nothing
    Set clipboard = Nothing
    Set objFolder = Nothing
    Set Items = Nothing

End If

ErrHandler:
    Debug.Print Err.Description
End Sub

 

This is the PowerShell Version !

 

CLS

[void][Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')

$title = "Start Date"
$msg   = "From"
$default   = (Get-Date).AddDays(-1).ToString("d")  # Get-Date -UFormat "%m/%d//%Y" or (Get-Date).AddDays(-1).ToString("MM/dd/yyyy")

$sDate = [Microsoft.VisualBasic.Interaction]::InputBox($msg, $title, $default)

[string]$sDate += "  00:00" # + (Get-Date).tostring(‘t’)


[threading.thread]::CurrentThread.CurrentCulture = 'en-US'  # Important line !!

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()

$workbook.WorkSheets.Item(1).Name = "Outbound"

$sheet = $workbook.ActiveSheet

$olFolderInbox = 6

    Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null

    $outlook = new-object -comobject outlook.application


    $namespace = $outlook.GetNameSpace(“MAPI”)

    $olShareName =  $namespace.CreateRecipient("outbound@yourcompany.com")

    $olShareName.Resolved()

    $sFilter = ("[ReceivedTime] >= '$olddate'")

# Shared Inbox

    $Folder = $namespace.GetSharedDefaultFolder($olShareName, $olFolderInbox)
    
    $Items = $Folder.Items.Restrict("[ReceivedTime] > '$sDate'")
    
    $Items | Select-Object -Property Subject, ReceivedTime, SenderName, SenderEmailAddress | ? {$_ -match "@"} 


    # $Items | Export-CSV -NoTypeInformation XXXX\Trial.csv 

$counter = 0

    foreach($Item in $Items){

     $Email = ($Item.SenderEmailAddress | ? {$_ -match "@"})

   # ($Item.Subject + " " + $Item.ReceivedTime + " " + $Item.SenderName + " " + $Email)

    $counter++
        
        $sheet.cells.Item($counter,1) = $Item.Subject
        $sheet.cells.Item($counter,3) = $Item.ReceivedTime.Date
    }


# SubFolder
    $SubFolder = $Folder.Parent.Folders("Shipments")

    $olddate = (Get-Date).AddDays(-2).ToLongDateString()

    $Items = $SubFolder.Items.Restrict("[ReceivedTime] > '$sDate'")
    
    $Items | Select-Object -Property Subject, ReceivedTime, SenderName, SenderEmailAddress | ? {$_ -match "@"} 

    # $array | Export-CSV -NoTypeInformation XXXX\Trial.csv 

    foreach($Item in $Items){

    $Email = ($Item.SenderEmailAddress | ? {$_ -match "@"})

    # ($Item.Subject + " " + $Item.ReceivedTime + " " + $Item.SenderName + " " + $Email)

    $counter++
        
        $sheet.cells.Item($counter,1) = $Item.Subject
        $sheet.cells.Item($counter,3) = $Item.ReceivedTime.Date
    }
 
 $sheet.Cells.EntireColumn.AutoFit()


[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Outlook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

# Stop-Process -Name EXCEL -Force

Remove-Variable Excel, Email

 

Enjoy !!

PowerShell – How to run an EncodedCommand using a Bat file

If you don’t want to expose the PowerShell script logic to your end users,

you can use the EncodedCommand switch to run Base64 Encoded files.

 

Next if you don’t want to give the PowerShell command or PS IDE you can use just a Bat file instead.

 

But this is challenging to get it all working.

 

SOLUTION :

1. Create a Bat file that calls the Encoded PowerShell script :

Like this …

 

Powershell -file "C:\TEMP\OUTBOUND_Logfiles.ps1"

 

Next create an EncodedCommand PowerShell script :

Like this …

powershell.exe -EncodedCommand "JABmAGkAbABlAHMAIAA9ACAARwBlAHQAL...."

 

TIP :

If you want to use the Out-GridView for showing the output in a GUI.

You will need to at this to the end of the script in order to keep the GridView Open at the end.

....

Read-Host "press enter to exit"

 

Otherwise the GridView will not remain open at the end when the script finished

 

Enjoy !!

PowerShell – GUI Ribbon

Ever wanted to build a modern GUI including a Ribbon in just a few clicks …

Office 2007 Style

Office 2010 Style

Office 2013 Style

Well this is where it all starts :

New Site : https://github.com/RibbonWinForms/RibbonWinForms

Old Site : https://www.codeproject.com/Articles/364272/Easily-Add-a-Ribbon-into-a-WinForms-Application-Cs?fid=1704255&df=90&mpp=25&sort=Position&view=Normal&spc=Relaxed&fr=101&prof=True

Movie : https://www.youtube.com/watch?v=OPrj7c1CYIg

Since the documentation shows all C# code we can easily transform this a PowerShell Script

PREREQUISITES :

You need to download here the Ribbon Assembly to reference in the script.

SOLUTION :

Don’t forget to change the path to the Assembly and the ICONS

Add-Type -Path "C:\_\System.Windows.Forms.Ribbon35.dll"

$form = New-Object System.Windows.Forms.Form
$form.Size = New-Object System.Drawing.Size(600,300)
$form.TopMost = $true
$form.StartPosition = "CenterScreen"

$form.AutoScalemode = "Font" # Enum Font or DPI
$form.AutoSize = $true
$form.AutoSizeMode = "GrowOnly"  # "GrowAndShrink"

$form.SuspendLayout()

# Objects
$ribbon = New-Object System.Windows.Forms.Ribbon
$tab1 = New-object System.Windows.Forms.RibbonTab
$tab2 = New-object System.Windows.Forms.RibbonTab
$MenuItem1 = New-object System.Windows.Forms.RibbonOrbMenuItem
$panel1 = New-object System.Windows.Forms.RibbonPanel
$button1 = New-object System.Windows.Forms.RibbonButton

# $ribbon | get-member
# $ribbon.Parent

# ribbon1
$ribbon.Font = New-Object System.Drawing.Font("Segoe UI", 9);
$ribbon.Location = New-Object System.Drawing.Point(0, 0);
$ribbon.Minimized = $false;
$ribbon.Name = "ribbon1";

$ribbon.OrbStyle = "Office_2007" # "Office_2010"
#$ribbon.Visible = $true

$ribbon.OrbDropDown.BorderRoundness = 8;
$ribbon.OrbDropDown.Location = New-Object System.Drawing.Point(0, 0);
$ribbon.OrbDropDown.MenuItems.Add($MenuItem1);
$ribbon.OrbDropDown.Name = "Drop";
$ribbon.OrbDropDown.Size = New-Object System.Drawing.Size(527, 116);
$ribbon.OrbDropDown.TabIndex = 0;
$ribbon.OrbImage = $null;
$ribbon.RibbonTabFont = New-Object System.Drawing.Font("Trebuchet MS", 9);
$ribbon.Size = New-Object System.Drawing.Size(1280, 200);
$ribbon.TabIndex = 0;
$ribbon.Tabs.Add($Tab1);
$ribbon.TabsMargin = New-Object System.Windows.Forms.Padding(12, 26, 20, 0);
$ribbon.Text = "ribbon1";
$ribbon.ThemeColor = "Blue";
$ribbon.Height = 150

# ribbonOrbMenuItem1
$MenuItem1.DropDownArrowDirection = "Left";
$MenuItem1.Image = [System.Drawing.Image]::FromFile("C:\_\ABF-Outlook.gif") 
# ((System.Drawing.Image)(resources.GetObject("ribbonOrbMenuItem1.Image")));
$MenuItem1.SmallImage = [System.Drawing.Image]::FromFile("C:\_\ABF-Outlook.gif") 
# ((System.Drawing.Image)(resources.GetObject("ribbonOrbMenuItem1.SmallImage")));
$MenuItem1.Text = "ribbonOrbMenuItem1";

# ribbonTab1
$Tab1.Panels.Add($panel1);
$Tab1.Text = "ribbonTab1";

# ribbonPanel1
$Panel1.Items.Add($Button1);
$Panel1.Text = "ribbonPanel1";

# ribbonButton1
$Button1.Image = [System.Drawing.Image]::FromFile("C:\_\SaveItem.png") 
#((System.Drawing.Image)(resources.GetObject("ribbonButton1.Image")));
$Button1.SmallImage = [System.Drawing.Image]::FromFile("C:\_\SaveItem.png") 
# ((System.Drawing.Image)(resources.GetObject("ribbonButton1.SmallImage")));
$Button1.Text = "ribbonButton1";

# ribbonTab2
$Tab2.Text = "ribbonTab2";

$form.Controls.Add($ribbon)

$form.ResumeLayout($false)

$form.ShowDialog()

Enjoy !