Showing results for 
Show  only  | Search instead for 
Did you mean: 
Champion Sweeper
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.

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
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

#region SelectHeader Static Text
$SelectHeader = @"
Select Distinct Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,,
htblticket.subject, As [User Name], As [Help Desk Agent Name],
#endregion SelectHeader Static Text

#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 = "$ As [$ReplaceString],"

{ <# if `$i equals total $TicketFieldsToReport, omits the last comma in the Select statement #>
$ReplaceString = $TicketFieldsToReport[$i] -replace '[\W]', ''
$SelectStatement = "$ As [$ReplaceString]"
} #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 =
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
#endregion FromStatement Static Text

#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,
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
Where Like `'$TicketCustomField') As $ReplaceString
On htblticket.ticketid = $ReplaceString.ticketid
} #end for
#endregion FromStatement Dynamic Text

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now