Office 365 – Retrieve User Password Expiration Date

December 4, 2018

Sometimes it will be handy to scan your O365 User base on next Password Expiration Date.

This is done using PowerShell, like this.

First connect to your tenant and execute this script :

CLS

$cred = Get-Credential "your.credentials@company.com"

Connect-MsolService -Credential $cred

$domain = Get-MsolDomain | where {$_.IsDefault -eq $true}

$PasswordPolicy = Get-MsolPasswordPolicy -DomainName $domain.Name

$Account = "Account.ToCheck@company.com" # Change HERE !!

$UserPrincipal  = Get-MsolUser -UserPrincipalName $Account

$UserPrincipal | fl PasswordNeverExpires

$PasswordExpirationDate = $UserPrincipal.LastPasswordChangeTimestamp.AddDays($PasswordPolicy.ValidityPeriod)

Write-host "Password will Expire on : $PasswordExpirationDate"

$StartDate = (GET-DATE)
$DaysLeft = NEW-TIMESPAN -Start $StartDate -End $PasswordExpirationDate
$DaysLeft = [math]::Floor($DaysLeft.TotalDays)

Write-host "Password will Expire in # Days : $DaysLeft"

$UserPrincipal | select DisplayName, LastPasswordChangeTimeStamp,@{Name=”PasswordAge”;Expression={((Get-Date).ToUniversalTime())-$_.LastPasswordChangeTimeStamp}} | sort-object PasswordAge -desc

image

If you want to change the Password Policy to NEVER EXPIRE use this codereset

Set-MsolUser -UserPrincipalName $Account -PasswordNeverExpires $true

If you want to RESET the Password, use this code

Set-MsolUserPassword -UserPrincipalName $Account -NewPassword "PassWord"

Enjoy !

Advertisements

Windows 2016 – Hyper-V CheckPoints Disk Space

December 4, 2018

Once you create VM checkpoints in Hyper-V it might start consuming a lot a disk space afterwards.

image

Because it will write data to the AVHDX files.

You can use PowerShell to investigate the current disk size.

Get-VM YourVM | Select-Object VMId | Get-VHD | Select-Object –Property path,computername,vhdtype,
@{label=’Size(GB)’;expression={$_.filesize/1gb –as [int]}}

image

As you can see the VHD Type is saying DIFFERENCING.

That is because you have a checkpoint pending. And this is NOT recommended in a production environment.

There are some tricky scenario’s where deleting checkpoints can corrupt your VM.

See here for more info

Enjoy !


PowerShell – Accessing Power BI Desktop Data and more…

November 18, 2018

Recently I got interested in getting data out of a Power BI Data Model to push into an SQL database.

On my way I discovered a lot of interesting techniques and Tools for Power BI Desktop.

1. First of all you need to know that Power BI Desktop actually is a small Web Server running an SSAS Mini Cube.

2. Next you need to prepare PowerShell to access the PBI Cube.

Solution :

In order to start you first need to start PBI Desktop and open a PBIX file you created before.

Once started you need to find out on which Random Port is was started.

There are 4 way to get the job done.

1. Install Dax Studio which you need to have, no question about this !

    Even if you are not using it for getting the Random Port…

Look in the bottom right corner :

image

2. Command line tool NETSTAT

   Run NETSTAT –b command and look for Power BI

image

In this case it was Port 60520

3. Look for the msmdsrv.port.txt file.

Once PBI is started it will create some folders and files.

You can find those here :

%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\

image

If you have multiple PBIX files open you will see more Random created subfolders.

Go to the random created folder and next to the Data Subfolder.

Open the msmdsrv.port.txt file to get the Port number.

image

4. Using the Tabular Editor

But it needs some dependent MS AS AMO Libraries

image

image

Now that you know how to get access to the Web Server Port.

PowerShell 32 Bit

You can now test PowerShell connection to the Power BI Desktop you have open.

But you first need a Connection String to be used in PowerShell

FIRST USE the 32 Bit PowerShell if you are using a 32 Bit Office installation.

Later in this post I will explain how to get it running in a 64Bit PS even if you have a 32 Bit Office installed.

I suggest that you use EXCEL for this to copy and paste it Winking smile

Go to Data and choose From Analysis Services.

image

Follow these steps :

image

image

image

image

Copy it from here :

image

You can also get the Catalog GUID from the folder name where you could find the Port number.

