====== Criação de Consultas Personalizadas no OpenAudit ======
===== Instrodução =====
Uma Query é na verdade um relatório exibido a partir de uma consulta específica no banco de dados. **No Menu->Admin->Queries->Activate** Queries, ative as principais Querys que existem
\\ ===== Criando uma Query =====
Uma query é baseada em uma pesquisa SQL no banco de dados, então, antes de tudo é necessário entender o modelo do banco de dados do Open-[[infraestrutura_de_ti:open-audit:AudIT]]. É preciso então definir quais campos serão mostrados, quais serão pesquisados e montar uma consulta que retorne esses dados. Utilizamos o [[infraestrutura_de_ti:open-audit:PHPMyAdmin]] para realizar consultas e entender o banco de dados. Com o SQL pronto e funcionando, escrevemos um arquivo XML com a definição do relatório em questão. Esse relatório deve ter os seguintes campos:
* report
* details
* report_name -> Nome da Query - Relatório
* report_display_in_menu -> **y** para mostrar no menu, **n** para não mostrar no menu
* report_sql -> campo CDATA com o SQL que será executado para o relatório
* columns
* column
* column_order -> ordem que a coluna vai aparecer (0,1,2 etc)
* column_name -> nome da coluna que irá aparecer para o usuário
* column_variable -> nome da coluna no banco de dados ou na consulta SQL
* column_type -> tipo da coluna (image, text, link)
* column_link -> link que será seguido quando clicado
===== Exemplo - Relatório de pesquisa de softwares =====
Abaixo um exemplo de criação de estrutura para monitorar softwares através do Open-[[infraestrutura_de_ti:open-audit:AudIT]]. Foram criadas duas tabelas na base do Open-[[infraestrutura_de_ti:open-audit:AudIT]] para armazenar quais são os softwares mais importantes para o TCE. O relatório consiste em comparar se nas estações de do TCE existem esses softwares.
Passos para criação do relatório:
- Criar tabelas necessárias
- Criar funções ou stored procidures necessárias no [[infraestrutura_de_ti:open-audit:MySQL]]
- Criar e testar consulta específica
- Criar arquivo XML
- Importar XML no Open-[[infraestrutura_de_ti:open-audit:AudIT]]
- Testar Consulta
\\ ==== Tabelas adicionais ====
Estrutura para tabela `tce_software_monitorado`
CREATE TABLE IF NOT EXISTS `tce_software_monitorado` (
`id` int(11) NOT NULL,
`nome` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='Criada para armazenar lista de softwares monitorados, por exemplo, aqueles que tenham licença especial.' ;
--
-- Fazendo dump de dados para tabela `tce_software_monitorado`
--
INSERT INTO `tce_software_monitorado` (`id`, `nome`) VALUES
(1, 'Autocad'),
(5, 'COREL DRAW'),
(6, 'FLIPPING'),
(8, 'SPHINX LEXICA'),
(9, 'MICROSOFT VISIO'),
(10, 'oraclelinux-release'),
(11, 'Quest SQL Optimizer for Oracle'),
(12, 'Toad for Oracle'),
(13, 'Knowledge Xpert for Oracle Administration'),
(14, 'Knowledge Xpert Oracle Common');
--
-- Índices de tabela `tce_software_monitorado`
--
ALTER TABLE `tce_software_monitorado`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT de tabela `tce_software_monitorado`
--
ALTER TABLE `tce_software_monitorado`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=15;
\\ Estrutura para tabela `tce_software_proibido`
CREATE TABLE IF NOT EXISTS `tce_software_proibido` (
`id` int(11) NOT NULL,
`nome` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 COMMENT='Criada para armazenar lista de software monitorados ou proibidos';
--
-- Fazendo dump de dados para tabela `tce_software_proibido`
--
INSERT INTO `tce_software_proibido` (`id`, `nome`) VALUES
(1, 'counter strike'),
(6, 'Dropbox'),
(8, 'BoxNet'),
(9, 'Facebook'),
(10, 'BoxNet'),
(11, 'Facebook'),
(12, 'Google Drive'),
(13, 'Pandora'),
(14, 'Google Drive'),
(15, 'Pandora'),
(17, 'Angry Birds'),
(18, 'Hoccer'),
(19, 'Netflix'),
(20, 'Google+'),
(21, 'Google Play Movies'),
(22, 'Google Play Music'),
(23, 'Google+ Hangouts'),
(24, 'YouTube'),
(25, 'SkyDrive'),
(26, 'Angry Birds'),
(27, 'Hoccer'),
(28, 'Netflix'),
(29, 'Google+'),
(30, 'Google Play Movies'),
(31, 'Google Play Music'),
(32, 'Google+ Hangouts'),
(33, 'YouTube'),
(34, 'thor'),
(35, 'Skype');
--
-- Índices de tabela `tce_software_proibido`
--
ALTER TABLE `tce_software_proibido`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT de tabela `tce_software_proibido`
--
ALTER TABLE `tce_software_proibido`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=36;
==== Funções de pesquisa ====
delimiter //
CREATE FUNCTION is_software_proibido (nome_pesq VARCHAR(100))
RETURNS BOOLEAN
NOT DETERMINISTIC
BEGIN
DECLARE existe BOOLEAN;
DECLARE campo_pesquisa VARCHAR(102);
SELECT TRUE INTO existe
FROM tce_software_proibido s
where upper(nome_pesq) like upper(concat('%',s.nome,'%'))
limit 1;
IF (existe IS NOT NULL) THEN SET existe = TRUE;
ELSE SET existe = FALSE;
END IF;
RETURN existe;
END //
delimiter ;
\\
delimiter //
CREATE FUNCTION is_software_monitorado (nome_pesq VARCHAR(100))
RETURNS BOOLEAN
NOT DETERMINISTIC
BEGIN
DECLARE existe BOOLEAN;
DECLARE campo_pesquisa VARCHAR(102);
SET campo_pesquisa = CONCAT( "%", nome_pesq, "%" );
SELECT TRUE INTO existe
FROM tce_software_monitorado s
where upper(nome_pesq) like upper(concat('%',s.nome,'%'))
limit 1;
IF (existe IS NOT NULL) THEN SET existe = TRUE;
ELSE SET existe = FALSE;
END IF;
RETURN existe;
END //
delimiter ;
==== Testando funções ====
SELECT distinct system.man_icon,
system.man_type,
system.system_id,
system.hostname,
sys_sw_software.software_name,
sys_sw_software.software_version,
sys_sw_software.software_publisher,
sys_sw_software.software_url,
sys_sw_software.software_email,
sys_sw_software.software_id,
sys_sw_software.software_comment
FROM sys_sw_software, system, oa_group_sys
WHERE sys_sw_software.timestamp = system.timestamp
AND sys_sw_software.system_id = system.system_id
AND system.system_id = oa_group_sys.system_id
AND is_software_proibido(sys_sw_software.software_name) = true
ORDER BY sys_sw_software.software_name;
\\
SELECT distinct system.man_icon,
system.man_type,
system.system_id,
system.hostname,
sys_sw_software.software_name,
sys_sw_software.software_version,
sys_sw_software.software_publisher,
sys_sw_software.software_url,
sys_sw_software.software_email,
sys_sw_software.software_id,
sys_sw_software.software_comment
FROM sys_sw_software, system, oa_group_sys
WHERE sys_sw_software.timestamp = system.timestamp
AND sys_sw_software.system_id = system.system_id
AND system.system_id = oa_group_sys.system_id
AND is_software_monitorado(sys_sw_software.software_name) = true
ORDER BY sys_sw_software.software_name;
\\ ==== Criando uma Query a partir de um XML ====
Por fim, basta criar um arquivo XML para mostrar os dados da pesquisa utilizando o SQL defindo anteriormente. Para melhorar o relatório, importante inserir a restrição **AND oa_group_sys.group_id = @group** no corpo do SQL para que sejam carregados apenas os dados do Grupo selecionado pelo usuário. Para importar o XML, vá em **Menu->Admin->Queries->Import Query** e cole o XML no campo específico.
Softwares - Monitorados
y
v_report
0
0
Icon
man_icon
image
man_type
center
1
Type
man_type
text
2
System Name
hostname
link
/main/system_display/
system_id
3
Package Name
software_name
link
/report/specific_software/$group_id/
software_id
left
4
Version
software_version
text
right
5
Installs
software_count
text
center
6
Publisher
software_publisher
text
left
7
Type
software_comment
text
left
8
Contact
software_url
url
left
9
Google Search
url
https://encrypted.google.com/search?q=
software_name
google
center
\\ ==== Testando Query ====
Para testar a query, abra a página inicial do Open-[[infraestrutura_de_ti:open-audit:AudIT]] e no grupo que você quiser clique em **Reports** e selecione o relatório criado.
{{ :open-audit:1200px-openaudit_ex_query.png |}}
\\ O Resultado será parecido com o seguinte:
{{ :open-audit:1200px-openaudit_ex_query_final.png |}}
\\ .