→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Sam_Smith_IPU
Engaged Sweeper
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.
4 REPLIES 4
Esben_D
Lansweeper Employee
Lansweeper Employee
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
Lansweeper Employee
Lansweeper Employee
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
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
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.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now