Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

...

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

1 Solution

Accepted Solutions
Not applicable
Author

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';

View solution in original post

3 Replies
francoiscave
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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?

Not applicable
Author

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';