image

Run this PowerShell Script, it uses a DMV Query to get hold of the PBI Cube Properties

CLS

$Port = "60520"
$Catalog = "cff5b9a8-2f87-416c-97fd-8eccd5962d51"

$Query = 'Select * from $SYSTEM.DISCOVER_PROPERTIES'

$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] | Format-Table # export-csv $filename -notypeinformation
   
$connection.Close() 

image

To list data from a Table use the EVALUATE command

$Query = 'EVALUATE Tickets'

image


PowerShell 64 Bit

If you are running a 32 Bit Office, you need to install the OLAP Drivers for 64 bit

(will be done when you install DAX Studio).

clip_image002

Or manually register the MSOLAP.DLL from the Power BI install folder.

image

Tools :

Power Pivot :

You can connect to a Power BI Desktop model using Power Pivot for example.

image

image

Dax Studio :

You can run commands directly against your Power BI Desktop.

Use DAX Commands

image

Or use a DMV Query.

image

For more info on DMV Queries see here

Power BI Helper :

image

More Tools you can find here

Enjoy !


Windows Server 2016 – HP iLO PowerShell and more…

November 9, 2018

image

image

HPE Integrated Lights-Out (iLO),

HPE Onboard Administrator (OA) for HPE BladeSystem Enclosures and

System BIOS of HPE ProLiant servers, this set of PowerShell utilities

 

image

We will only use iLO at this moment, because this is the most valuable.

It is a proprietary embedded server management technology of HP.

iLO makes it possible to perform activities on an server from a remote location. Even if the server OS is not responding.

 

1. Download the PowerShell Module

See here at the moment we are at version 2.1

image

 

2. Load the PS Module

Check the version

 

ClS

import-module "C:\Program Files (x86)\Hewlett Packard Enterprise\PowerShell\Modules\HPEiLOCmdlets\HPEiLOCmdlets.psd1"

Get-Module -ListAvailable "HPEiLO*"

 

image

List the Cmdlets

Get-Command -Module "HPEiLOCmdlets"

 

image

 

Update the PS Module if needed

Update-HPEiLOModuleVersion

 

Search for iLO servers in your network using a range or subnet

$IPRange = Read-host " Enter the IP Range of HPOA ( 10.0.0.1-25) "

Find-HPEiLO $IPRange

image

Now you are ready to run some cmdlets.

 

Let’s check the power of the server:

Get-HPEiLOServerPower -Connection $connection

image

 

Let’s check the Temperature Sensors

$Ret = Get-HPEiLOTemperature -Connection $connection

$Ret.Temperature | Out-GridView

 

image

 

3. iLO commandline Tools

The HP management utillity called hponcfg.exe is installed here.

 

image

iLO config Export Example :

Command :

clip_image002

hponcfg.exe /w c:\Temp\iloconfig.txt

 

image

 

You can use the is config file to make changes to your iLO configuration, like resetting your Password etc.

 

hponcfg /f your_script_name.txt

 

Enjoy !


PowerBI – Using FreshDesk Web Services API

November 7, 2018

We started exploring the use of the FreshDesk cloud-based customer support platform.

Which is one of the modules of the FreshWorks Suite.

You can start off with a free subscription, which is good enough to start building you BI tool.

image

Once you are happy you can switch over to a paid subscription giving you extra features.

image

One of them is extensive reporting,

After exploring the Power BI integration. I found out that all of the third party solution provided did not work Sad smile

And one if the solutions required that you have a Power BI Pro licence…

SOLUTION :

So I ended up making my own Power BI Dashboard using the FreshDesk REST Web Services API.

See here for the API documentation : https://developers.freshdesk.com/api/

The Web Service API looks very straight forward and works with any REST compatible application.

Implementing this in Power BI, was challenging.

But as you can see here, you can do all you need in your Free Power BI Desktop too.

So this will save you some licenses money too Winking smile

You can start off with a free subscription, which is good enough to start building your BI Dashboards.

image

image

Shows that Power BI is the right marriage for the 2 platforms, O365 and FreshDesk Winking smile

In case you need to run this as offline data source or want to schedule reports.

You can use PowerShell to extract the data, and use this as a data source.

This will save you quite some money uplifting your subscription.

image

Example :

If you have 25 Agents and you need to schedule reports. Your subscription is $19 / user / month more expansive.

