New Ticket Display Screen. ( ANY SQL GURU's )???

twinturbo · 2012-11-23 12:14:26

We want a screen in the office that Only shows new tickets that have come in and not been assigned to a technician.

All it has to do is refresh every 30secons or so and list all unassigned tickets.

It needs to be simple and big

has anybody done this?

Rob

Last edited by twinturbo (2012-11-23 15:37:03)

twinturbo · 2012-11-23 15:36:43

Ok no answers so.

I have found that I need to start by pulling data from the glpi_tickets_users table.

this contains a list fo ID's

Each ID has a ticket number and a user type ( type 2 semes to be a supporter )

ID TICKET USER TYPE
1900 50 10 1
1901 50 05 2
1902 51 11 1

so ticket 50 has 2 users ( 5 and 10) a supporter and a normal user
so ticket 51 has 1 users ( 11 ) a normal user

I need an sql query that will pull out just those tickets with no type 2 user ( supporter)

My SQL knowledge has deminished considerably over the last 20 years since uni...

Anyone help with a query????

joaoprietos · 2012-11-23 16:42:19

Just use the ticket list and create a bookmark where you select

status is new
AND
Assign to - Technician is (left this field empty)

And for the refresh go to Setup > General > Default Values > and set Automatically refresh the list of tickets: 1 min.

twinturbo wrote:

We want a screen in the office that Only shows new tickets that have come in and not been assigned to a technician.
All it has to do is refresh every 30secons or so and list all unassigned tickets.
It needs to be simple and big
has anybody done this?
Rob

Last edited by joaoprietos (2012-11-23 16:45:29)

twinturbo · 2012-11-23 17:19:10

Thanks for the reply.

We want something MUCH simpler, and something that fills the screen.

And we want to generate a Numerical value for the number of unclaimed tickets.

So if I can get an answer on how to strip out the tickets with a type "2" user , I can do the rest.

Cheers

Rob

joaoprietos · 2012-11-23 17:40:08

To reply your specific question:

select * from glpi.glpi_tickets_users where type = "2"

but this is not a good solution... you will need to create a new code to display it and etc...
using filters is more secure AND you dont need to handle code alterations

In my work a lot of people uses diferent filters and views, then i teach each one to create and bookmark his own views

if i was going to make a new page for everyone...

twinturbo · 2012-11-23 19:52:42

that select statement will not give the required result as the table can have more than one entry for a ticket. It will just return all the tickets that do have a type 2 user not the ones that don't.

I need to find

glpi_tickets_users where the ticket does not have a type "2" user.

but using

select * from glpi.glpi_tickets_users where type != "2"

would not work either as it will return all tickets.

This will be a script outside of the GLPI code, a simple script querying the DB and generating a very simple HTML page to alert the office of new tickets, it's for a monitor on the wall not for an end user.

The filters pages are far too cluttered and small to be of use in this situation.

Cheers

Rob

Last edited by twinturbo (2012-11-23 19:55:23)

twinturbo · 2012-11-23 21:25:37

select ticket,sum(type) as utp from glpi_tickets_users group by ticket;

this will sum the "type" per ticket number

1 user
2 supporter
3 user + supporter

we only care about 1

So if we wrap this round it

select * from ( select ticket,sum(type) as utp from glpi_tickets_users group by ticket) as jobs where utp = 1;

we get a result of tickets with just a total user type sum of "1"

Crude perhaps but it will do.

Rob

imunro01 · 2012-11-28 02:28:28

I've just done this at work - a display screen that refreshes, showing new tickets, assigned tickets and pending tickets highlighted separately.

We've also written a new simple front end for staff to enter tickets (submits by email), and then to track them. It uses a mix of data from the GLPI database, and NTLM authentication to identify who is accessing it.

If you PM me an email address I can send you both screenshots and copies of the code we've used. It's all very simple

twinturbo · 2012-11-28 12:57:27

Thanks, I got it working on Monday.

Something "Proper" should be rolled into the code, but this does exactly what we needed. My script needs a bit of a tidy but that can wait for a nother time.

script refreshes the html every minuet, page reloads every 30 secons.

the red text flashes!

Job Jobbed.

I can close that one off the helpdesk

Rob

twinturbo · 2012-11-28 12:58:33

We obviously need a more permenant solution for the actual display screen, sitting a toshiba laptop on top of a thin terminal is not ideal.

Rob

twinturbo · 2012-11-28 13:16:35

And the bash script.

It's not pretty and needs a tidy, and may need some tayloring if anyone is planning on using it.

One cavet, the per-user details bring back tickets the are a supporter on and if they were a requester (unusual in our case) this could be fairly easily fixed though (i think) . the per user code needs tidying into a FOR loop. And there's lots more I could tidy up.

If I was any good at HTML/PHP it could be made more attractive and show more stats.

There's also old redundant code in there.

But it's simple and there to do a job.

http://www.twistedpear.org.uk/images/st … st-new.txt

Rob

nalian · 2012-11-30 07:18:55

I want a screen in the office that only shows new tickets

Forum GLPI-Project

Announcement

#1 2012-11-23 12:14:26

New Ticket Display Screen. ( ANY SQL GURU's )???

#2 2012-11-23 15:36:43

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#3 2012-11-23 16:42:19

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#4 2012-11-23 17:19:10

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#5 2012-11-23 17:40:08

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#6 2012-11-23 19:52:42

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#7 2012-11-23 21:25:37

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#8 2012-11-28 02:28:28

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#9 2012-11-28 12:57:27

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#10 2012-11-28 12:58:33

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#11 2012-11-28 13:16:35

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

#12 2012-11-30 07:18:55

Re: New Ticket Display Screen. ( ANY SQL GURU's )???

Board footer