You are not logged in.
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)
Offline
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????
Offline
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.
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)
GLPI 9.1.6
4500+ computers / 2000 tickets month
Offline
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
Offline
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...
GLPI 9.1.6
4500+ computers / 2000 tickets month
Offline
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)
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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
Offline
I want a screen in the office that only shows new tickets
Offline