Some remarks with PowerShell Core and Azure / PowerBI

All my scripts are under Windows and for automation what means that authentication to Azure is made using a Service Principal, a Register App. 

I'm working from a VM, so I want to use a certificate for Authentication. 

Working with a Proxy

Most people nowadays work behind an enterprise proxy. 

pwsh makes the live more easy behind a proxy - it gets all configuration from IE, so you can even use a pac file - but also implement some diferences. 

With older PowerShell versions, a common configuration was:

$proxyUri = "http://MyProxyUrl:MyProxyPort"

[System.Net.WebRequest]::DefaultWebProxy = new-object System.Net.WebProxy ($proxyUri, $true) 

With PowerShell core this is now: 

$proxyUri = "http://MyProxyUrl:MyProxyPort" 

[System.Net.Http.HttpClient]::DefaultProxy = New-Object System.Net.WebProxy( $proxyUri ) 

PowerShell AzureAD 

It's almost impossible to use a certificate to authenticate with AzureAD module. It works fine under an user, but installing the certificate as computer level normally it will not work. I give up to use AzureAD module because of this error: 

"Connect-AzureAD Could not load type 'System.Security.Cryptography.SHA256Cng' from assembly" 

I had no luck finding a workaround for this problem..

PowerShell Microsoft. Graph 

Shifting to Microsoft. Graph was almost transparent. Only permissions are now with a different setup in Azure. Make sure that permissions are assigned to application:

 

Parameter -Scope is not available for Service Principals, so all permission configuration is on Azure. 

After connection the available permissions can be listed using:

 (Get-MgContext).scopes

User.Read.All

 

SQL Server - Check all databases with "Auto Close" active

select name, state_desc, is_auto_close_on from sys.databases where is_auto_close_on = 1

SQL Server - Rebuild all indexes for all tables in a single database

USE "YourDatabaseNameHere" DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' PRINT (@sql) EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor

Move or relocate /var to another partition in CentOS 6.8

A few steps to easily move or relocate /var in CentOS 6.8:

Start by sudo bash.

- Mount the future /var with a different name, like /newvar.
  Tip: Setup fstab to use /newvar and use "mount -a" will also help you checking if fstab is fine.
- Copy current /var to new location with rsync:
       rsync -aqxP /var/* /newvar
- fix the security contents with
       restorecon -r -v /newvar
- adapt fstab to mount the new /var (if you had setup fstab to /newvar now is the time to set it to /var)
- reboot

Use [by default] TLS 1.2 on PowerShell

As simple as this:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

TP-Link MR3420 and Vodafone Modem K5160

TP-Link MR3420 v2 refused to work with my new K5160.

The firmware from TP-Link didn't help so I give a try on "Of Modems and Men" firmware [ROOter].
It's quite simple to install the firmware (but be aware that rollback is not so simple).

ROOter firmware identified the modem correctly but still no connection to the internet. It seems that the hostless modem is still working as MBIM.

The trick is to ssh to the modem, rename /sbin/umbim to something else and reboot the router. 

Get size of each table on MS SQL Server database

Simple query to get the size of each table on Microsoft SQL Server database.

SELECT TOP 1000 t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY t.Name