4 answers
- 210
OK, a couple of items were incorrect in the SQL.
The table named oa_location has now changed to locations.
There is no requirement to join to a group any more - use @filter instead.
You were ordering by system.name, but it wasn't in the list of retrieved columns.The below SQL should work fine. It does have a condition of only showing devices in a location with a specific name. I assume that was intentional.
When the result is shown, the web GUI will chop characters after 30 are replaced with "...". I should make that a configuration item...
SELECT system.id AS `system.id`, system.name AS `system.name`, windows.user_name as 'windows.user_name', system.os_name AS `system.os_name`, system.form_factor AS `system.form_factor`, system.os_installation_date AS `system.os_installation_date`, system.ip AS `system.ip`, system.description AS `system.description`, floor(system.memory_count/1024) as 'system.memory_count', system.manufacturer as 'system.manufacturer', system.model AS `system.model`, system.serial as 'system.serial', processor.description as 'processor.description', processor.core_count as 'processor.core_count', disk.model as 'disk.model', floor(disk.size/1024) as 'disk.size', disk.hard_drive_index as 'disk.hard_drive_index', system.last_seen as 'system_last_seen', locations.name as 'locations.name', monitor.manufacturer as 'monitor.manufacturer', monitor.model as 'monitor.model', monitor.size as 'monitor.size', monitor.aspect_ratio as 'monitor.aspect_ratio', monitor.description as 'monitor.description', monitor.serial as 'monitor.serial' FROM system LEFT JOIN processor ON (system.id = processor.system_id and processor.current = 'y') LEFT JOIN locations ON (system.location_id = locations.id) LEFT JOIN disk ON (disk.system_id = system.id and disk.current = 'y') LEFT JOIN windows ON (system.id = windows.system_id AND windows.current = 'y') LEFT JOIN monitor on (system.id = monitor.system_id and monitor.current = 'y') WHERE @filter AND disk.hard_drive_index ='0' AND locations.name = 'Nuembrecht Rathaus' ORDER BY system.name;
- PetePossum
Works like a charm. Thanks a lot. This example should help me to get the other reports transferred.
- Mark Unwin
I've already added a new config option so users can set their own length. Look for it in the next release. It's called gui_trim_characters. :-)
- PetePossum
Hey Mark, is there any way to change the 30 character limit already today without waiting for the next release?
- Mark Unwin
That change is in the now released 2.2.1.
Add your comment... - 10-1
Thanks Paul and Mark,
I tried to extract the SQL Part and build a new report, but I received error 43 - "Parameters you have provided failed use. " May be a field does not exist anymore... have not yet found the time to check them all one by one.
Here is the content of the XML file I used in 1.21. This might not have been correct in the past, as I am not a SQL Guru, but it worked
. Thanks again for your support. Having a graphical UI to build queries would be a great feature. I mean picking the desired fields from the database via GUI.
<?xml version="1.0" encoding="ISO-8859-1"?>
<report>
<details>
<report_name>Agathes Report - Nur Rathaus</report_name>
<report_display_in_menu>y</report_display_in_menu>
<report_sql><![CDATA[SELECT system.id AS `system.id`,
system.hostname AS `system.hostname`,
windows.user_name as 'windows.user_name',
system.os_name AS `system.os_name`,
system.form_factor AS `system.form_factor`,
system.os_installation_date AS `system.os_installation_date`,
system.ip AS `system.ip`,
system.description AS `system.description`,
floor(system.memory_count/1024) as 'system.memory_count',
system.manufacturer as 'system.manufacturer',
system.model AS `system.model`,
system.serial as 'system.serial',
processor.description as 'processor.description',
processor.core_count as 'processor_corecount',
disk.model as 'disk.model',
floor(disk.size/1024) as 'disk.size',
disk.hard_drive_index as 'disk.hard_drive_index',
system.last_seen as 'system_last_seen',
oa_location.name as 'oa_location.name',monitor.manufacturer as 'monitor.manufacturer',
monitor.model as 'monitor.model',
monitor.size as 'monitor.size',
monitor.aspect_ratio as 'monitor.aspect_ratio',monitor.description as 'monitor.description',
monitor.serial as 'monitor.serial'FROM system
LEFT JOIN oa_group_sys ON (oa_group_sys.system_id = system.id)
LEFT JOIN processor ON (system.id = processor.system_id and processor.current = 'y')
LEFT JOIN oa_location ON (system.location_id = oa_location.id)
LEFT JOIN disk ON (disk.system_id = system.id and disk.current = 'y')
LEFT JOIN windows ON (system.id = windows.system_id AND windows.current = 'y')
LEFT JOIN monitor on (system.id = monitor.system_id and monitor.current = 'y')
WHERE oa_group_sys.group_id = @group AND disk.hard_drive_index ='0' AND oa_location.name = 'Nuembrecht Rathaus' ORDER BY system.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>
<report_description>GENUEM-NEU-V2</report_description>
</details>
<columns>
<column>
<column_order>0</column_order>
<column_name>PC Name</column_name>
<column_variable>system.hostname</column_variable>
<column_type>link</column_type>
<column_link>/devices/</column_link>
<column_secondary>system.id</column_secondary>
<column_ternary></column_ternary>
<column_align></column_align>
</column>
<column>
<column_order>1</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></column_align>
</column>
<column>
<column_order>2</column_order>
<column_name>OS Name</column_name>
<column_variable>system.os_name</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>3</column_order>
<column_name>IP</column_name>
<column_variable>system.ip</column_variable>
<column_type>ip_address</column_type>
<column_link></column_link>
<column_secondary></column_secondary>
<column_ternary></column_ternary>
<column_align></column_align>
</column>
<column>
<column_order>4</column_order>
<column_name>MAC</column_name>
<column_variable>system.description</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>5</column_order>
<column_name>Speicher (MB)</column_name>
<column_variable>system.memory_count</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>6</column_order>
<column_name>Hersteller</column_name>
<column_variable>system.manufacturer</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>7</column_order>
<column_name>Modell</column_name>
<column_variable>system.model</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>8</column_order>
<column_name>Seriennummer</column_name>
<column_variable>system.serial</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>9</column_order>
<column_name>Prozessor</column_name>
<column_variable>processor.description</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>10</column_order>
<column_name>Prozessor Kerne</column_name>
<column_variable>processor_corecount</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>11</column_order>
<column_name>Festplatte</column_name>
<column_variable>disk.model</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>12</column_order>
<column_name>Groesse (GB)</column_name>
<column_variable>disk.size</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>13</column_order>
<column_name>Letzter Report</column_name>
<column_variable>system_last_seen</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>14</column_order>
<column_name>Standort</column_name>
<column_variable>oa_location.name</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>15</column_order>
<column_name>Monitor Hersteller</column_name>
<column_variable>monitor.manufacturer</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>16</column_order>
<column_name>Monitor Model</column_name>
<column_variable>monitor.model</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>17</column_order>
<column_name>Mon. Groesse (Zoll)</column_name>
<column_variable>monitor.size</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>18</column_order>
<column_name>Monitor Aufloesung</column_name>
<column_variable>monitor.description</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>19</column_order>
<column_name>Monitor Seitenverh.</column_name>
<column_variable>monitor.aspect_ratio</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>20</column_order>
<column_name>Monitor SerienNr.</column_name>
<column_variable>monitor.serial</column_variable>
<column_type>text</column_type>
<column_link></column_link>
<column_secondary></column_secondary>
<column_ternary></column_ternary>
<column_align></column_align>
</column></columns>
</report>Add your comment... - 10-1
If you post your report definitions, if I have time I;ll see if I can convert them.
Add your comment... - 10-1
Hi PetePossum,
There has been a lot of changes in the database schema between versions 1.12 and 2.2 preventing custom reports from transferring properly. To get your reports working again you would have to hand edit the SQL piece in the SQL file. After that you would recreate the query in the Open-AudIT GUI by navigating to menu -> Manage -> Queries -> Create Queries or Import Queries from CSV depending on how you want to do it.
Regards,
Paul M.
Add your comment...
I want to upgrade to V2.2, and successfully transferred the database already to a new installation. Out of the box reports are working, but I had a few customized reports (stored in XML format in code_igniter\application\controllers\reports) which I want to transer as well. However, I cant find an easy way to export and import them, and the folder structure and the way the reports are stored seems to have changed, so I cant simply copy the files.
Does anybody have any advice?