Monitoring with PowerShell: Monitoring Office 365 deleted users & License usage

I’ve been getting some requests to talk more about monitoring access and license management for Office 365. Some of you have asked how to be notified when users get deleted, or to get a notification right before a user is deleted permanently. Another question was on how to check if all licenses are assigned and you’re not wasting any resources or money on unused licenses. I’ve decided to blog about both ūüôā

Monitoring deleted users

So the first one up is monitoring the deleted users – I understand monitoring this for a multitude of reasons. Imagine you’re distributing licenses and when a user gets deleted you need to update your billing systems, or imagine that you have a specific off-boarding procedure that needs to be kicked off when a user is deleted. I’ve created two cases; One to alert as soon as a deleted user has been found, another to alert when the user is about to be permanently deleted.

Monitoring all deleted users
##############################
$ApplicationId = 'XXXX-XXXX-XXXX-XXX-XXX'
$ApplicationSecret = 'YourApplicationSecret' | Convertto-SecureString -AsPlainText -Force
$TenantID = 'YourTenantID.Onmicrosoft.com'
$RefreshToken = 'VeryLongRefreshToken'
##############################
$credential = New-Object System.Management.Automation.PSCredential($ApplicationId, $ApplicationSecret)
$aadGraphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.windows.net/.default' -ServicePrincipal -Tenant $tenantID 
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $tenantID 
Connect-MsolService -AdGraphAccessToken $aadGraphToken.AccessToken -MsGraphAccessToken $graphToken.AccessToken
$customers = Get-MsolPartnerContract -All
$DeletedUserlist = foreach ($customer in $customers) {
    write-host "Getting Deleted users for $($Customer.name)" -ForegroundColor Green
    $DeletedUsers = Get-MsolUser -ReturnDeletedUsers -TenantId $($customer.TenantID)
    foreach($User in $DeletedUsers){ "$($user.UserPrincipalName) has been deleted on $($User.SoftDeletionTimestamp)" }
}
if(!$DeletedUserlist) {  $DeletedUserlist = "Healthy" }
Monitoring near permanent delete date.
##############################
$Daystomonitor = (Get-Date).AddDays(-28) #This means we will alert when a user has been deleted for 28 days, and is 1 day before permanent deletion.
##############################
$ApplicationId = 'XXXX-XXXX-XXXX-XXX-XXX'
$ApplicationSecret = 'YourApplicationSecret' | Convertto-SecureString -AsPlainText -Force
$TenantID = 'YourTenantID.Onmicrosoft.com'
$RefreshToken = 'VeryLongRefreshToken'
##############################
$credential = New-Object System.Management.Automation.PSCredential($ApplicationId, $ApplicationSecret)
$aadGraphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.windows.net/.default' -ServicePrincipal -Tenant $tenantID 
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $tenantID 
Connect-MsolService -AdGraphAccessToken $aadGraphToken.AccessToken -MsGraphAccessToken $graphToken.AccessToken
$customers = Get-MsolPartnerContract -All
$DeletedUserlist = foreach ($customer in $customers) {
    write-host "Getting Deleted users for $($Customer.name)" -ForegroundColor Green
    $DeletedUsers = Get-MsolUser -ReturnDeletedUsers -TenantId $($customer.TenantID) | Where-Object {$($User.SoftDeletionTimestamp) -lt $Daystomonitor}
    foreach ($User in $DeletedUsers) { "$($user.UserPrincipalName) has been deleted on $($User.SoftDeletionTimestamp)" }
}
if (!$DeletedUserlist) { $DeletedUserlist= "Healthy" }


 

To explain the scripts; The first script connects to each tenant in your Microsoft Partner portal, grabs all deleted users, and gives you a report of all deleted users. The second one does the same, but filters specifically on users that have been deleted for 28 days.

Monitoring unused licenses

So this is one that we are going to use internally after getting some requests from all of you – I think it’s a pretty smart idea to monitor if licenses are in use, and if not to alert on them. It helps to compare the administrative side of licensing to the actual cost of licensing.

##############################
$ApplicationId = 'XXXX-XXXX-XXXX-XXX-XXX'
$ApplicationSecret = 'YourApplicationSecret' | Convertto-SecureString -AsPlainText -Force
$TenantID = 'YourTenantID.Onmicrosoft.com'
$RefreshToken = 'VeryLongRefreshToken'
##############################
$credential = New-Object System.Management.Automation.PSCredential($ApplicationId, $ApplicationSecret)
$aadGraphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.windows.net/.default' -ServicePrincipal -Tenant $tenantID 
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $tenantID 
Connect-MsolService -AdGraphAccessToken $aadGraphToken.AccessToken -MsGraphAccessToken $graphToken.AccessToken
$customers = Get-MsolPartnerContract -All
$UnusedLicensesList = foreach ($customer in $customers) {
    write-host "Getting licenses $($customer.name)" -ForegroundColor Green
    $Licenses = Get-MsolAccountSku -TenantId $($customer.TenantId)
    foreach ($License in $Licenses) { 
        if ($License.ActiveUnits -lt $License.consumedUnits) { "$($customer.name) - $($License.AccountSkuId) has licenses available." }

    }
}
if (!$UnusedLicensesList) { $UnusedLicensesList = "Healthy" } 

And that’s it! a somewhat longer blog than normal, with multiple scripts. But I hope you’ve enjoyed it. As always, Happy Powershelling.

Documenting with PowerShell: Handling IT-Glue API security and rate limiting.

I’ve been blogging a whole lot about documentation lately; I truly believe all automated documentation is better than just having people enter data manually. My company uses IT-Glue as a documentation system. IT-Glue is a very cool system but has some huge API limitations. For example; You’re allowed to make 10 requests per second and 10,000 requests per day. These limitations can get pretty bad if you manage a lot of workstations or servers that upload data at the same time.

After my previous blogs the comment I’ve received most was worries about the API key. If they key gets stolen you’re giving away the keys to the castle. The API has no limitations and with a leaked key all your documentation could be download. I’ve been discussing this issue with IT-Glue for some time but haven’t gotten a real solution yet. This has forced me to look for a solution myself. I gave myself some requirements for the solution.

  • The solution needed to be simple and accessible for everyone.
  • The solution needed to have multiple levels of authentication; an API key, IP whitelisting, and organization whitelisting.
  • The solution needed to block requests for all passwords/files/etc for all organisations.
  • The solution needed to allow some form of handling of the API rate limiting, e.g. repeating a request if it was rate limited.
  • The solution needed to be able to used, without adapting any scripts (except URLs and API codes.)

So after some research I decided to use an Azure Function for this. I’ve blogged about Azure Functions before, but the main reason is that running this function in the consumption model will cost us nothing (or next to nothing if you are an extremely heavy user.)

Setup

This time we will not use the Azure Function to only run a script but act as a “middleware” for the IT-Glue API. Follow this guide to set up your Azure function App. The only difference is that we select “PowerShell” as our runtime language. Do not continue at “Create an HTTP triggered function” as we’re going to be inserting our own function.

When the Function App has been deployed click on your Function’s name and then on “platform features”. You should be presented with the following screen

In this screen click on “Configuration” – We’re going to be adding some configuration options here that are used in our scripts. Add the three following items:

  • AzAPIKey: This will be the new API key you will enter on all your scripts that will upload data to IT-Glue. Generate a password for this or enter one of choice.
  • ITGlueURI: This is the current IT-Glue API url you use, most likely https://api.itglue.com or https://api.eu.itglue.com
  • ITGlueAPIKey: Your current API key. This is the only location that this API key will be used from now on.

After this you can return to the overview page and click the + symbol next to the “Functions”, Choose the “HTTP trigger” option. Name the HTTP trigger “AzGlueForwarder” and choose the Anonymous Authorisation level. This is because we are going to take care of authentication on the script level and not at the Azure Function level. After creating the function you’ll be presented with a script page. Paste the following script:

