Helpdesk Report SQL generator using Powershell

[harringg]

Using the Lansweeper template for a Helpdesk Ticket Report, I've written the following Powershell script to create a SQL query formatted to copy/paste in the Lansweeper report editor.

The scope of this Powershell script is to simply take an existing customized Helpdesk Ticket, use the selected fields from that ticket and generate a SQL report for the report editor.

All you need to do is enter the Ticket Custom Field Name (label) where indicated in the script. Note: syntax must match the values in the Name (label) field of the Ticket Custom Field.

I have only tested on v5+ of Powershell. This is As-Is, and I don't have any plans to modify/expand it. If anyone wants to enhance it, please share your code here.

Moderators, if this should be moved to another forum, please do so.

<#	
	.NOTES
	===========================================================================
	 Created with: 	SAPIEN Technologies, Inc., PowerShell Studio 2018 v5.5.152
	 Created on:   	7/9/2018 10:50 AM
	 Created by:   	Grant.Harrington
	 Filename:     	Helpdesk-ReportGenerator.ps1
	===========================================================================
	.DESCRIPTION
		This will take a list of Lansweeper v6 Ticket Field names and create the SQL code for building a custom Report
#>

<#--- PASTE THE Lansweeper Ticket Fields here ---#>
#region Lansweeper Ticket Fields
$TicketFieldsToReport = @"
CL: Project Name
CL: Date
CL: Action
CL: Description
"@ -split '\r\n'
#endregion Lansweeper Ticket Fields

<#--- DO NOT EDIT CODE BELOW --- #>
#region SelectHeader Static Text
$SelectHeader = @"
Select Distinct Top 1000000 htblticket.ticketid,
  '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
  htblticket.date,
  htbltickettypes.typename,
  htblticketstates.statename,
  htblticket.subject,
  htblusers1.name As [User Name],
  htblusers.name As [Help Desk Agent Name],
"@
#endregion SelectHeader Static Text

$SelectHeader
#region SelectStatement Dynamic Text
for ($i = 0<# Set counter to '0' #>; $i -le $TicketFieldsToReport.Length - 1<# While `$i less than total $TicketFieldsToReport minus 1 #>; $i++<# Increment `$i by 1 #>)
{
	if ($i -lt $TicketFieldsToReport.Length - 1 <# if `$i less than total $TicketFieldsToReport minus 1 #>)
	{
		
		$ReplaceString = $TicketFieldsToReport[$i] -replace '[\W]', ''
		$SelectStatement = "$ReplaceString.data As [$ReplaceString],"
		$SelectStatement
		
	}
	else
	{ <# if `$i equals total $TicketFieldsToReport, omits the last comma in the Select statement #>
		$ReplaceString = $TicketFieldsToReport[$i] -replace '[\W]', ''
		$SelectStatement = "$ReplaceString.data As [$ReplaceString]"
		$SelectStatement
	} #end if-else
} #end for
#endregion SelectStatement Dynamic Text

#region FromStatement Static Text
$FromStatement = @"
From htblticket
  Left Join htblagents On htblagents.agentid = htblticket.agentid
  Left Join htblusers On htblusers.userid = htblagents.userid
  Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
  Left Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htbltickettypes On htbltickettypes.tickettypeid =
    htblticket.tickettypeid
"@
#endregion FromStatement Static Text

$FromStatement
#region FromStatement Dynamic Text
for ($i = 0<# Set counter to '0' #>; $i -le $TicketFieldsToReport.Length - 1<# While `$i less than 12 #>; $i++<# Increment `$i by 1 #>)
{
	
	$TicketCustomField = $TicketFieldsToReport[$i]
	$ReplaceString = $TicketFieldsToReport[$i] -replace '[\W]', ''
	
	$TicketID = @"
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
    htblticketcustomfield.data
  From htblticketcustomfield
    Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
  Where htblcustomfields.name Like `'$TicketCustomField') As $ReplaceString
    On htblticket.ticketid = $ReplaceString.ticketid
"@
	$TicketID
} #end for
#endregion FromStatement Dynamic Text