DEPRECATED as at v2.0.

Introduction

Open-AudIT comes with many queries inbuilt. Only a few are activated by default, but you are able to active more by going to menu -> Admin -> Queries -> Activate Query.

...

Query definitions are stored as JSON XML files which can then be "activated". By activating a query you import the XML into the database which will also make the query name appear in the menu when you view a Group. Anyone can run a query. Only Admin level users can activate or deactivate a query.

...

    <details>
        <report_name>Workstation Hardware</report_name>
        <report_display_in_menu>y</report_display_in_menu>
        <report_sql><![CDATA[SELECT system.man_icon, system.man_os_family, system.system_id, system.hostname, 
                             system.man_ip_address, system.man_manufacturer, system.man_model, system.man_serial, 
                             floor(system.pc_memory / 1024) AS pc_memory, system.man_form_factor, sys_hw_processor.processor_description 
                             FROM system LEFT JOIN oa_group_sys ON system.system_id = oa_group_sys.system_id LEFT JOIN sys_hw_processor 
                             ON (sys_hw_processor.system_id = system.system_id AND sys_hw_processor.timestamp = system.timestamp) LEFT JOIN 
                             oa_location ON (system.man_location_id = oa_location.location_id) WHERE oa_group_sys.group_id = @group AND 
                             man_type = 'computer' AND man_class != 'server' GROUP BY system.hostname ORDER BY system.hostname]]></report_sql>
        <report_view_file>v_report</report_view_file>
        <report_view_contents></report_view_contents>
        <report_processing></report_processing>
        <report_sort_column>3</report_sort_column>
        <report_display_sql></report_display_sql>
        <report_description>Device details - name, ip, manufacturer, model, serial, form factor, memory, processor.</report_description>
    </details>

...

report_display_in_menu determines if the report should be shown in the menu. Certain queries are not designed to be displayed in the menu. Specific Software is one example. You need to provide a software_id to this report. It is designed to be called form from the Installed Software report.

...

report_view_file is normally not set and will default to v_report. Unless your view file needs to contain something special in terms of extra processing PHP code, just leave this blank.

report_view_contents is not used. This was originally designed to contain extra PHP processing codethe view file contents (see report_view_file). Set the report_view_file instead of using this.

report_processing is not used. This was originally designed to contain extra PHP processing code. 

report_display_sql is not used.

report_sort_column is the initial sort column id (starting at 0).

...

The column attributes are again reasonably self evident.

• column_id is the numeric order of the column on the displayed query page.
• column_name is the header value for the column.
• column_variable is the name of the variable returned from the SQL statement above.
• column_type can be set to 'text', 'link', 'image', 'ip_address', 'multi', 'url', 'timestamp'. Formatting for each type is below:

  ...

  • • text - simply show the output

  ...

                       link - use the column_link variable to create a hyperlink and append the column_secondary value. Typically used to create a link to a device.

  ...

  • • .

    • timestamp - currently outputs as per 'text' type.

    • url - and external link is created that points to the value of column_link. Will be shown using an icon instead of the actual text.

    • multi - is not used.

    • ip_address - format the output to de-pad the ip address. IP Addresses are stored in the database padded, so an ip address thus 192.168.1.1 would be internally stored as 192.168.001.001.

    • image - If column_name == Icon, use an image in /var/www/open-audit/theme-tango/tango-images/16_*.png where * is the value from column_variable.

  ...

  • •  If column_name == Picture, use an image in /var/www/open-audit/device_images/*.jpg where * is the value from column_variable.

                       ip_address - format the output to de-pad the ip address. IP Addresses are stroed in the database padded, so an ip address thus 192.168.1.1 would be internally stored as 192.168.001.001.

                       multi - is not used.

                       url - and external link is created that points to the value of column_link. Will be shown using an icon instead of the actual text.

  ...

  • • link - use the column_link variable to create a hyperlink and append the column_secondary value. Typically used to create a link to a device.

  • column_link is used to set the external link when column_type is 'url'.
  • column_secondary is appended where required when column_type is 'link'.
  • column_ternary is appended where required when column_type is 'link'.
  • column_align is the alignment of the data in the displayed column.

  Report SQL

  So the main piece of code of interest is report_sql. This defines what data is extracted from the database.

  When a device is queried, most of it's specific information is stored in the 'system' table. If data from another table (an attribute table, say hard_drive) is required a join must be created.

  With the release of 1.10, the below has changed (with the exception of the sys_hw_network_card_ip table). See the release notes for 1.10 here - Release Notes for Open-AudIT v1.10

  There is now only a single join required an attribute table to the system table. As well, most tables have been renamed. Using the old example below, we would select items like this:

  Code Block
  language sql
  processor.system_id = system.system_id and processor.current = "y"

   

  Open-AudIT stores its data using two main keys. sys_hw_processor.system_id = system.system_id is pretty obvious. The second key should use the timestamps. This way you will return current attribute rows. If you omit this you will receive all attribute rows, whether they're current or not. So sys_hw_processor.timestamp = system.timestamp. FYI - Using the timestamps you can easily find out what's not current by specifying sys_hw_processor.timestamp != system.timestamp.

  So your attribute joins should look like this:

  Code Block
  language sql
  LEFT JOIN sys_hw_processor ON (sys_hw_processor.system_id = system.system_id AND sys_hw_processor.timestamp = system.timestamp)

  ...

  The other join that should appear in a report_sql statement is the group join. In order to run a query on a particular group, the group id is passed for you by the front-end and you can reference it via @group. You need to create a join like thus: 

  Code Block
  language sql
  LEFT JOIN oa_group_sys ON system.system_id = oa_group_sys.system_id

  ...