AZGlueForwarder
using namespace System.Net
param($Request, $TriggerMetadata)
#Check if AZapiKey is correct
if ($request.Headers.'x-api-key' -eq $ENV:AzAPIKey) {
    #Comparing the client IP to the Organization list, and checking if it exists.
    $ClientIP = ($request.headers.'X-Forwarded-For' -split ':')[0]
    $CompareList = import-csv "AzGlueForwarder\OrgList.csv" -delimiter ","
    $AllowedOrgs = $comparelist | where-object { $_.ip -eq $ClientIP }
    if (!$AllowedOrgs) { 
        Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
                headers    = @{'content-type' = 'application\json' }
                StatusCode = [httpstatuscode]::OK
                Body       = @{"Error" = "401 - No match found in allowed list" } | convertto-json
            })
        exit 1
    }

    #Sending request to ITGlue
    #$resource = $request.params.path -replace "AzGlueForwarder/", ""
    $resource = $request.url -replace "https://$($ENV:WEBSITE_HOSTNAME)/API/AzGlueForwarder/", ""
    #Replace x-api-key with actual key
    $ITGHeaders = @{
        "x-api-key" = $ENV:ITGlueAPIKey
    } 
    $Method = $($Request.method)
    $ITGBody = $($Request.body)
    #write-host ($AllowedOrgs | out-string)
    $SuccessfullQuery = $false
    $attempt = 3
    while ($attempt -gt 0 -and -not $SuccessfullQuery) {
        try {
            $ITGlueRequest = Invoke-RestMethod -Method $Method -ContentType "application/vnd.api+json" -Uri "$($ENV:ITGlueURI)/$resource" -Body $ITGBody -Headers $ITGHeaders
            $SuccessfullQuery = $true
        }
        catch {
            $ITGlueRequest = @{'Errorcode' = $_.Exception.Response.StatusCode.value__ }
            $rand = get-random -Minimum 0 -Maximum 10
            start-sleep $rand
            $attempt--
            if ($attempt -eq 0) { $ITGlueRequest = @{'Errorcode' = "Error code $($_.Exception.Response.StatusCode.value__) - Made 3 attempts and upload failed. $($_.Exception.Message) " } }
        }
    }

    #Checking if we can strip the data that does not belong to this client. 
    #Important so passwords/items can only be retrieved belonging to this organisation.
    #Can't do it for all requests, such as get-organisation, but for senstive data it works perfectly. :)

    if ($($ITGlueRequest.data.attributes.'organization-id')) {
        write-host ($AllowedOrgs.ITGlueOrgID)
        $ITGlueRequest.data = $ITGlueRequest.data | where-object { $_.attributes.'organization-id' -in $($AllowedOrgs.ITGlueOrgID) }    
    }

    #Sending the final object back to the client.
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
            headers    = @{'content-type' = 'application\json' }
            StatusCode = [httpstatuscode]::OK
            Body       = $ITGlueRequest
        })


}
else {
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
            headers    = @{'content-type' = 'application\json' }
            StatusCode = [httpstatuscode]::OK
            Body       = @{"Error" = "401 - No API Key entered or API key incorrect." } | convertto-json
        })
    
}

Save the script and use the right-hand menu to add a file to the function. Call this file “OrgList.csv”. This is the database that will be used to check which IP’s are allowed to upload data, and for which organisations they can retrieve data.

IP,ITGlueOrgID
1.1.1.1,123456
2.2.2.2,123457

Next click on “Integrate” and select the allowed methods, in our case we want all methods selected for the IT-Glue API. Replace the “Route template” with “{*path}”.

Click on AzGlueForwarder once more and press “Get Function URL” and copy this URL up to the {PATH} part. This will be the URL you will put in place of the API endpoint variable in your scripts. e.g. “https://AzureFunctionITGlue.azurewebsites.net/api/”.

And that’s it! A small recap:

  • Create the Azure Function
  • Add the environment variables AzAPIKey ITGlueBaseURI,ITGlueAPIKey.
  • The function URL will be your new IT-Glue API url to put in your scripts
  • The AzAPIKey is the key to put in your script.
  • The IT-Glue API key will only remain at the Azure Function side.
  • The OrgList.CSV file should contain your client’s their IP’s and allowed organisation.
  • your API requests can only be used for the organisations defined in OrgList.CSV.
  • When an API call fails, the script will try again 3 times, each with a random wait between 1 and 10 seconds to prevent rate limiting from getting in the way.

It’s a fairly simple but clean solution while I try to work with our friends at IT-Glue to increase the API limitations. It also helps on the security side as no one will be able to just download your entire database.

That’s it for today. As always, Happy PowerShelling.

Monitoring with PowerShell: Monitoring SQL server health

Seems like this is the week of SQL server blogs! This time we’re going to cover monitoring the SQL server health. SQL server health monitoring is important to keep all line of business applications in check and to make sure they perform well. We’ll be focussed on monitoring the server, databases, and jobs.

We will use the same trick as we did in the last SQL post. We’re going to be using the SQL Server module called SQLPS, which loads a PSDrive to browse all databases and get the state of each database. So let’s get started!

The script

I’d like to take a moment to point at that this script offers only very basic monitoring. This is often enough for MSPs and non-dba type administrators. If you want more extensive monitoring you should really look into the amazing dbatools module by Chrissy Lemaire and her team of amazing PowerShell admins/dbas. ūüôā

The script alerts on databases that are not in a normal state, That have a recovery model other than “Simple” and where a database max size has been set. Also we’re checking if the database is located on C:\. You might want to comment out one of these if you do not care about one of these settings. These are just some of the things that we look out for in our environments. Its fairly straight forward to edit this script to monitor the backup dates instead, or if the database has the correct collation.

import-module SQLPS
$Instances = Get-ChildItem "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)"
foreach ($Instance in $Instances) {
    $databaseList = get-childitem "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)\$($Instance.Displayname)\Databases"
    $SkipDatabases = @("Master","Model","ReportServer","SLDModel.SLDData")
    $Errors =  foreach ($Database in $databaselist | Where-Object {$_.Name -notin $SkipDatabases}) {
        if ($Database.status -ne "normal") {"$($Database.name) has the status: $($Database.status)" }
        if ($Database.RecoveryModel -ne "Simple") {  "$($Database.name) is in logging mode $($Database.RecoveryModel)" }
        if ($database.filegroups.files.MaxSize -ne "-1") { "$($Database.name) has a Max Size set." }
        if ($database.filegroups.files.filename -contains "C:") { "$($Database.name) is located on the C:\ drive." }
    }
}
if (!$errors) { $HealthState = "Healthy" } else { $HealthState = $Errors }  

And that’s it! Of course, modify these scripts to your own environment and requirements. And as always, Happy PowerShelling.

Documenting with PowerShell: Active Directory domain and settings

Clients that still have a server on-site are become rare these days – Most of our client base is either completely public cloud using AAD or they have hosted servers in our private cloud. For these clients I’ve made the following script to document their Active Directory server settings. I always I want to be in complete control of my clients environment. That means having up to date documentation at the ready.

Of course there are a stack of other reasons to document the Active Directory environment, think of disaster recovery/runbook scenarios, troubleshooting, possible mergers, or even simply getting a correct overview of the sites, servers, and roles. So, let’s get started! I’ll be posting 2 versions of the script. One for IT-Glue and another for generic use.

IT-Glue version

Warning: Currently my wordpress installation is still replacing < with the HTML equivalent. I’m looking into better code plugins but please check the HTML code parts if you are seeing strangle results.

#####################################################################
$APIKEy =  "APIKEY"
$orgID = "ORGID"
$APIEndpoint = "https://api.itglue.com"
$FlexAssetName = "Active Directory - AutoDoc"
$Description = "A network one-page document that shows the current configuration for Active Directory."
#####################################################################
#Grabbing ITGlue Module and installing.
If (Get-Module -ListAvailable -Name "ITGlueAPI") { 
    Import-module ITGlueAPI 
}
Else { 
    Install-Module ITGlueAPI -Force
    Import-Module ITGlueAPI
}
 
#Settings IT-Glue logon information
Add-ITGlueBaseURI -base_uri $APIEndpoint
Add-ITGlueAPIKey $APIKEy
 