This is a burden of an additional $5,000 subscription cost.

Other paid service provider is FreshInsights / Radiare

image

PowerShell

If you run the Invoke-WebRequest command against your URI in Powershell.

Like this :

$Data = Invoke-WebRequest -uri $URL -Headers $Headers -Method GET -ContentType application/json 

image

You can see in the response header information the API Version and the remaining calls you have

In this case 2996 out of 3000 per hour !

This is a limit set by Freshdesk to reduce the maximum API calls per hour.

Steps to get started using Power BI :

1. create a free trial subscription

2. Copy your Web Service API Key

image

3. Open Power BI and do a Get DATA Web Data

image

image

Paste in the web service URL for Tickets for example.

4. Connect to FreshDesk using the API Key and password

image

5. Next built your Queries and transformations to extract the data

image

6. Build your Visuals and Measures

image

An extra bonus is that you can incorporate this in you SharePoint site.

No license needed see here on how to.

image

Need to get started building your own Dashboard, drop a message here.


Visual Studio Code – How to run PowerShell or AutoIT

July 25, 2018

What is Visual Studio Code (VSCode) ?

Visual Studio Code is a cross platform source code editor for Windows, Linux and macOS, that support a multitude of programming and scripting languages.

https://en.wikipedia.org/wiki/Visual_Studio_Code

 

image

 

And more using extension …

https://marketplace.visualstudio.com/vscode

image

More then 1700 extensions available, only for Code language alone …

 

Install VSCode :

https://code.visualstudio.com/download

 

VSCode Architecture ?

VS Code is fully decoupled from the specific debugger and this architecture allows VS Code to debug anything, as long as there’s a Debug Adapter available, as illustrated here:

image

 

Why using VSCode ?

If you don’t want to use the full blown Visual Studio, which is a ton of software on you computer !

But still want to have the syntax highlighting, code intellisense, and more then VSCode is the real deal for you.

Big community and training videos

https://code.visualstudio.com/community

 

Extensions ?

There are many extensions available that gives VSCode extra functionality…

On of them is running code inside VSCode, like PowerShell and AutoIT, and hundreds of many more !

So you don’t need to use the PowerShell ISE or SciTE anymore, keeping all different code development & debugging in 1 tool Smile

 

Solution :

1. install the PS Code extension:

https://marketplace.visualstudio.com/items?itemName=ms-vscode.PowerShell

 

2. Restart VS Code

 

3. Test a PS Script

Open a PS1 script and press F5.

image

See here for more info :

https://4sysops.com/archives/how-to-run-powershell-code-in-vscode/

 

The same goes for AutoIT Smile

1. Install the extension

2. Restart VSCode

3. Test a au3 Script

Keyboard Shortcuts :

  • Run Script: F5
  • AutoIt Check: Ctrl+F5
  • Compile Script: Ctrl+F7
  • Build Script: F7
  • Run AutoIt Help: Ctrl+F1
  • Run Au3Info: Ctrl+F6
  • Debug to MsgBox: Ctrl+Shift+D
  • Debug to Console: Alt+D
  • Run Koda: Alt+M

image

 

More info on how to Configure VSCode :

https://4sysops.com/archives/vscode-as-a-console-for-powershell-core-6-part-1-multiple-shells/

 

Enjoy!


Windows Core IoT – Stop Cortana process

July 21, 2018

When running the latest version of Windows IoT, it will install the Cortana service as well …

Which can take quite some unnecessary memory and CPU from your little RPI3 Sad smile

This way you can find out how to stop it manually, because there is no way you can do it at boot time ?

When starting Windows IoT you can see the Cortana process is running :

image

If you select STOP you notice it doesn’t stop from the first time ?

You need to repeat it multiple times….

Uninstalling did not work, IoT crashed afterwards and stopped working.

Let’s see if we can find a different way ?

We will make use of some command line tools, see here :

First lets list all processes using TLIST.EXE

image

You will notice you won’t find anything related to Cortana at first sight…

Let’s run TLIST.EXE –v

image

Just like to the full Windows 10 you find out that SearchUI.exe is hosting the Cortana process.

Let’s try to kill it now using the KILL.EXE command :

image

Using the Process ID or the Process Name …

Indeed the process stopped immediately.

image

I noticed that after a while the process starts again automatically ?

So now we can quickly kill it again when needed.

Enjoy !