Open-AudIT

Hello forum,

I just created a my own report, it works but loading result page slow extremely.
When I remove 2 lines below, the responding time is very fast then, please help me to fix this.
Thanks!

[code]sys_sw_software_key.key_text,[/code]
[code]INNER JOIN sys_sw_software_key ON sys_sw_software_key.system_id = system.system_id[/code]

My report
[code]
<?xml version="1.0" encoding="ISO-8859-1"?>
<report>
<details>
<report_name>Full - Motherboard</report_name>
<report_display_in_menu>y</report_display_in_menu>
<report_sql><![CDATA[
SELECT
system.first_timestamp,
system.`timestamp`,
system.hostname,
system.man_ip_address,
sys_hw_network_card_ip.net_mac_address,
sys_sw_windows.windows_user_name,
system.os_name,
system.os_version,
system.pc_os_bit,
sys_sw_software_key.key_text,
system.model,
sys_hw_motherboard.manufacturer,
sys_hw_motherboard.serial,
sys_hw_motherboard.processor_type,
system.form_factor
FROM
system
INNER JOIN sys_sw_windows ON sys_sw_windows.system_id = system.system_id
INNER JOIN sys_hw_motherboard ON sys_hw_motherboard.system_id = system.system_id
INNER JOIN sys_hw_network_card_ip ON sys_hw_network_card_ip.system_id = system.system_id
INNER JOIN sys_sw_software_key ON sys_sw_software_key.system_id = system.system_id
GROUP BY system.hostname
ORDER BY hostname
]]></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>First Scan</column_name>
<column_variable>first_timestamp</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>1</column_order>
<column_name>Latest Scan</column_name>
<column_variable>timestamp</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>2</column_order>
<column_name>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>left</column_align>
</column>
<column>
<column_order>3</column_order>
<column_name>IP Address</column_name>
<column_variable>man_ip_address</column_variable>
<column_type>ip_address</column_type>
<column_link></column_link>
<column_secondary></column_secondary>
<column_ternary></column_ternary>
<column_align>left</column_align>
</column>
<column>
<column_order>4</column_order>
<column_name>MAC</column_name>
<column_variable>net_mac_address</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>5</column_order>
<column_name>User</column_name>
<column_variable>windows_user_name</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>6</column_order>
<column_name>Domain</column_name>
<column_variable>domain</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>7</column_order>
<column_name>Operating System</column_name>
<column_variable>os_name</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>Service Pack</column_name>
<column_variable>os_version</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>9</column_order>
<column_name>OS Bit</column_name>
<column_variable>pc_os_bit</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>10</column_order>
<column_name>Product Key</column_name>
<column_variable>key_text</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>11</column_order>
<column_name>Motherboard</column_name>
<column_variable>model</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>12</column_order>
<column_name>Manufacturer</column_name>
<column_variable>manufacturer</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>13</column_order>
<column_name>Motherboard Serial</column_name>
<column_variable>serial</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>14</column_order>
<column_name>CPU Socket</column_name>
<column_variable>processor_type</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>15</column_order>
<column_name>Form</column_name>
<column_variable>form_factor</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>
</columns>
</report>
[/code]

Your query is not linking the timestamp field between tables. You probably want to link the system.os_name and sys_sw_software_key.key_name fields. Your link between system and sys_hw_network_card_ip is probably returning multiple rows on machines with multiple network cards or IPV4 and IPV6 addresses. You're using this to get a mac address so you might want to filter out the IPV6 stuff (or V4 depending) and deal with multiple network cards somehow as well.

Hello jpa,

Since I'm not familiar with mysql so could you please help me with the fixed xml.
Thanks!

My SQL isn't great either and I'm guessing a bit on what you want to do but...

[code]
Select
system.first_timestamp,
system.timestamp,
system.hostname,
system.man_ip_address,
sys_hw_network_card_ip.net_mac_address,
sys_sw_windows.windows_user_name,
system.os_name,
system.os_version,
system.pc_os_bit,
sys_sw_software_key.key_text,
system.model,
sys_hw_motherboard.manufacturer,
sys_hw_motherboard.serial,
sys_hw_motherboard.processor_type,
system.form_factor
From
system Inner Join
sys_sw_windows On sys_sw_windows.system_id = system.system_id And
system.timestamp = sys_sw_windows.timestamp Inner Join
sys_hw_motherboard On sys_hw_motherboard.system_id = system.system_id And
system.timestamp = sys_hw_motherboard.timestamp Inner Join
sys_hw_network_card_ip On sys_hw_network_card_ip.system_id = system.system_id
And system.timestamp = sys_hw_network_card_ip.timestamp And
system.man_ip_address = sys_hw_network_card_ip.ip_address_v4 Inner Join
sys_sw_software_key On sys_sw_software_key.system_id = system.system_id And
system.timestamp = sys_sw_software_key.timestamp And system.os_name =
sys_sw_software_key.key_name Inner Join
oa_group_sys On oa_group_sys.system_id = system.system_id
Where
oa_group_sys.group_id = @group
Order By
system.hostname[/code]
Try that and see what is or is not working for you.

Thanks jpa, but oops... the output is just NULL

[url]http://s24.postimg.org/5s1s8tx85/2014_04_18_080740.png[/url]

When creating a proper report the SQL needs a where clause which filters for the group the report is being run against.

If you're testing you need to replace the @group with 1 which is the default "All Computers" group.

As per JPA, when testing in MySQL you can also do:
[code]set @group=1;[/code]
And then run the SQL query as is (leaving @group in it).

_________________
Support and Development hours available from [url=https://opmantek.com]Opmantek[/url].
Please consider a purchase to help make Open-AudIT better for everyone.

[quote="Mark"]As per JPA, when testing in MySQL you can also do:
[code]set @group=1;[/code]
And then run the SQL query as is (leaving @group in it).

Run the set @group query in your query builder. It will not return any rows but will set the @group [url=http://dev.mysql.com/doc/refman/5.0/en/user-variables.html]user-defined variable[/url].