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-AudIT. É preciso então definir quais campos serão mostrados, quais serão pesquisados e montar uma consulta que retorne esses dados. Utilizamos o 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-AudIT. Foram criadas duas tabelas na base do Open-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 MySQL
Criar e testar consulta específica
Criar arquivo XML
Importar XML no Open-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.

<?xml version="1.0" encoding="ISO-8859-1"?>
<report>
        <details>
                <report_name>Softwares - Monitorados</report_name>
                <report_description></report_description>
                <report_display_in_menu>y</report_display_in_menu>
                <report_sql><![CDATA[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 oa_group_sys.group_id = @group AND is_software_monitorado(sys_sw_software.software_name) = true ORDER BY sys_sw_software.software_name]]> </report_sql>
                <report_view_file>v_report</report_view_file>
                <report_view_contents></report_view_contents>
                <report_processing></report_processing>
                <report_sort_column>0</report_sort_column>
        </details>
        <columns>
                <column>
                        <column_order>0</column_order>
                        <column_name>Icon</column_name>
                        <column_variable>man_icon</column_variable>
                        <column_type>image</column_type>
                        <column_link></column_link>
                        <column_secondary>man_type</column_secondary>
                        <column_ternary></column_ternary>
                        <column_align>center</column_align>
                </column>
                <column>
                        <column_order>1</column_order>
                        <column_name>Type</column_name>
                        <column_variable>man_type</column_variable>
                        <column_type>text</column_type>
                        <column_link></column_link>
                        <column_secondary></column_secondary>
                        <column_ternary></column_ternary>
                        <column_align></column_align>
                </column>
                <column>
                        <column_order>2</column_order>
                        <column_name>System Name</column_name>
                        <column_variable>hostname</column_variable>
                        <column_type>link</column_type>
                        <column_link>/main/system_display/</column_link>
                        <column_secondary>system_id</column_secondary>
                        <column_ternary></column_ternary>
                        <column_align></column_align>
                </column>
                <column>
                        <column_order>3</column_order>
                        <column_name>Package Name</column_name>
                        <column_variable>software_name</column_variable>
                        <column_type>link</column_type>
                        <column_link>/report/specific_software/$group_id/</column_link>
                        <column_secondary>software_id</column_secondary>
                        <column_ternary></column_ternary>
                        <column_align>left</column_align>
                </column>
                <column>
                        <column_order>4</column_order>
                        <column_name>Version</column_name>
                        <column_variable>software_version</column_variable>
                        <column_type>text</column_type>
                        <column_link></column_link>
                        <column_secondary></column_secondary>
                        <column_ternary></column_ternary>
                        <column_align>right</column_align>
                </column>
                <column>
                        <column_order>5</column_order>
                        <column_name>Installs</column_name>
                        <column_variable>software_count</column_variable>
                        <column_type>text</column_type>
                        <column_link></column_link>
                        <column_secondary></column_secondary>
                        <column_ternary></column_ternary>
                        <column_align>center</column_align>
                </column>
                <column>
                        <column_order>6</column_order>
                        <column_name>Publisher</column_name>
                        <column_variable>software_publisher</column_variable>
                        <column_type>text</column_type>
                        <column_link></column_link>
                        <column_secondary></column_secondary>
                        <column_ternary></column_ternary>
                        <column_align>left</column_align>
                </column>
                <column>
                        <column_order>7</column_order>
                        <column_name>Type</column_name>
                        <column_variable>software_comment</column_variable>
                        <column_type>text</column_type>
                        <column_link></column_link>
                        <column_secondary></column_secondary>
                        <column_ternary></column_ternary>
                        <column_align>left</column_align>
                </column>
                <column>
                        <column_order>8</column_order>
                        <column_name>Contact</column_name>
                        <column_variable>software_url</column_variable>
                        <column_type>url</column_type>
                        <column_link></column_link>
                        <column_secondary></column_secondary>
                        <column_ternary></column_ternary>
                        <column_align>left</column_align>
                </column>
                <column>
                        <column_order>9</column_order>
                        <column_name>Google Search</column_name>
                        <column_variable></column_variable>
                        <column_type>url</column_type>
                        <column_link>https://encrypted.google.com/search?q=</column_link>
                        <column_secondary>software_name</column_secondary>
                        <column_ternary>google</column_ternary>
                        <column_align>center</column_align>
                </column>
        </columns>
</report>

Testando Query

Para testar a query, abra a página inicial do Open-AudIT e no grupo que você quiser clique em Reports e selecione o relatório criado.

O Resultado será parecido com o seguinte:
.