function Get-WinADForestInformation {
    $Data = @{ }
    $ForestInformation = $(Get-ADForest)
    $Data.Forest = $ForestInformation
    $Data.RootDSE = $(Get-ADRootDSE -Properties *)
    $Data.ForestName = $ForestInformation.Name
    $Data.ForestNameDN = $Data.RootDSE.defaultNamingContext
    $Data.Domains = $ForestInformation.Domains
    $Data.ForestInformation = @{
        'Name'                    = $ForestInformation.Name
        'Root Domain'             = $ForestInformation.RootDomain
        'Forest Functional Level' = $ForestInformation.ForestMode
        'Domains Count'           = ($ForestInformation.Domains).Count
        'Sites Count'             = ($ForestInformation.Sites).Count
        'Domains'                 = ($ForestInformation.Domains) -join ", "
        'Sites'                   = ($ForestInformation.Sites) -join ", "
    }
     
    $Data.UPNSuffixes = Invoke-Command -ScriptBlock {
        $UPNSuffixList  =  [PSCustomObject] @{ 
                "Primary UPN" = $ForestInformation.RootDomain
                "UPN Suffixes"   = $ForestInformation.UPNSuffixes -join ","
            }  
        return $UPNSuffixList
    }
     
    $Data.GlobalCatalogs = $ForestInformation.GlobalCatalogs
    $Data.SPNSuffixes = $ForestInformation.SPNSuffixes
     
    $Data.Sites = Invoke-Command -ScriptBlock {
      $Sites = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest().Sites            
        $SiteData = foreach ($Site in $Sites) {          
          [PSCustomObject] @{ 
                "Site Name" = $site.Name
                "Subnets"   = ($site.Subnets) -join ", "
                "Servers" = ($Site.Servers) -join ", " 
            }  
        }
        Return $SiteData
    }
     
       
    $Data.FSMO = Invoke-Command -ScriptBlock {
        [PSCustomObject] @{ 
            "Domain" = $ForestInformation.RootDomain
            "Role"   = 'Domain Naming Master'
            "Holder" = $ForestInformation.DomainNamingMaster
        }

        [PSCustomObject] @{ 
            "Domain" = $ForestInformation.RootDomain
            "Role"   = 'Schema Master'
            "Holder" = $ForestInformation.SchemaMaster
        }
         
        foreach ($Domain in $ForestInformation.Domains) {
            $DomainFSMO = Get-ADDomain $Domain | Select-Object PDCEmulator, RIDMaster, InfrastructureMaster

            [PSCustomObject] @{ 
                "Domain" = $Domain
                "Role"   = 'PDC Emulator'
                "Holder" = $DomainFSMO.PDCEmulator
            } 

            
            [PSCustomObject] @{ 
                "Domain" = $Domain
                "Role"   = 'Infrastructure Master'
                "Holder" = $DomainFSMO.InfrastructureMaster
            } 

            [PSCustomObject] @{ 
                "Domain" = $Domain
                "Role"   = 'RID Master'
                "Holder" = $DomainFSMO.RIDMaster
            } 

        }
         
        Return $FSMO
    }
     
    $Data.OptionalFeatures = Invoke-Command -ScriptBlock {
        $OptionalFeatures = $(Get-ADOptionalFeature -Filter * )
        $Optional = @{
            'Recycle Bin Enabled'                          = ''
            'Privileged Access Management Feature Enabled' = ''
        }
        ### Fix Optional Features
        foreach ($Feature in $OptionalFeatures) {
            if ($Feature.Name -eq 'Recycle Bin Feature') {
                if ("$($Feature.EnabledScopes)" -eq '') {
                    $Optional.'Recycle Bin Enabled' = $False
                }
                else {
                    $Optional.'Recycle Bin Enabled' = $True
                }
            }
            if ($Feature.Name -eq 'Privileged Access Management Feature') {
                if ("$($Feature.EnabledScopes)" -eq '') {
                    $Optional.'Privileged Access Management Feature Enabled' = $False
                }
                else {
                    $Optional.'Privileged Access Management Feature Enabled' = $True
                }
            }
        }
        return $Optional
        ### Fix optional features
    }
    return $Data
}
 
$TableHeader = "&lt;table class=`"table table-bordered table-hover`" style=`"width:80%`">"
$Whitespace = "&lt;br/>"
$TableStyling = "&lt;th>", "&lt;th style=`"background-color:#4CAF50`">"
 
$RawAD = Get-WinADForestInformation
 
$ForestRawInfo = new-object PSCustomObject -property $RawAD.ForestInformation | convertto-html -Fragment | Select-Object -Skip 1
$ForestNice = $TableHeader + ($ForestRawInfo -replace $TableStyling) + $Whitespace
 
$SiteRawInfo = $RawAD.Sites | Select-Object 'Site Name', Servers, Subnets | ConvertTo-Html -Fragment | Select-Object -Skip 1
$SiteNice = $TableHeader + ($SiteRawInfo -replace $TableStyling) + $Whitespace
 
$OptionalRawFeatures = new-object PSCustomObject -property $RawAD.OptionalFeatures | convertto-html -Fragment | Select-Object -Skip 1
$OptionalNice = $TableHeader + ($OptionalRawFeatures -replace $TableStyling) + $Whitespace
 
$UPNRawFeatures = $RawAD.UPNSuffixes |  convertto-html -Fragment -as list| Select-Object -Skip 1
$UPNNice = $TableHeader + ($UPNRawFeatures -replace $TableStyling) + $Whitespace
 
$DCRawFeatures = $RawAD.GlobalCatalogs | ForEach-Object { Add-Member -InputObject $_ -Type NoteProperty -Name "Domain Controller" -Value $_; $_ } | convertto-html -Fragment | Select-Object -Skip 1
$DCNice = $TableHeader + ($DCRawFeatures -replace $TableStyling) + $Whitespace
 
$FSMORawFeatures = $RawAD.FSMO | convertto-html -Fragment | Select-Object -Skip 1
$FSMONice = $TableHeader + ($FSMORawFeatures -replace $TableStyling) + $Whitespace
 
$ForestFunctionalLevel = $RawAD.RootDSE.forestFunctionality
$DomainFunctionalLevel = $RawAD.RootDSE.domainFunctionality
$domaincontrollerMaxLevel = $RawAD.RootDSE.domainControllerFunctionality
 
$passwordpolicyraw = Get-ADDefaultDomainPasswordPolicy | Select-Object ComplexityEnabled, PasswordHistoryCount, LockoutDuration, LockoutThreshold, MaxPasswordAge, MinPasswordAge | convertto-html -Fragment -As List | Select-Object -skip 1
$passwordpolicyheader = "&lt;tr>&lt;th>&lt;b>Policy&lt;/b>&lt;/th>&lt;th>&lt;b>Setting&lt;/b>&lt;/th>&lt;/tr>"
$passwordpolicyNice = $TableHeader + ($passwordpolicyheader -replace $TableStyling) + ($passwordpolicyraw -replace $TableStyling) + $Whitespace
 
$adminsraw = Get-ADGroupMember "Domain Admins" | Select-Object SamAccountName, Name | convertto-html -Fragment | Select-Object -Skip 1
$adminsnice = $TableHeader + ($adminsraw -replace $TableStyling) + $Whitespace
 
$EnabledUsers = (Get-AdUser -filter * | Where-Object { $_.enabled -eq $true }).count
$DisabledUSers = (Get-AdUser -filter * | Where-Object { $_.enabled -eq $false }).count
$AdminUsers = (Get-ADGroupMember -Identity "Domain Admins").count
$Users = @"
There are &lt;b> $EnabledUsers &lt;/b> users Enabled&lt;br>
There are &lt;b> $DisabledUSers &lt;/b> users Disabled&lt;br>
There are &lt;b> $AdminUsers &lt;/b> Domain Administrator users&lt;br>
"@
 
$FlexAssetBody = @{
    type       = 'flexible-assets'
    attributes = @{
        traits = @{
            'domain-name'               = $RawAD.ForestName
            'forest-summary'            = $ForestNice
            'site-summary'              = $SiteNice
            'domain-controllers'        = $DCNice
            'fsmo-roles'                = $FSMONice
            'optional-features'         = $OptionalNice
            'upn-suffixes'              = $UPNNice
            'default-password-policies' = $passwordpolicyNice
            'domain-admins'             = $adminsnice
            'user-count'                = $Users
        }
    }
}
 
#Checking if the FlexibleAsset exists. If not, create a new one.
$FilterID = (Get-ITGlueFlexibleAssetTypes -filter_name $FlexAssetName).data
if (!$FilterID) { 
    $NewFlexAssetData = 
    @{
        type          = 'flexible-asset-types'
        attributes    = @{
            name        = $FlexAssetName
            icon        = 'sitemap'
            description = $description
        }
        relationships = @{
            "flexible-asset-fields" = @{
                data = @(
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order           = 1
                            name            = "Domain Name"
                            kind            = "Text"
                            required        = $true
                            "show-in-list"  = $true
                            "use-for-title" = $true
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 2
                            name           = "Forest Summary"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 3
                            name           = "Site Summary"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 4
                            name           = "Domain Controllers"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 5
                            name           = "FSMO Roles"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 6
                            name           = "Optional Features"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 7
                            name           = "UPN Suffixes"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 8
                            name           = "Default Password Policies"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 9
                            name           = "Domain Admins"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    },
                    @{
                        type       = "flexible_asset_fields"
                        attributes = @{
                            order          = 10
                            name           = "User Count"
                            kind           = "Textbox"
                            required       = $false
                            "show-in-list" = $false
                        }
                    }
                )
            }
        }
    }
    New-ITGlueFlexibleAssetTypes -Data $NewFlexAssetData 
    $FilterID = (Get-ITGlueFlexibleAssetTypes -filter_name $FlexAssetName).data
}
 
