{"id":104,"date":"2023-10-24T14:58:26","date_gmt":"2023-10-24T17:58:26","guid":{"rendered":"https:\/\/thiagoldaniel.com.br\/?p=104"},"modified":"2023-10-24T14:59:12","modified_gmt":"2023-10-24T17:59:12","slug":"query-mysql-base-para-extracao-de-chamados-do-glpi-e-uso-em-uma-ferramenta-de-bi","status":"publish","type":"post","link":"https:\/\/thiagoldaniel.com.br\/index.php\/2023\/10\/24\/query-mysql-base-para-extracao-de-chamados-do-glpi-e-uso-em-uma-ferramenta-de-bi\/","title":{"rendered":"Query Mysql Base para extra\u00e7\u00e3o de Chamados do GLPI e uso em uma ferramenta de BI."},"content":{"rendered":"\n<p>Apresento uma Query Base, criada com o objetivo de extrair dados de chamados da Plataforma GLPI aplicando estes dados as mais diversas ferramentas de BI Existententes.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT DISTINCT\n        `glpi_prd`.`glpi_tickets`.`id` AS `id`,\n        `glpi_prd`.`glpi_tickets`.`name` AS `narrativa`,\n        `glpi_prd`.`glpi_plugin_status_ticket`.`descricao` AS `descricao`,\n        `glpi_prd`.`glpi_entities`.`completename` AS `empresa`,\n        `glpi_prd`.`glpi_tickets`.`date_mod` AS `modificacao`,\n        `glpi_prd`.`glpi_tickets`.`date` AS `abertura`,\n        `glpi_prd`.`glpi_tickets`.`priority` AS `prioridade`,\n        `glpi_prd`.`glpi_itilcategories`.`completename` AS `Categoria`,\n        `glpi_prd`.`glpi_tickets`.`solvedate` AS `solucao`,\n        (SELECT \n                `glpi_prd`.`glpi_users`.`name` AS `requerente`\n            FROM\n                (`glpi_prd`.`glpi_users`\n                JOIN `glpi_prd`.`glpi_tickets_users` ON (`glpi_prd`.`glpi_users`.`id` = `glpi_prd`.`glpi_tickets_users`.`users_id`))\n            WHERE\n                `glpi_prd`.`glpi_tickets_users`.`type` = 1\n                    AND `glpi_prd`.`glpi_tickets_users`.`tickets_id` = `glpi_prd`.`glpi_tickets`.`id`) AS `requerente`,\n        (SELECT DISTINCT\n                `glpi_prd`.`glpi_users`.`name` AS `tecnico`\n            FROM\n                (`glpi_prd`.`glpi_users`\n                JOIN `glpi_prd`.`glpi_tickets_users` ON (`glpi_prd`.`glpi_users`.`id` = `glpi_prd`.`glpi_tickets_users`.`users_id`))\n            WHERE\n                `glpi_prd`.`glpi_tickets_users`.`type` = 2\n                    AND `glpi_prd`.`glpi_tickets_users`.`tickets_id` = `glpi_prd`.`glpi_tickets`.`id`\n            LIMIT 1) AS `tecnico`\n    FROM\n        (((((((`glpi_prd`.`glpi_tickets`\n        LEFT JOIN `glpi_prd`.`glpi_entities` ON (`glpi_prd`.`glpi_tickets`.`entities_id` = `glpi_prd`.`glpi_entities`.`id`))\n        LEFT JOIN `glpi_prd`.`glpi_tickets_users` `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24` ON (`glpi_prd`.`glpi_tickets`.`id` = `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`tickets_id`\n            AND `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1'))\n        LEFT JOIN `glpi_prd`.`glpi_users` `glpi_users_af1042e23ce6565cfe58c6db91f84692` ON (`glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`users_id` = `glpi_users_af1042e23ce6565cfe58c6db91f84692`.`id`))\n        LEFT JOIN `glpi_prd`.`glpi_tickets_users` `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5` ON (`glpi_prd`.`glpi_tickets`.`id` = `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`tickets_id`\n            AND `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2'))\n        LEFT JOIN `glpi_prd`.`glpi_users` `glpi_users_b1b92f6be5e70531688d870931e94a65` ON (`glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`users_id` = `glpi_users_b1b92f6be5e70531688d870931e94a65`.`id`))\n        LEFT JOIN `glpi_prd`.`glpi_itilcategories` ON (`glpi_prd`.`glpi_tickets`.`itilcategories_id` = `glpi_prd`.`glpi_itilcategories`.`id`))\n        LEFT JOIN `glpi_prd`.`glpi_plugin_status_ticket` ON (`glpi_prd`.`glpi_tickets`.`status` = `glpi_prd`.`glpi_plugin_status_ticket`.`id`))\n    WHERE\n        `glpi_prd`.`glpi_tickets`.`is_deleted` = 0\n            AND `glpi_prd`.`glpi_tickets`.`status` IN ('1' , '2', '3', '4', '5', '6')\n    GROUP BY `glpi_prd`.`glpi_tickets`.`id`\n    ORDER BY `glpi_prd`.`glpi_tickets`.`id`<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Apresento uma Query Base, criada com o objetivo de extrair dados de chamados da Plataforma GLPI aplicando estes dados as mais diversas ferramentas de BI Existententes.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,4,1],"tags":[18,10,11,8],"class_list":["post-104","post","type-post","status-publish","format-standard","hentry","category-banco-de-dados","category-infraestrutura-de-tecnologia-da-informacao","category-sem-categoria","tag-glpi","tag-infraestrutura-de-tecnologia-da-informacao","tag-mysql","tag-query"],"_links":{"self":[{"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/posts\/104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=104"}],"version-history":[{"count":1,"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/posts\/104\/revisions"}],"predecessor-version":[{"id":105,"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/posts\/104\/revisions\/105"}],"wp:attachment":[{"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thiagoldaniel.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}