Reporting on a custom Field in Helpdesk

[Sam_Smith_IPU]

Hi Forum Members!!

I was hoping someone could help me out as this goes slightly over my head to creating what I need even after having a go.

I am after a report where I can see all tickets with the value of 'Other', this value is under a custom field called 'Item'

Also the ticket number, subject and requester would be helpful too.

[Esben_D]

try giving the ID an alias, like 'number' or something. By default IDs are not shown within Lansweeper because they are just used as unique identifiers and usually do not mean anything.

[Esben_D]

The report below should get you started. I've added all the tables you needed, all you need to do is just enable the fields you want to see and add conditions. That can all be done in the visual interface.

Select Top 1000000 htblticket.subject
From htblcustomfields
  Inner Join htblcustomfieldvalues On htblcustomfields.fieldid =
    htblcustomfieldvalues.fieldid
  Inner Join htblticketcustomfield On htblcustomfields.fieldid =
    htblticketcustomfield.fieldid
  Inner Join htblticket On htblticket.ticketid = htblticketcustomfield.ticketid
  Inner Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htblusers On htblusers.userid = htblticket.fromuserid
  Inner Join htblagents On htblagents.agentid = htblticket.agentid

[Sam_Smith_IPU]

Esben.D wrote:
The report below should get you started. I've added all the tables you needed, all you need to do is just enable the fields you want to see and add conditions. That can all be done in the visual interface.

Select Top 1000000 htblticket.subject
From htblcustomfields
  Inner Join htblcustomfieldvalues On htblcustomfields.fieldid =
    htblcustomfieldvalues.fieldid
  Inner Join htblticketcustomfield On htblcustomfields.fieldid =
    htblticketcustomfield.fieldid
  Inner Join htblticket On htblticket.ticketid = htblticketcustomfield.ticketid
  Inner Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htblusers On htblusers.userid = htblticket.fromuserid
  Inner Join htblagents On htblagents.agentid = htblticket.agentid

Hi Esben,

Thank you, I have tried adding in a filter to show anything with Other and add in the ticket ID but the filter does not work and just adds a column with other next to everything and the ID does not show. Blow is the new SQL Code

Select Top 1000000 htblticket.subject,
htblcustomfieldvalues.value,
htblticket.ticketid
From htblcustomfields
Inner Join htblcustomfieldvalues On htblcustomfields.fieldid =
htblcustomfieldvalues.fieldid
Inner Join htblticketcustomfield On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Inner Join htblticket On htblticket.ticketid = htblticketcustomfield.ticketid
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Where htblcustomfieldvalues.value = 'Other'
Group By htblticket.subject,
htblcustomfieldvalues.value,
htblticket.ticketid

[adamentally]

I cannot get the ID to show either. Even if it's the only column in the report and I'm asking them to show all the entries in that table. It doesn't matter how I order it, there's no column title and no id numbers.