Powershell - Automated Monthly Usage Reports

requires: SMTP server

Written by Ines

Last published at: June 21st, 2024

Description

 

The script is automated using the Windows Scheduled Task, running either on the Windows Server where Thinscale Server is installed or on another machine that can communicate with the Thinscale Server and its database.

 

Note:

To fully automate the delivery of the Usage Report, an SMTP server must be available.

 

 

Prerequisites

  • Powershell version 7.x
  • Thinscale Powershell Module that corresponds the version of the installed Thinscale Management Platform that can be downloaded from my.thinscale.com
  • SMPT server

 

Download Files

 

Guide

Step 1: Personalizing the Script Parameters

  • The $URI refers to the full server URI that can be the FQDN or IP Address.
  • The $username should only contain the username with administrative privileges within the Thinscale Environment (administrator login to the Thinscale Server).
  • The $Recipient is the email address which will be receiving the report. Consult with your Thinscale Account Manager for this.
  • The $smtpServer is your email provider’s SMTP server.
  • The $smtpFrom is the sender email address of the form.
  • The $PW is the password to be used with the above for the purpose of authentication before the email goes out

 

SMTP PORT

If your SMTP server does not require an smtp port to be configured, you can place a hashtag symbol to the left of the $smtpPort to disable it. Make sure to place it here as well:

 

 

 
 

Step 2:  Personalizing and running the EncryptCreds.ps1

For the script to make the connection with the Thinscale Server, the Thinscale Administrator’s password needs to be saved as an encrypted hash locally within the encrypted.txt file, which must be located in the working directory (<system drive>\TST_LOGS)

To make this happen, run the EncryptCreds.ps1 script as the user which will also be running the Windows Task Schedule.

The only times this will have to be done is if this is the first setup, the administrator account of the Thinscale Server has changed, or its password has changed.

 

Warning!

If the user that runs the WinTask is different from the user that created the encrypted.txt file, the WinTask will fail as the encrypted file is bound to the user that had run the script.

 

Warning!

The  “encrypted.txt” file and the "UsageReport.ps1" files need to be in the same folder, so make sure that the $destination parameter matches the one in the UsageReport.ps1 script

 

Tip:

You can use the free Microsoft Visual Code software with the powershell module installed to edit the scripts!

 

 
 

Step 3: Load and edit the WindowsTask.xml

The WindowsTask.xml is the pre-configured task that will run the Usage Report script on the last day of each month.

To edit:

1. On the server that has Thinscale Management Platform installed, or machine that is able to communicate with it, start the Windows Task Scheduler.

2. Click on Task Scheduler Library and then right-click within the workspace, then click "Import Task…"

3. Locate your downloaded WindowsTask.xml file and open it.

4. When the Task editor wizard appears, click the change User or Group to select the user that will be running the task.

IMPORTANT!

This user needs to be the same one that ran the “encryptedCreds.ps1” script otherwise the script will fail!

 

5. Click OK to save the task.

 
 

Step 4: Test Runs

Test the Task


If the user permissions are correct a successful execution of the WinTask would give the “Last Run Result” message like this: “The operation completed successfully. (0x0)”

 

Test the Script

Bearing in mind the $destination variable, where you’ve configured the working folder, check that path to see if the log and exports have been created. If the PowerShell script had been executed correctly, then you would see this within the folder:

And success messages within the ReportLog file:

 
 

Troubleshooting Issues

There are two types of possible issues you might encounter:

  1. Windows Task Schedule issues
  2. PowerShell script issues

It’s important to recognize the nature of the issue to be able to troubleshoot locally or to know which party to reach out to for support.

 

Windows Task Schedule issues

If a Windows Tash fails to run, the “Last Run Result” column within the Tash Scheduler library will show an Error and its description. Typically, these are related to the permissions of the user that was configured to run the task. 

Also, none of the files will be created within the working directory of the script.

 

PowerShell script issues

If there is an issue with the script itself, the output of the errors will be exported in the log file. In case it cannot be troubleshooted locally, this log is required when reaching out to Thinscale Support.

 
 

 

Example of the email report

Email:

Attachment (csv):