Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hallo,
ich habe folgenden Skript:
ODBC
CONNECT
TO
GLPI (
XUserId
is
YMPWTYRNJbaMXUVMXDfA,
XPassword
is
CeaVZYRNJbaMXUVMXbMB);
Load
*,
month
(
floor
(
date
))
as
Monat
,
year
(
floor
(
date
))
as
Jahr
,
floor
(
date
)
as
Datum1
,
frac
(
date
)
as
Zeit
;
SQL
SELECT *
FROM glpi.`glpi_tickets` where `is_deleted` = '0';
SELECT
realname FROM glpi.glpi_users, glpi.glpi_tickets,glpi.glpi_tickets_users
where glpi.glpi_users.id=glpi_tickets_users.users_id and
glpi.glpi_tickets.id=glpi_tickets_users.tickets_id and
glpi_tickets_users.type=2;
Jetzt habe ich das Problem, dass es in der Tabellenstruktur keine Beziehung
gibt. Was habe ich flasch gemacht, oder wie kann ich das beheben. Können Sie
mir hier helfen? Ziel ist, dass ich seh, wieviele Tickets der Techniker
(realname) sortiert nach Monat bzw. Status erledigt hat Aber jeder
Techniker hat als Anzahl alle Tickets drin Danke im Voraus
Florian Klünder
This is the answer:
Load *, day(floor(date)) as Tag, week(floor(date)) as Woche, month(floor(date)) as Monat, year(floor(date)) as Jahr, floor(date) as Datum1, frac(date) as Zeit;
SELECT * from glpi_tickets;
SELECT `glpi_tickets`.`id`,`glpi_users`.`realname`,glpi_tickets.status
FROM `glpi_tickets`
left JOIN `glpi_tickets_users` ON (`glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id` AND `glpi_tickets_users`.`type` = 2 )
left JOIN `glpi_users` ON (`glpi_tickets_users`.`users_id` = `glpi_users`.`id` )
where `glpi_tickets`.`is_deleted` = '0';
Hallo Florian,
Es gibt keine Beziehung in der Tabellenstruktur...
You have certainly a key field in GLPI to Join your tickets and your Technician name...
Something like that ?
Tickets:
LOAD
id as tickets_id
....;
SQL SELECT
id,
...
FROM glpi_tickets;
User_tmp:
LOAD
tickets_id,
users_id,
type as type_user;
SQL SELECT
tickets_id,
users_id,
type
FROM glpi_tickets_users;
Left Join
LOAD
id as users_id,
name as TechnicianName,
realname &' '& firstname as Technician;
SQL SELECT
id,
name,
realname,
firstname
FROM glpi_users;
François
OK. I tried it. This is the Skript now:
ODBC CONNECT TO GLPI (XUserId is YMPWTYRNJbaMXUVMXDfA, XPassword is CeaVZYRNJbaMXUVMXbMB);
Load *, month(floor(date)) as Monat, year(floor(date)) as Jahr, floor(date) as Datum1, frac(date) as Zeit;
SQL SELECT *
FROM glpi.`glpi_tickets` where `is_deleted` = '0';
SELECT *
FROM `glpi_users`;
Tickets:
LOAD
id as tickets_id;
SQL SELECT
id
FROM glpi_tickets;
User_tmp:
LOAD
tickets_id,
users_id,
type as type_user;
SQL SELECT
tickets_id,
users_id,
type
FROM glpi_tickets_users;
Left Join
LOAD
id as users_id,
name as TechnicianName,
realname &' '& firstname as Technician;
SQL SELECT
id,
name,
realname,
firstname
FROM glpi_users;
My Problem now is, that all you write to me is functional, but the Connection to the tickets doesn´t work (it Shows all tickets from December 2014 at every user). See Picture
https://owncloud.pc-blocka.de/public.php?service=files&t=d89083c2b195bd56f1fdce861fa03961
And i needin the left graph not the technican Name(because you define technican as firstname and realname). I Need only the users defined like this (only type 2).
SELECT firstname,realname as Techniker FROM glpi.glpi_users, glpi.glpi_tickets,glpi.glpi_tickets_users
where glpi.glpi_users.id=glpi_tickets_users.users_id and glpi.glpi_tickets.id=glpi_tickets_users.tickets_id and glpi_tickets_users.type=2;
Do you have an idea?
This is the answer:
Load *, day(floor(date)) as Tag, week(floor(date)) as Woche, month(floor(date)) as Monat, year(floor(date)) as Jahr, floor(date) as Datum1, frac(date) as Zeit;
SELECT * from glpi_tickets;
SELECT `glpi_tickets`.`id`,`glpi_users`.`realname`,glpi_tickets.status
FROM `glpi_tickets`
left JOIN `glpi_tickets_users` ON (`glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id` AND `glpi_tickets_users`.`type` = 2 )
left JOIN `glpi_users` ON (`glpi_tickets_users`.`users_id` = `glpi_users`.`id` )
where `glpi_tickets`.`is_deleted` = '0';