cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
smartinez
Engaged Sweeper
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.
1 REPLY 1
Bruce_B
Lansweeper Alumni
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