#Upload data to IT-Glue. We try to match the Server name to current computer name.
$ExistingFlexAsset = (Get-ITGlueFlexibleAssets -filter_flexible_asset_type_id $Filterid.id -filter_organization_id $orgID).data | Where-Object { $_.attributes.traits.'domain-name' -eq $RawAD.ForestName }
 
#If the Asset does not exist, we edit the body to be in the form of a new asset, if not, we just upload.
if (!$ExistingFlexAsset) {
    $FlexAssetBody.attributes.add('organization-id', $orgID)
    $FlexAssetBody.attributes.add('flexible-asset-type-id', $FilterID.id)
    Write-Host "Creating new flexible asset"
    New-ITGlueFlexibleAssets -data $FlexAssetBody
}
else {
    Write-Host "Updating Flexible Asset"
    $ExistingFlexAsset = $ExistingFlexAsset[-1]
    Set-ITGlueFlexibleAssets -id $ExistingFlexAsset.id  -data $FlexAssetBody
} 

this version of the script does the following:

  • It creates a Flexible Asset configuration in IT-Glue called ” Active Directory – AutoDoc”
  • It creates a Flexible Asset file in the supplied organisation($orgID).
  • The flexible asset file will be filled with the domain name, the forest summary, the site summary, domain controller, fsmo roles, optional features, upn suffixes, default password policies, the domain admins, and a user account.

Special thanks in this blog go out to Przemyslaw Klys for his Get-WinADForestInformation function, and to Jon Czerwinski at Chon Consulting Corp for assisting in some layout and ordering.

Generic version
 #Head for HTML file
$head = @"
&lt;Title>Server AD report&lt;/Title>
&lt;style>
body { background-color:#E5E4E2;
      font-family:Monospace;
      font-size:10pt; }
td, th { border:0px solid black; 
        border-collapse:collapse;
        white-space:pre; }
th { color:white;
    background-color:black; }
table, tr, td, th {
     padding: 2px; 
     margin: 0px;
     white-space:pre; }
tr:nth-child(odd) {background-color: lightgray}
table { width:95%;margin-left:5px; margin-bottom:20px; }
h2 {
font-family:Tahoma;
color:#6D7B8D;
}
.footer 
{ color:green; 
 margin-left:10px; 
 font-family:Tahoma;
 font-size:8pt;
 font-style:italic;
}
&lt;/style>
"@

function Get-WinADForestInformation {
    $Data = @{ }
    $ForestInformation = $(Get-ADForest)
    $Data.Forest = $ForestInformation
    $Data.RootDSE = $(Get-ADRootDSE -Properties *)
    $Data.ForestName = $ForestInformation.Name
    $Data.ForestNameDN = $Data.RootDSE.defaultNamingContext
    $Data.Domains = $ForestInformation.Domains
    $Data.ForestInformation = @{
        'Name'                    = $ForestInformation.Name
        'Root Domain'             = $ForestInformation.RootDomain
        'Forest Functional Level' = $ForestInformation.ForestMode
        'Domains Count'           = ($ForestInformation.Domains).Count
        'Sites Count'             = ($ForestInformation.Sites).Count
        'Domains'                 = ($ForestInformation.Domains) -join ", "
        'Sites'                   = ($ForestInformation.Sites) -join ", "
    }
     
    $Data.UPNSuffixes = Invoke-Command -ScriptBlock {
        $UPNSuffixList  =  [PSCustomObject] @{ 
                "Primary UPN" = $ForestInformation.RootDomain
                "UPN Suffixes"   = $ForestInformation.UPNSuffixes -join ","
            }  
        return $UPNSuffixList
    }
     
    $Data.GlobalCatalogs = $ForestInformation.GlobalCatalogs
    $Data.SPNSuffixes = $ForestInformation.SPNSuffixes
     
    $Data.Sites = Invoke-Command -ScriptBlock {
        $Sites = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest().Sites            
          $SiteData = foreach ($Site in $Sites) {          
            [PSCustomObject] @{ 
                  "Site Name" = $site.Name
                  "Subnets"   = ($site.Subnets) -join ", "
                  "Servers" = ($Site.Servers) -join ", " 
              }  
          }
          Return $SiteData
      }
     
    $Data.FSMO = Invoke-Command -ScriptBlock {
        [PSCustomObject] @{ 
            "Domain" = $ForestInformation.RootDomain
            "Role"   = 'Domain Naming Master'
            "Holder" = $ForestInformation.DomainNamingMaster
        }

        [PSCustomObject] @{ 
            "Domain" = $ForestInformation.RootDomain
            "Role"   = 'Schema Master'
            "Holder" = $ForestInformation.SchemaMaster
        }
         
        foreach ($Domain in $ForestInformation.Domains) {
            $DomainFSMO = Get-ADDomain $Domain | Select-Object PDCEmulator, RIDMaster, InfrastructureMaster
            [PSCustomObject] @{ 
                "Domain" = $Domain
                "Role"   = 'PDC Emulator'
                "Holder" = $DomainFSMO.PDCEmulator
            } 

            [PSCustomObject] @{ 
                "Domain" = $Domain
                "Role"   = 'PDC Emulator'
                "Holder" = $DomainFSMO.PDCEmulator
            } 

            
            [PSCustomObject] @{ 
                "Domain" = $Domain
                "Role"   = 'Infrastructure Master'
                "Holder" = $DomainFSMO.InfrastructureMaster
            } 

            [PSCustomObject] @{ 
                "Domain" = $Domain
                "Role"   = 'RID Master'
                "Holder" = $DomainFSMO.RIDMaster
            } 

        }
         
        Return $FSMO
    }
     
    $Data.OptionalFeatures = Invoke-Command -ScriptBlock {
        $OptionalFeatures = $(Get-ADOptionalFeature -Filter * )
        $Optional = @{
            'Recycle Bin Enabled'                          = ''
            'Privileged Access Management Feature Enabled' = ''
        }
        ### Fix Optional Features
        foreach ($Feature in $OptionalFeatures) {
            if ($Feature.Name -eq 'Recycle Bin Feature') {
                if ("$($Feature.EnabledScopes)" -eq '') {
                    $Optional.'Recycle Bin Enabled' = $False
                }
                else {
                    $Optional.'Recycle Bin Enabled' = $True
                }
            }
            if ($Feature.Name -eq 'Privileged Access Management Feature') {
                if ("$($Feature.EnabledScopes)" -eq '') {
                    $Optional.'Privileged Access Management Feature Enabled' = $False
                }
                else {
                    $Optional.'Privileged Access Management Feature Enabled' = $True
                }
            }
        }
        return $Optional
        ### Fix optional features
    }
    return $Data
}
 
$TableHeader = "&lt;table class=`"table table-bordered table-hover`" style=`"width:80%`">"
$Whitespace = "&lt;br/>"
$TableStyling = "&lt;th>", "&lt;th style=`"background-color:#4CAF50`">"
 
$RawAD = Get-WinADForestInformation
 
$ForestRawInfo = new-object PSCustomObject -property $RawAD.ForestInformation | convertto-html -Fragment | Select-Object -Skip 1
$ForestNice = $TableHeader + ($ForestRawInfo -replace $TableStyling) + $Whitespace
 
$SiteRawInfo = $RawAD.Sites | Select-Object 'Site Name', Servers, Subnets | ConvertTo-Html -Fragment | Select-Object -Skip 1
$SiteNice = $TableHeader + ($SiteRawInfo -replace $TableStyling) + $Whitespace
 
$OptionalRawFeatures = new-object PSCustomObject -property $RawAD.OptionalFeatures | convertto-html -Fragment | Select-Object -Skip 1
$OptionalNice = $TableHeader + ($OptionalRawFeatures -replace $TableStyling) + $Whitespace
 
$UPNRawFeatures = $RawAD.UPNSuffixes | convertto-html -Fragment | Select-Object -Skip 1
$UPNNice = $TableHeader + ($UPNRawFeatures -replace $TableStyling) + $Whitespace
 
$DCRawFeatures = $RawAD.GlobalCatalogs | ForEach-Object { Add-Member -InputObject $_ -Type NoteProperty -Name "Domain Controller" -Value $_; $_ } | convertto-html -Fragment | Select-Object -Skip 1
$DCNice = $TableHeader + ($DCRawFeatures -replace $TableStyling) + $Whitespace
 
$FSMORawFeatures = $RawAD.FSMO | convertto-html -Fragment | Select-Object -Skip 1
$FSMONice = $TableHeader + ($FSMORawFeatures -replace $TableStyling) + $Whitespace
 
