Dashboard EPM with psWriteHTML

Published by david on

PowerShell script setup and execution process for creating automated HTML reports using the PSWriteHTML module.

Install PSWriteHTML Module

The PSWriteHTML module is a powerful tool for creating HTML reports directly from PowerShell. It’s particularly useful for automating report generation, providing a visual representation of data from various sources like SQL databases. For more details, you can visit the GitHub – EvotecIT/PSWriteHTML.

?
1
Install-Module PSWriteHTML

PowerShell Script Overview

This guide demonstrates how to use PowerShell to connect to a SQL database, run queries, and generate an HTML report using the PSWriteHTML module. The process involves several key steps: importing the module, establishing a database connection, running SQL queries, and generating HTML output.

Import Module

?
1
2
Write-Host "--- Importing PSWriteHTML module"
Import-Module -Name PSWriteHTML

Connect to SQL Database

To connect to a SQL database, you’ll need to provide the data source, credentials, and target database. Here’s a sample script to establish a connection, handling errors gracefully with a try-catch block:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$Exporthtml = "C:\temp\default.htm"
$dataSource = "epm2024.monlab.lan"
$user = "sa"
 
# Best practice: use encrypted passwords
# $password = ConvertTo-SecureString -String "your_encrypted_password"
# $creds = New-Object -TypeName System.Management.Automation.PsCredential -ArgumentList ($user, $password)
 
# Example with plain text password (not recommended)
$password = "Password1"
$creds = New-Object -TypeName System.Management.Automation.PsCredential -ArgumentList ($user, (ConvertTo-SecureString -String $password -AsPlainText -Force))
$database = "EPM"
 
Write-Host "--- Connecting to SQL"
$PassSQL = $creds.GetNetworkCredential().Password
 
# Connecting to the database
try {
    $connectionString = "Server=$dataSource;uid=$user;pwd=$PassSQL;Database=$database;Integrated Security=False;"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()
    Write-Host "------ Connected to Database: $database"
} catch {
    Write-Host "------ Error connecting to Database: $database"
    exit
}

This snippet uses a try-catch block to handle potential connection errors gracefully. The $connectionString is configured with server details, credentials, and database information.

SQL Query

View Convert a EPM query into an SQL query to easy create a SQL query

After establishing a connection, we execute SQL queries to fetch data. We use a custom function Get-SqlData to streamline this process, which executes the query and loads the results into a PowerShell data table.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
####################### DATA QUERIES ###############
# Function to execute SQL queries and load results into a PowerShell data table
function Get-SqlData($connection, $query) {
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $result = $command.ExecuteReader()
    $table = New-Object System.Data.DataTable
    $table.Load($result)
    return $table
}
 
# Query for Application1
$Application1 = @()
$query = "SELECT DISTINCT A0.DISPLAYNAME, A1.SUITENAME FROM Computer A0 (nolock)
          LEFT OUTER JOIN AppSoftwareSuites A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn
          WHERE (A1.SUITENAME LIKE N'%Visual C++ 2022 X86%')
          ORDER BY A0.DISPLAYNAME"
$table = Get-SqlData $connection $query
foreach ($element in $table) {
    $Application1 += [PSCustomObject]@{
        'DISPLAYNAME' = $element.DISPLAYNAME
        'SUITENAME'   = $element.SUITENAME
    }
}
 
# Query for Application2
$Application2 = @()
$query = "SELECT DISTINCT A0.DISPLAYNAME, A1.SUITENAME FROM Computer A0 (nolock)
          LEFT OUTER JOIN AppSoftwareSuites A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn
          WHERE (A1.SUITENAME LIKE N'%Edge%')
          ORDER BY A0.DISPLAYNAME"
$table = Get-SqlData $connection $query
foreach ($element in $table) {
    $Application2 += [PSCustomObject]@{
        'DEVICENAME' = $element.DISPLAYNAME
        'SUITENAME'  = $element.SUITENAME
    }
}
 
# Query for Bitlocker details
$Bitlocker = @()
$query = "SELECT DISTINCT A0.DISPLAYNAME, A2.SECUREBOOTENABLED, A2.UEFIENABLED, A3.CONVERSIONSTATUS,
                 A4.TPMENABLE, A4.TPMVERSION, A5.MODEL
          FROM Computer A0 (nolock)
          LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn
          LEFT OUTER JOIN BIOS A2 (nolock) ON A0.Computer_Idn = A2.Computer_Idn
          LEFT OUTER JOIN BitLocker A3 (nolock) ON A0.Computer_Idn = A3.Computer_Idn
          LEFT OUTER JOIN TPMSystem A4 (nolock) ON A0.Computer_Idn = A4.Computer_Idn
          LEFT OUTER JOIN CompSystem A5 (nolock) ON A0.Computer_Idn = A5.Computer_Idn
          WHERE (A1.OSTYPE LIKE N'%Windows 10%')
          ORDER BY A0.DISPLAYNAME"
$table = Get-SqlData $connection $query
foreach ($element in $table) {
    $Bitlocker += [PSCustomObject]@{
        'DEVICENAME'    = $element.DISPLAYNAME
        'SECURE Boot'   = $element.SECUREBOOTENABLED
        'UEFI'          = $element.UEFIENABLED
        'BitLocker'     = $element.CONVERSIONSTATUS
        'TPM'           = $element.TPMENABLE
        'TPM Version'   = $element.TPMVERSION
        'Model'         = $element.MODEL
    }
}
 
