Helpdesk Closed Ticket Report with Custom Fields

[smartinez]

I am trying to export ticket data out of lansweeper upon resolution/closing. I have successfully ran a closed ticket report for the last 30 days but in doing so I wish to include custom fields in my criteria.

The first of these fields is called: Outside_Ticket , I would like the report to filter results only when the value inside this field is set to yes. In doing so two additional nested custom fields are made available which are the fields that im looking to pull back into another system.

These fields are: WSID and ImportID, the WSID is a drop down field that only allows for one option to be selected as this number is constant and will never change. The second field "ImportID" will be a field associated inside a different system with the unique ticket record. This is a free text field that the agent will be required to fill in prior to closing the ticket.

Ideal the Report Table should yield.

Custom Fields:
WSID = "1234" , ImportID, Outside_Ticket = "Yes",

Standard Lansweeper Fields:
TicketNumber, TicketNotes, TicketStatus = "Closed", TicketCloseDate, AgentName

Then end goal is to have the ability to run this report on a regular basis and update an external system.

[Bruce_B]

This question was forwarded to technical support via email. I've added my reply below for other people's benefit. Do note that the highlights that are referred to are not incorporated as the forum SQL syntax formatting doesn't allow highlighting.

We've added a few subqueries to your report that incorporate the ticket custom fields you've highlighted in your report. If you'd like to apply this for other custom fields or in other reports in the future, we've highlighted the values you should replace with the custom field name in yellow and the subquery in green.

SELECT DISTINCT TOP 1000000 htblticket.ticketid,
                            htblticket.subject,
                            htblusers1.name AS Agent,
                            htblusers.name AS Requester,
                            htblticket.priority,
                            htblticket.date,
                            htblticket.updated,
                            htblticketstates.statename,
                            htblticket.assetid,
                            Ticket.data AS Ticket,
                            ImportID.data AS ImportID,
                            WSID.Data AS WSID
FROM htblticket
     INNER JOIN htblnotes ON htblticket.ticketid = htblnotes.ticketid
     INNER JOIN htblusers ON htblusers.userid = htblticket.fromuserid
     INNER JOIN htblticketstates ON htblticketstates.ticketstateid = htblticket.ticketstateid
     INNER JOIN htblagents ON htblagents.agentid = htblticket.agentid
     INNER JOIN htblusers htblusers1 ON htblusers1.userid = htblagents.userid
                                        AND htblusers.userid = htblnotes.userid
     LEFT JOIN
(
    SELECT TOP 1000000 htblticketcustomfield.ticketid,
                       htblticketcustomfield.data
    FROM htblticketcustomfield
         INNER JOIN htblcustomfields ON htblcustomfields.fieldid = htblticketcustomfield.fieldid
    WHERE htblcustomfields.name LIKE 'Ticket'
) AS Ticket ON Ticket.ticketid = htblticket.ticketid
     LEFT JOIN
(
    SELECT TOP 1000000 htblticketcustomfield.ticketid,
                       htblticketcustomfield.data
    FROM htblticketcustomfield
         INNER JOIN htblcustomfields ON htblcustomfields.fieldid = htblticketcustomfield.fieldid
    WHERE htblcustomfields.name LIKE 'ImportID'
) AS ImportID ON ImportID.ticketid = htblticket.ticketid
     LEFT JOIN
(
    SELECT TOP 1000000 htblticketcustomfield.ticketid,
                       htblticketcustomfield.data
    FROM htblticketcustomfield
         INNER JOIN htblcustomfields ON htblcustomfields.fieldid = htblticketcustomfield.fieldid
    WHERE htblcustomfields.name LIKE 'WSID'
) AS WSID ON WSID.ticketid = htblticket.ticketid
WHERE htblticketstates.statename = 'Closed' AND htblticket.updated > GetDate() - 30