$ForestFunctionalLevel = $RawAD.RootDSE.forestFunctionality
$DomainFunctionalLevel = $RawAD.RootDSE.domainFunctionality
$domaincontrollerMaxLevel = $RawAD.RootDSE.domainControllerFunctionality
 
$passwordpolicyraw = Get-ADDefaultDomainPasswordPolicy | Select-Object ComplexityEnabled, PasswordHistoryCount, LockoutDuration, LockoutThreshold, MaxPasswordAge, MinPasswordAge | convertto-html -Fragment -As List | Select-Object -skip 1
$passwordpolicyheader = "&lt;tr>&lt;th>&lt;b>Policy&lt;/b>&lt;/th>&lt;th>&lt;b>Setting&lt;/b>&lt;/th>&lt;/tr>"
$passwordpolicyNice = $TableHeader + ($passwordpolicyheader -replace $TableStyling) + ($passwordpolicyraw -replace $TableStyling) + $Whitespace
 
$adminsraw = Get-ADGroupMember "Domain Admins" | Select-Object SamAccountName, Name | convertto-html -Fragment | Select-Object -Skip 1
$adminsnice = $TableHeader + ($adminsraw -replace $TableStyling) + $Whitespace
 
$EnabledUsers = (Get-AdUser -filter * | Where-Object { $_.enabled -eq $true }).count
$DisabledUSers = (Get-AdUser -filter * | Where-Object { $_.enabled -eq $false }).count
$AdminUsers = (Get-ADGroupMember -Identity "Domain Admins").count
$Users = @"
There are &lt;b> $EnabledUsers &lt;/b> users Enabled&lt;br>
There are &lt;b> $DisabledUSers &lt;/b> users Disabled&lt;br>
There are &lt;b> $AdminUsers &lt;/b> Domain Administrator users&lt;br>
"@

$HTMLFile = @"
$head
&lt;b>Domain Name&lt;/b>: $($RawAD.ForestName) &lt;br>
&lt;br>
&lt;h1>Forest Configuration&lt;/h1> &lt;br>
$ForestNice
&lt;br>
&lt;h1>Site Summary&lt;/h1> &lt;br>
$SiteNice
&lt;br>
&lt;h1>Domain Controllers&lt;/h1> &lt;br>
$DCNice
&lt;br>
&lt;h1>FSMO Roles&lt;/h1>
$FSMONice
&lt;h1>Optional Features&lt;/h1>
$OptionalNice
&lt;br>
&lt;h1>UPN Suffixes&lt;/h1>
$UPNNice
&lt;br>
&lt;h1>Password Policies&lt;/h1>
$passwordpolicyNice
&lt;br>
&lt;h1>Domain Admins&lt;/h1>
$adminsnice
&lt;br>
&lt;h1>Domain Admins&lt;/h1>
$Users
&lt;br>
"@
$HTMLFile | out-file C:\Temp\ServerDoc.html 

And that’s it. If you want more local documentation without the IT-Glue component you should check out this blog too. as always, Happy PowerShelling.

Update: After some comments from PrzemysŇāaw KŇāys I’ve updated the scripts to be a little more efficient and mostly prettier ūüôā

Documenting with PowerShell: Documenting SQL settings and databases

Most of our clients have some form of line of business application that requires a database engine. in 99% of the cases this ends up being a SQL server. I always enjoy being in complete control of an environment so whenever we deploy SQL servers we automatically run this documentation script. This is especially good if you ever need to recreate databases, or need to check what the state of a SQL server was a couple of weeks ago.

So, for this script we use the SQLPS module which is included on any server with SQL Server 2012+ installed. The SQLPS module gives us a PSDrive with the SQLSERVER:\ path. This allows us to grab all information we need.

The script documents the existing databases, their settings, file locations, but also generic server settings. It automatically finds all instances on the server so in the case of multiple SQL instances you’re also covered by this script ūüôā

IT-Glue script
#####################################################################
$APIKEy = "ITGLUEAPIKEY"
$APIEndpoint = "https://api.eu.itglue.com"
$orgID = "ORGIDHERE"
$FlexAssetName = "ITGLue AutoDoc - SQL Server"
$Description = "SQL Server settings and configuration, Including databases."
#####################################################################
If (Get-Module -ListAvailable -Name "ITGlueAPI") { Import-module ITGlueAPI } Else { install-module ITGlueAPI -Force; import-module ITGlueAPI }
#Settings IT-Glue logon information
Add-ITGlueBaseURI -base_uri $APIEndpoint
Add-ITGlueAPIKey $APIKEy
#Collect Data
import-module SQLPS
$Instances = Get-ChildItem "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)"
foreach ($Instance in $Instances) {
    $databaseList = get-childitem "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)\$($Instance.Displayname)\Databases"
    $Databases = @()
    foreach ($Database in $databaselist) {
        $Databaseobj = New-Object -TypeName PSObject
        $Databaseobj | Add-Member -MemberType NoteProperty -Name "Name" -value $Database.Name
        $Databaseobj | Add-Member -MemberType NoteProperty -Name "Status" -value $Database.status
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "RecoveryModel" -value $Database.RecoveryModel
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "LastBackupDate" -value $Database.LastBackupDate
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "DatabaseFiles" -value $database.filegroups.files.filename
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "Logfiles"      -value $database.LogFiles.filename
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "MaxSize" -value $database.filegroups.files.MaxSize
        $Databases += $Databaseobj
    }
    $InstanceInfo = $Instance | Select-Object DisplayName, Collation, AuditLevel, BackupDirectory, DefaultFile, DefaultLog, Edition, ErrorLogPath | convertto-html -PreContent "&lt;h1>Settings&lt;/h1>" -Fragment | Out-String
    $Instanceinfo = $instanceinfo -replace "&lt;th>", "&lt;th style=`"background-color:#4CAF50`">"
    $InstanceInfo = $InstanceInfo -replace "&lt;table>", "&lt;table class=`"table table-bordered table-hover`" style=`"width:80%`">"
    $DatabasesHTML = $Databases | ConvertTo-Html -fragment -PreContent "&lt;h3>Database Settings&lt;/h3>" | Out-String
    $DatabasesHTML = $DatabasesHTML -replace "&lt;th>", "&lt;th style=`"background-color:#4CAF50`">"
    $DatabasesHTML = $DatabasesHTML -replace "&lt;table>", "&lt;table class=`"table table-bordered table-hover`" style=`"width:80%`">"



    #Tagging devices
    $DeviceAsset = @()
    If ($TagRelatedDevices -eq $true) {
        Write-Host "Finding all related resources - Based on computername: $ENV:COMPUTERNAME"
        foreach ($hostfound in $networkscan | Where-Object { $_.Ping -ne $false }) {
            $DeviceAsset += (Get-ITGlueConfigurations -page_size "1000" -filter_name $ENV:COMPUTERNAME -organization_id $orgID).data 
        }
    }     
    $FlexAssetBody = 
    @{
        type       = 'flexible-assets'
        attributes = @{
            name   = $FlexAssetName
            traits = @{
                "instance-name"     = "$($ENV:COMPUTERNAME)\$($Instance.displayname)"
                "instance-settings" = $InstanceInfo
                "databases"         = $DatabasesHTML
                "tagged-devices"    = $DeviceAsset.ID
                    
            }
        }
    }
    #Checking if the FlexibleAsset exists. If not, create a new one.
    $FilterID = (Get-ITGlueFlexibleAssetTypes -filter_name $FlexAssetName).data
    if (!$FilterID) { 
        $NewFlexAssetData = 
        @{
            type          = 'flexible-asset-types'
            attributes    = @{
                name        = $FlexAssetName
                icon        = 'sitemap'
                description = $description
            }
            relationships = @{
                "flexible-asset-fields" = @{
                    data = @(
                        @{
                            type       = "flexible_asset_fields"
                            attributes = @{
                                order           = 1
                                name            = "Instance Name"
                                kind            = "Text"
                                required        = $true
                                "show-in-list"  = $true
                                "use-for-title" = $true
                            }
                        },
                        @{
                            type       = "flexible_asset_fields"
                            attributes = @{
                                order          = 2
                                name           = "Instance Settings"
                                kind           = "Textbox"
                                required       = $false
                                "show-in-list" = $true
                            }
                        },
                        @{
                            type       = "flexible_asset_fields"
                            attributes = @{
                                order          = 3
                                name           = "Databases"
                                kind           = "Textbox"
                                required       = $false
                                "show-in-list" = $false
                            }
                        },
                        @{
                            type       = "flexible_asset_fields"
                            attributes = @{
                                order          = 8
                                name           = "Tagged Devices"
                                kind           = "Tag"
                                "tag-type"     = "Configurations"
                                required       = $false
                                "show-in-list" = $false
                            }
                        }
                    )
                }
            }
                  
        }
        New-ITGlueFlexibleAssetTypes -Data $NewFlexAssetData 
        $FilterID = (Get-ITGlueFlexibleAssetTypes -filter_name $FlexAssetName).data
    } 
    #Upload data to IT-Glue. We try to match the Server name to current computer name.
    $ExistingFlexAsset = (Get-ITGlueFlexibleAssets -filter_flexible_asset_type_id $Filterid.id -filter_organization_id $orgID).data | Where-Object { $_.attributes.traits.'instance-name' -eq "$($ENV:COMPUTERNAME)\$($Instance.displayname)" }
    #If the Asset does not exist, we edit the body to be in the form of a new asset, if not, we just upload.
    if (!$ExistingFlexAsset) {
        $FlexAssetBody.attributes.add('organization-id', $orgID)
        $FlexAssetBody.attributes.add('flexible-asset-type-id', $FilterID.id)
        Write-Host "Creating new flexible asset"
        New-ITGlueFlexibleAssets -data $FlexAssetBody
    }
    else {
        Write-Host "Updating Flexible Asset"
        $ExistingFlexAsset = $ExistingFlexAsset[-1]
        Set-ITGlueFlexibleAssets -id $ExistingFlexAsset.id  -data $FlexAssetBody
    }
}
Generic version