# Query for Windows details
$Windows  = @()
$WindowsG = @()
$query = "SELECT DISTINCT A0.DISPLAYNAME, A1.OSTYPE, A2.CURRENTBUILD, A2.UBR
          FROM Computer A0 (nolock)
          LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn
          LEFT OUTER JOIN OSNT A2 (nolock) ON A0.Computer_Idn = A2.Computer_Idn
          WHERE (A1.OSTYPE LIKE N'%Windows 1%')
          ORDER BY A0.DISPLAYNAME"
$table = Get-SqlData $connection $query
foreach ($element in $table) {
    $versionFull  = "$($element.CURRENTBUILD).$($element.UBR)"
    $versionShort = "$($element.CURRENTBUILD)"
    $Windows += [PSCustomObject]@{
        'DEVICENAME' = $element.DISPLAYNAME
        'VERSION'    = $versionFull
    }
    $WindowsG += [PSCustomObject]@{
        'DEVICENAME' = $element.DISPLAYNAME
        'VERSION'    = $versionShort
    }
}
 
# Sort and group Windows data by version
$Windows        = $Windows | Sort-Object VERSION
$groupesVersion = $Windows | Group-Object -Property VERSION
 
$WindowsG        = $WindowsG | Sort-Object VERSION
$groupesVersionG = $WindowsG | Group-Object -Property VERSION
 
# Closing the SQL connection
$connection.Close()
 
Write-Host "------ Finished executing queries and closing SQL connection"

Generate HTML Report

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# Generating HTML report
New-HTML -TitleText 'IVANTI Report' {
 
    # Start Applications Tab
    New-HTMLTab -Name 'Application Tab' {
 
        # Content for Application1 tab
        New-HTMLPanel {
            New-HTMLSection -HeaderText 'Application Name for Application1' {
                New-HTMLTable -DataTable $Application1 -HideFooter -SearchPane
            }          
        }
 
        # Content for Application2 tab
        New-HTMLPanel {
            New-HTMLSection -HeaderText 'Application Name for Application2' {
                New-HTMLTable -DataTable $Application2 -HideFooter -SearchPane
            }          
        }
    }
    # End Applications Tab
     
    # Start Windows Tab
    New-HTMLTab -Name 'Windows TAB' {      
        New-HTMLSection -HeaderText 'Windows Version' {
            New-HTMLPanel {
                # Chart for full version details
                New-HTMLChart -Title "Version" {
                    New-ChartToolbar -Download
                    New-ChartEvent -DataTableID 'WindowsOS' -ColumnID 1
                    foreach ($groupe in $groupesVersion) {
                        New-ChartDonut -Name $($groupe.Name) -Value $($groupe.Count)
                    }
                }
            }
            # Chart for short version details
            New-HTMLPanel {
                New-HTMLChart -Title "VersionG" {
                    New-ChartToolbar -Download
                    foreach ($groupe in $groupesVersionG) {
                        New-ChartDonut -Name $($groupe.Name) -Value $($groupe.Count)
                    }
                }
            }
        }
 
        # Hidden section to display Windows data table
        New-HTMLSection -Invisible {         
            New-HTMLPanel {
                New-HTMLTable -DataTable $Windows -DataTableID 'WindowsOS' -HideFooter
            }
        }       
        # Section for Bitlocker details
        New-HTMLSection -HeaderText 'Bitlocker detail' {
            New-HTMLTable -DataTable $Bitlocker -HideFooter -SearchPane -PreContent {
                "Bitlocker Details"
            }
        }
    }
    # End Windows Tab
 
    # Footer with the report date
    New-HTMLFooter {
        New-HTMLText -Text "Date of this report (GMT time): $(Get-Date)" -Color Blue -Alignment Center
    }
} -FilePath $Exporthtml -Online

Task Scheduler

Create a scheduled task to execute this report every hour:

?
1
2
3
4
5
6
$action = New-ScheduledTaskAction -Execute 'powershell.exe' -Argument '-NoProfile -ExecutionPolicy Bypass -File "C:\temp\report.ps1"'
$trigger = New-ScheduledTaskTrigger -Once -At (Get-Date).Date -RepetitionInterval (New-TimeSpan -Hours 1) -RepetitionDuration (New-TimeSpan -Hours 23)
$principal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount
$settings = New-ScheduledTaskSettingsSet -AllowStartIfOnBatteries -DontStopIfGoingOnBatteries
 
Register-ScheduledTask -Action $action -Trigger $trigger -Principal $principal -Settings $settings -TaskName "RunReportScriptHourly" -Description "Runs report.ps1 every hour as SYSTEM account"

IIS Configuration

By default, the report file is named default.htm (as specified in the PowerShell script).

  • Create a virtual directory in the same folder as default.htm.
Creating a virtual directory in IIS for the PowerShell-generated HTML report.
  • Open the report in a web browser.
Viewing the generated HTML report in a web browser.

Script : https://github.com/DavidWuibaille/Repository/tree/main/Powershell/Module%20PsWriteHtml

Categories: EPM
Tags:

0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

EPM

IVANTI Documentation

Official IVANTI documentation

EPM

IVANTI EPM Provisioning 2024 Agent (Error 1603)

Resolve the IVANTI agent installation issue by ensuring the correct installation sequence of .NET Framework, including reboot steps and provisioning integration.

EPM

Uninstall Agent EBA

Uninstall Ivanti EPM agent effectively with step-by-step PowerShell commands. Learn to remove both new and legacy agents with ease