As always I’ve included a generic version. You can use this with any other system.

import-module SQLPS
$Instances = Get-ChildItem "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)"
foreach ($Instance in $Instances) {
    $InstanceInfo = $Instance | Select-Object DisplayName, Collation, AuditLevel, BackupDirectory, DefaultFile, DefaultLog, Edition, ErrorLogPath
    $databaseList = get-childitem "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)\$($Instance.Displayname)\Databases"
    $Databases = @()
    foreach($Database in $databaselist){
        $Databaseobj = New-Object -TypeName PSObject
        $Databaseobj | Add-Member -MemberType NoteProperty -Name "Name" -value $Database.Name
        $Databaseobj | Add-Member -MemberType NoteProperty -Name "Status" -value $Database.status
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "RecoveryModel" -value $Database.RecoveryModel
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "LastBackupDate" -value $Database.LastBackupDate
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "DatabaseFiles" -value $database.filegroups.files.filename
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "Logfiles"      -value $database.LogFiles.filename
        $Databaseobj | Add-Member -MemberType NoteProperty -Name  "MaxSize" -value $database.filegroups.files.MaxSize
        $Databases += $Databaseobj
    }
    $InstanceInfo = $Instance | Select-Object DisplayName, Collation, AuditLevel, BackupDirectory, DefaultFile, DefaultLog, Edition, ErrorLogPath | convertto-html -PreContent "&lt;h1>Settings&lt;/h1>" -Fragment | Out-String
    $Instanceinfo = $instanceinfo -replace "&lt;th>", "&lt;th style=`"background-color:#4CAF50`">"
    $InstanceInfo = $InstanceInfo -replace "&lt;table>", "&lt;table class=`"table table-bordered table-hover`" style=`"width:80%`">"
    $DatabasesHTML = $Databases | ConvertTo-Html -fragment -PreContent "&lt;h3>Database Settings&lt;/h3>" | Out-String
    $DatabasesHTML = $DatabasesHTML -replace "&lt;th>", "&lt;th style=`"background-color:#4CAF50`">"
    $DatabasesHTML = $DatabasesHTML -replace "&lt;table>", "&lt;table class=`"table table-bordered table-hover`" style=`"width:80%`">"

    $output = $InstanceInfo,$DatabasesHTML | out-file "C:\Temp\Output.html"

}

a small warning: it seems that the latest wordpress updates makes the < symbol appear as its html encoded version in the code. Visual Studio Code automatically converts this. if you are using any other IDE replace this yourself.

And that’s it! as always, Happy PowerShelling!

Monitoring and Documenting with PowerShell: End of year review

Hi! So this is the final post of this year. I’m going to be enjoying some well deserved holidays and spend Christmas with my family. The past year has been pretty cool. I’ve been doing so many cool projects.

I figured I also would list the top blogs of this year by views, and just generally some stuff I’m proud of, so lets get started:

Top blogs

The most viewed blog this year is my Functional PowerShell for MSPs webinar, which is pretty amazing because it was only posted 3 months ago. I still see the views racking up on the Teams Live Event recording and I am going to be giving another (albeit slightly shorter one) the 16th of december.

The runner up in this is the start of the Documenting with PowerShell series. That entire series seems to have been a favorite for most people. The third place is going to the unofficial IT-Glue backup script.

My personal favorite has to be a recent blog; either the Secure Application Model blog or the OneDrive monitoring script which uses user impersonation.

Documenting with PowerShell series

The documenting with PowerShell series has been a hit. I’ve taken a small break from it to reorganise and make it a little more “Eye-candy” focussed as this was the primary question I’ve been getting. I love how some of you have adapted the scripts. Most of them were made as an example so it’s cool to see all different variations of it. I will be continuing this series at the start of next year. If you have any wishes, let me know!

Monitoring with PowerShell series

The monitoring with PowerShell series is still my baby, I love doing it and showing all the different methods of using PowerShell over SNMP, or PowerShell over generic WMI monitoring. Next year I hope to still be posting at least 1 blog a week. I’ve recently been mailed some question that I will be picking up next year too.

Special thanks

This year was great! I especially want to thank my peers in MSP’r’Us that always help me find new ideas. I also want to thank Datto for the way we have been collaborating and adding my blogs and script to their product. It’s been a great adventure.

And that’s it! I wish all my readers an amazing Christmas, and of course a happy new year. As always, Happy PowerShelling!

Monitoring with PowerShell: User Recycle bin Remediation

I deploy a lot of environments where there is some form of folder redirection – be it classical folder redirection using a GPO or UPD on Windows Virtual Desktop, or even Known Folder Redirection using OneDrive. The benefits to using these forms of folder redirection is clear, but comes with another cool feature; The recycle bin is often redirected too. This is great because each user has his own recycler this way and we never have to worry about anyone seeing files or folders from anyone else.

The downside to this is that it can quickly eat away at disk space you don’t want to lose, Which is why I’ve built the following component, you can run this from your RMM system, put it in a logon script or logoff script in your GPO, or just run it on demand.

You can modify $days to set to any amount, 0 means it will clear the entire recycle bin for that user – Something we often don’t like doing as accidental deletes can happen.

$Days = 14
$Shell = New-Object -ComObject Shell.Application
$Global:Recycler = $Shell.NameSpace(0xa)
foreach ($item in $Recycler.Items()) {
    $DateDel = $Recycler.GetDetailsOf($item, 2) -replace "\u200f|\u200e", "" | get-date
    If ($DateDel -lt (Get-Date).AddDays(-$Days)) { Remove-Item -Path $item.Path -Confirm:$false -Force -Recurse }
} 

And that’s it! as always, Happy PowerShelling!

Monitoring with PowerShell: Alerting on large Office 365 mailboxes

This script is one we’ve used in the past as a sales tool – Some companies tend to use their mailbox as a storage location more than just a mailbox. They save large attachments, use it as a personal CRM system or even just really like sending eachother large PDFs ūüėČ

When mailboxes get too large your users will start experiencing performance or caching issues. It’s also just not a good practice to have huge mailboxes, just imagine you’ll want to work on a Remote Desktop or Windows Virtual Desktop server with a 60GB mailbox cached…

Anyway; to make sure that when users experience large growth in mailboxes I’ve been using the following monitoring set in our N-central RMM system. This monitoring script alerts whenever a user has a mailbox larger than 60GB. As always I’ve included two scripts: one for a single tenant, one for multiple tenants. As always, my scripts are using the Secure Application Model.

Multiple tenant script

$ApplicationId         = 'xxxx-xxxx-xxx-xxxx-xxxx'
$ApplicationSecret     = 'TheSecretTheSecret' | Convertto-SecureString -AsPlainText -Force
$TenantID              = 'YourTenantID'
$RefreshToken          = 'RefreshToken'
$ExchangeRefreshToken  = 'ExchangeRefreshToken'
$upn                   = 'UPN-Used-To-Generate-Tokens'
$SizeToMonitor         = 60

$credential = New-Object System.Management.Automation.PSCredential($ApplicationId, $ApplicationSecret)
$aadGraphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.windows.net/.default' -ServicePrincipal -Tenant $tenantID 
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $tenantID 
Connect-MsolService -AdGraphAccessToken $aadGraphToken.AccessToken -MsGraphAccessToken $graphToken.AccessToken
$customers = Get-MsolPartnerContract -All
$LargeMailboxes = @()
foreach ($customer in $customers) {
    write-host "Getting started for $($Customer.name)" -foregroundcolor green
    $token = New-PartnerAccessToken -ApplicationId 'a0c73c16-a7e3-4564-9a95-2bdf47383716'-RefreshToken $ExchangeRefreshToken -Scopes 'https://outlook.office365.com/.default' -Tenant $customer.TenantId
    $tokenValue = ConvertTo-SecureString "Bearer $($token.AccessToken)" -AsPlainText -Force
    $credential = New-Object System.Management.Automation.PSCredential($upn, $tokenValue)
    $customerId = $customer.DefaultDomainName
    $session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://ps.outlook.com/powershell-liveid?DelegatedOrg=$($customerId)&amp;BasicAuthToOAuthConversion=true" -Credential $credential -Authentication Basic -AllowRedirection
    Import-PSSession $session -allowclobber -Disablenamechecking
    $Mailboxes = Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName, @{name = "TotalItemSize (GB)"; expression = { [math]::Round((($_.TotalItemSize.Value.ToString()).Split("(")[1].Split(" ")[0].Replace(",", "") / 1GB), 2) } }, ItemCount | Sort "TotalItemSize (GB)" -Descending
    foreach ($Mailbox in $Mailboxes) { if ($Mailbox.'TotalItemSize (GB)' -gt  $SizeToMonitor) { $LargeMailboxes += $Mailbox } }
    Remove-PSSession $session
}

if (!$LargeMailboxes) { "No Large mailboxes found" }

Single Tenant Script

$ApplicationId         = 'xxxx-xxxx-xxx-xxxx-xxxx'
$ApplicationSecret     = 'TheSecretTheSecrey' | Convertto-SecureString -AsPlainText -Force
$TenantID              = 'YourTenantID'
$RefreshToken          = 'RefreshToken'
$ExchangeRefreshToken  = 'ExchangeRefreshToken'
$upn                   = 'UPN-Used-To-Generate-Tokens'
$customertenant        = 'CustomerTenant.onmicrosoft.com'
$SizeToMonitor         = 60 

$credential = New-Object System.Management.Automation.PSCredential($ApplicationId, $ApplicationSecret)
$aadGraphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.windows.net/.default' -ServicePrincipal -Tenant $tenantID 
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $tenantID 
Connect-MsolService -AdGraphAccessToken $aadGraphToken.AccessToken -MsGraphAccessToken $graphToken.AccessToken
$LargeMailboxes = @()

    write-host "Getting Large mailboxes" -ForegroundColor green
    $token = New-PartnerAccessToken -ApplicationId 'a0c73c16-a7e3-4564-9a95-2bdf47383716'-RefreshToken $ExchangeRefreshToken -Scopes 'https://outlook.office365.com/.default' -Tenant $customertenant
    $tokenValue = ConvertTo-SecureString "Bearer $($token.AccessToken)" -AsPlainText -Force
    $credential = New-Object System.Management.Automation.PSCredential($upn, $tokenValue)
    $customerId = $customertenant
    $session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://ps.outlook.com/powershell-liveid?DelegatedOrg=$($customerId)&amp;BasicAuthToOAuthConversion=true" -Credential $credential -Authentication Basic -AllowRedirection
    Import-PSSession $session -allowclobber -Disablenamechecking
    $Mailboxes = Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName, @{name = "TotalItemSize (GB)"; expression = { [math]::Round((($_.TotalItemSize.Value.ToString()).Split("(")[1].Split(" ")[0].Replace(",", "") / 1GB), 2) } }, ItemCount | Sort "TotalItemSize (GB)" -Descending
    foreach ($Mailbox in $Mailboxes) { if ($Mailbox.'TotalItemSize (GB)' -gt  $SizeToMonitor  { $LargeMailboxes += $Mailbox } }
    Remove-PSSession $session

if (!$LargeMailboxes) { "No Large mailboxes found" } 

And that’s it! as always, Happy PowerShelling.

Ps: I’m giving a new PowerShell webinar soon. Join me by clicking this link.

Monitoring with PowerShell: Alerting on Shodan results

This is a bit of a short script again – but that’s just because sometimes life can made be real simple. Shodan is a tool that scans the entire internet and documents which open ports are available, if it is vulnerable for specific CVE’s, and lots of cool other stuff explained here.

We’ve seen some MSP’s offer a simple Shodan query and selling it as a “Dark Web Scan” – Please note that this is absolutely not a comprehensive scan and finding online exposed services is not always such a big deal, for example in controlled environments.

The script I’ve made is one we run at our clients on IP addresses where we know nothing should be listed in Shodan, networks that should not have exposed services, or just IP addresses where we want to alert on changes. Simply change the list of IPs to the list you would like to monitor.

$APIKEY = "YourShodanAPIKey"
$CurrentIP = (Invoke-WebRequest -uri "http://ifconfig.me/ip" -UseBasicParsing ).Content
$ListIPs = @("1.1.1.1","2.2.2.2",$CurrentIP)
foreach($ip in $ListIPs){
   $Shodan = Invoke-RestMethod -uri "https://api.shodan.io/shodan/host/$($ip)?key=$APIKEY"
}
if(!$Shodan) { $HealthState = "Healthy"} else { $HealthState = "Alert - $($Shodan.ip_str) is found in Shodan."} 

We also like running these scripts at our prospects as a part of a security survey, because if Shodan has found external services such as RDP on a different port it often shows bad security practices as a whole.

Getting a Shodan subscription is absolutely worth it because it gives you that little bit more of visibility on how exposed you actually. Anyway, as always happy PowerShelling!

Monitoring with PowerShell: Monitoring OneDrive status for current logged on user!

Since the release of Onedrive and Onedrive for business, a lot of system administrators have been trying to figure out how to monitor the onedrive status. Rodney Viana at Microsoft made a pretty awesome module to be able to get the current OneDrive Sync status, you can find that module here.

The issue with this module is that it has to run under the current logged on user, You don’t always have the ability to do that, especially when using RMM systems that always use the NT AUTHORITY\SYSTEM account. Now PowerShell has the ability to load .NET components as code and execute them, this gave me the idea to use impersonation of the current user in my PowerShell script to monitor OneDrive.

After messing around trying to build my own, a friend of mine pointed me to Roger Zanders post here. Combining these two scripts was pretty simple and resolved the entire onedrive monitoring issue for me.

So without any more ado; I introduce the CyberDrain OneDrive Status monitoring script for RMM systems. The script downloads the latest version of the OneDriveLib.dll, runs Get-ODStatus cmdlet under the current user, and returns the state of the OneDrive sync in $ODErrors.

The script

$Source = @"
using System;  
using System.Runtime.InteropServices;

namespace murrayju.ProcessExtensions  
{
    public static class ProcessExtensions
    {
        #region Win32 Constants

        private const int CREATE_UNICODE_ENVIRONMENT = 0x00000400;
        private const int CREATE_NO_WINDOW = 0x08000000;

        private const int CREATE_NEW_CONSOLE = 0x00000010;

        private const uint INVALID_SESSION_ID = 0xFFFFFFFF;
        private static readonly IntPtr WTS_CURRENT_SERVER_HANDLE = IntPtr.Zero;

        #endregion

        #region DllImports

        [DllImport("advapi32.dll", EntryPoint = "CreateProcessAsUser", SetLastError = true, CharSet = CharSet.Ansi, CallingConvention = CallingConvention.StdCall)]
        private static extern bool CreateProcessAsUser(
            IntPtr hToken,
            String lpApplicationName,
            String lpCommandLine,
            IntPtr lpProcessAttributes,
            IntPtr lpThreadAttributes,
            bool bInheritHandle,
            uint dwCreationFlags,
            IntPtr lpEnvironment,
            String lpCurrentDirectory,
            ref STARTUPINFO lpStartupInfo,
            out PROCESS_INFORMATION lpProcessInformation);

        [DllImport("advapi32.dll", EntryPoint = "DuplicateTokenEx")]
        private static extern bool DuplicateTokenEx(
            IntPtr ExistingTokenHandle,
            uint dwDesiredAccess,
            IntPtr lpThreadAttributes,
            int TokenType,
            int ImpersonationLevel,
            ref IntPtr DuplicateTokenHandle);

        [DllImport("userenv.dll", SetLastError = true)]
        private static extern bool CreateEnvironmentBlock(ref IntPtr lpEnvironment, IntPtr hToken, bool bInherit);

        [DllImport("userenv.dll", SetLastError = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool DestroyEnvironmentBlock(IntPtr lpEnvironment);

        [DllImport("kernel32.dll", SetLastError = true)]
        private static extern bool CloseHandle(IntPtr hSnapshot);

        [DllImport("kernel32.dll")]
        private static extern uint WTSGetActiveConsoleSessionId();

        [DllImport("Wtsapi32.dll")]
        private static extern uint WTSQueryUserToken(uint SessionId, ref IntPtr phToken);

        [DllImport("wtsapi32.dll", SetLastError = true)]
        private static extern int WTSEnumerateSessions(
            IntPtr hServer,
            int Reserved,
            int Version,
            ref IntPtr ppSessionInfo,
            ref int pCount);

        #endregion

        #region Win32 Structs

        private enum SW
        {
            SW_HIDE = 0,
            SW_SHOWNORMAL = 1,
            SW_NORMAL = 1,
            SW_SHOWMINIMIZED = 2,
            SW_SHOWMAXIMIZED = 3,
            SW_MAXIMIZE = 3,
            SW_SHOWNOACTIVATE = 4,
            SW_SHOW = 5,
            SW_MINIMIZE = 6,
            SW_SHOWMINNOACTIVE = 7,
            SW_SHOWNA = 8,
            SW_RESTORE = 9,
            SW_SHOWDEFAULT = 10,
            SW_MAX = 10
        }

        private enum WTS_CONNECTSTATE_CLASS
        {
            WTSActive,
            WTSConnected,
            WTSConnectQuery,
            WTSShadow,
            WTSDisconnected,
            WTSIdle,
            WTSListen,
            WTSReset,
            WTSDown,
            WTSInit
        }

        [StructLayout(LayoutKind.Sequential)]
        private struct PROCESS_INFORMATION
        {
            public IntPtr hProcess;
            public IntPtr hThread;
            public uint dwProcessId;
            public uint dwThreadId;
        }

        private enum SECURITY_IMPERSONATION_LEVEL
        {
            SecurityAnonymous = 0,
            SecurityIdentification = 1,
            SecurityImpersonation = 2,
            SecurityDelegation = 3,
        }

        [StructLayout(LayoutKind.Sequential)]
        private struct STARTUPINFO
        {
            public int cb;
            public String lpReserved;
            public String lpDesktop;
            public String lpTitle;
            public uint dwX;
            public uint dwY;
            public uint dwXSize;
            public uint dwYSize;
            public uint dwXCountChars;
            public uint dwYCountChars;
            public uint dwFillAttribute;
            public uint dwFlags;
            public short wShowWindow;
            public short cbReserved2;
            public IntPtr lpReserved2;
            public IntPtr hStdInput;
            public IntPtr hStdOutput;
            public IntPtr hStdError;
        }

        private enum TOKEN_TYPE
        {
            TokenPrimary = 1,
            TokenImpersonation = 2
        }

        [StructLayout(LayoutKind.Sequential)]
        private struct WTS_SESSION_INFO
        {
            public readonly UInt32 SessionID;

            [MarshalAs(UnmanagedType.LPStr)]
            public readonly String pWinStationName;

            public readonly WTS_CONNECTSTATE_CLASS State;
        }

        #endregion

        // Gets the user token from the currently active session
        private static bool GetSessionUserToken(ref IntPtr phUserToken)
        {
            var bResult = false;
            var hImpersonationToken = IntPtr.Zero;
            var activeSessionId = INVALID_SESSION_ID;
            var pSessionInfo = IntPtr.Zero;
            var sessionCount = 0;

            // Get a handle to the user access token for the current active session.
            if (WTSEnumerateSessions(WTS_CURRENT_SERVER_HANDLE, 0, 1, ref pSessionInfo, ref sessionCount) != 0)
            {
                var arrayElementSize = Marshal.SizeOf(typeof(WTS_SESSION_INFO));
                var current = pSessionInfo;

                for (var i = 0; i < sessionCount; i++)
                {
                    var si = (WTS_SESSION_INFO)Marshal.PtrToStructure((IntPtr)current, typeof(WTS_SESSION_INFO));
                    current += arrayElementSize;

                    if (si.State == WTS_CONNECTSTATE_CLASS.WTSActive)
                    {
                        activeSessionId = si.SessionID;
                    }
                }
            }

            // If enumerating did not work, fall back to the old method
            if (activeSessionId == INVALID_SESSION_ID)
            {
                activeSessionId = WTSGetActiveConsoleSessionId();
            }

            if (WTSQueryUserToken(activeSessionId, ref hImpersonationToken) != 0)
            {
                // Convert the impersonation token to a primary token
                bResult = DuplicateTokenEx(hImpersonationToken, 0, IntPtr.Zero,
                    (int)SECURITY_IMPERSONATION_LEVEL.SecurityImpersonation, (int)TOKEN_TYPE.TokenPrimary,
                    ref phUserToken);

                CloseHandle(hImpersonationToken);
            }

            return bResult;
        }

        public static bool StartProcessAsCurrentUser(string appPath, string cmdLine = null, string workDir = null, bool visible = true)
        {
            var hUserToken = IntPtr.Zero;
            var startInfo = new STARTUPINFO();
            var procInfo = new PROCESS_INFORMATION();
            var pEnv = IntPtr.Zero;
            int iResultOfCreateProcessAsUser;

            startInfo.cb = Marshal.SizeOf(typeof(STARTUPINFO));

            try
            {
                if (!GetSessionUserToken(ref hUserToken))
                {
                    throw new Exception("StartProcessAsCurrentUser: GetSessionUserToken failed.");
                }

                uint dwCreationFlags = CREATE_UNICODE_ENVIRONMENT | (uint)(visible ? CREATE_NEW_CONSOLE : CREATE_NO_WINDOW);
                startInfo.wShowWindow = (short)(visible ? SW.SW_SHOW : SW.SW_HIDE);
                startInfo.lpDesktop = "winsta0\\default";

                if (!CreateEnvironmentBlock(ref pEnv, hUserToken, false))
                {
                    throw new Exception("StartProcessAsCurrentUser: CreateEnvironmentBlock failed.");
                }

                if (!CreateProcessAsUser(hUserToken,
                    appPath, // Application Name
                    cmdLine, // Command Line
                    IntPtr.Zero,
                    IntPtr.Zero,
                    false,
                    dwCreationFlags,
                    pEnv,
                    workDir, // Working directory
                    ref startInfo,
                    out procInfo))
                {
                    throw new Exception("StartProcessAsCurrentUser: CreateProcessAsUser failed.\n");
                }

                iResultOfCreateProcessAsUser = Marshal.GetLastWin32Error();
            }
            finally
            {
                CloseHandle(hUserToken);
                if (pEnv != IntPtr.Zero)
                {
                    DestroyEnvironmentBlock(pEnv);
                }
                CloseHandle(procInfo.hThread);
                CloseHandle(procInfo.hProcess);
            }
            return true;
        }
    }
}


"@
New-Item 'C:\programdata\Microsoft OneDrive' -ItemType directory -Force -ErrorAction SilentlyContinue
Invoke-WebRequest -Uri 'https://raw.githubusercontent.com/rodneyviana/ODSyncService/master/Binaries/PowerShell/OneDriveLib.dll' -OutFile 'C:\programdata\Microsoft OneDrive\OneDriveLib.dll'

Add-Type -ReferencedAssemblies 'System', 'System.Runtime.InteropServices' -TypeDefinition $Source -Language CSharp 
$scriptblock = {
    Unblock-File 'C:\programdata\Microsoft OneDrive\OneDriveLib.dll'
    import-module 'C:\programdata\Microsoft OneDrive\OneDriveLib.dll'
    $ODStatus = Get-ODStatus | convertto-json | out-file 'C:\programdata\Microsoft OneDrive\OneDriveLogging.txt'
}


[murrayju.ProcessExtensions.ProcessExtensions]::StartProcessAsCurrentUser("C:\Windows\System32\WindowsPowershell\v1.0\Powershell.exe", "-command $($scriptblock)","C:\Windows\System32\WindowsPowershell\v1.0",$false)
start-sleep 5
$ErrorList = @("NotInstalled", "ReadOnly", "Error", "OndemandOrUnknown")
$ODStatus = (get-content "C:\programdata\Microsoft OneDrive\OneDriveLogging.txt" | convertfrom-json).value
foreach ($ODStat in $ODStatus) {
    if ($ODStat.StatusString -in $ErrorList) { $ODerrors = "$($ODStat.LocalPath) is in state $($ODStat.StatusString)" }
}
if (!$ODerrors) {
    $ODerrors = "Healthy"
}

 

And that’s it! I’m quite proud of this one as I have seen a lot of people struggle with it so I hope it helps. As always, Happy PowerShelling!