Open-AudIT

So i am trying to create a report to pull in PC info with all monitors associated to the device. Most people have two monitors and some 3. I have it where it can pull in everything but can't figure out how to pull in data for monitor 2 and monitor 3.

The problem is that you are pulling info that would result in more than one row per PC (multiple monitors) but then grouping by hostname (making one row per PC). If you remove the "GROUP BY system.hostname" from your report_sql, it should work. NOTE that you will get multiple rows per PC (when they have more than one monitor).

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.

That worked thanks as always.

Do build off of this is there a way to do an "Alert" for when something has changed for a monitor?

Say my PC has monitor a1, b1, and c3 on first pull.

Next time I do a pull it has a2, b1, and c3....with a2 being the new monitor?

Thanks

If an audit is submitted and a monitors manufacturer, model or serial changes it should generate an alert (in this case - new monitor). And conversely if an existing computer has a monitor that is not detected, an alert should also be generated.
You can check an individual system at its Device Details page, then on the left menu go to Summary -> Alert Log.
If you want a Report (for all devices in a given Group), take a look at the "Alerts - Software" XML report definition.
You should be able to modify it to enumerate monitors easily enough.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.

Looks like since the last update I get an error now -

A Database Error Occurred

Error Number: 1146

Table 'openaudit.sys_sw_windows' doesn't exist

SELECT system.system_id, system.hostname, system.man_owner, floor(system.pc_memory / 1024) AS pc_memory, sys_hw_processor.processor_description, system.man_manufacturer, system.man_model, system.man_serial, system.man_icon, date(system.last_seen) AS last_seen, system.last_seen_by, system.man_owner, system.man_os_family, sys_sw_windows.windows_client_site_name, sys_sw_windows.windows_user_name, oa_location.location_name, sys_hw_monitor.monitor_id, sys_hw_monitor.manufacturer, sys_hw_monitor.serial, sys_hw_monitor.model FROM system LEFT JOIN oa_group_sys ON system.system_id = oa_group_sys.system_id LEFT JOIN sys_sw_windows ON (system.system_id = sys_sw_windows.system_id AND system.timestamp = sys_sw_windows.timestamp) LEFT JOIN sys_hw_processor ON (sys_hw_processor.system_id = system.system_id AND sys_hw_processor.timestamp = system.timestamp) LEFT JOIN sys_hw_monitor ON (sys_hw_monitor.system_id = system.system_id AND sys_hw_monitor.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' ORDER BY system.hostname

Filename: models/m_oa_report.php

Line Number: 219

What needs to be fixed on this?

Thanks

Lot's of stuff and it may need to be updated again when going to 1.12.8.

My OA is on 1.12.8 right now so the following works on 1.12.8. Probably just skip the system table changes for 1.12.6?



How to get there via find and replace.

I am running 1.12

With new select statement......

Error Number: 1054

Unknown column 'system.id' in 'field list'

Select system.id, system.hostname, system.owner, floor(system.memory_count / 1024) As pc_memory, processor.description, system.manufacturer, system.model, system.serial, system.icon, Date(system.last_seen) As last_seen, system.last_seen_by, system.owner, system.os_family, windows.client_site_name, windows.user_name, oa_location.name, monitor.id, monitor.manufacturer, monitor.serial, monitor.model From system Left Join oa_group_sys On system.id = oa_group_sys.system_id Left Join windows On system.id = windows.system_id Left Join processor On processor.system_id = system.id Left Join monitor On monitor.system_id = system.id Left Join oa_location On system.location_id = oa_location.id Where oa_group_sys.group_id = @group And system.type = 'computer' And system.class != 'server' And windows.current = 'y' And processor.current = 'y' And monitor.current = 'y' Order By system.hostname

Filename: models/m_oa_report.php

Line Number: 219

That was for 1.12.8 and you're probably on 1.12.6 which is the current public release. I don't have a 1.12.6 anymore so you'll need to roll back some of the changes I made.

So start with the following and see where it errors.

1.12.8 is now available. Upgrade to it and retry JPA's suggested fix. Should work fine.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.

Aviable only for linux not for windows.

Ok got it to work. Had to go through each of these one by one until it worked

sys_hw_ -> ""
sys_sw_ -> ""
system.system_id -> system.id
man_ -> ""
system.pc_memory -> system.memory_count
processor.processor_description -> processor.description
windows.windows_ -> windows.
oa_location.location_ -> oa_location.
monitor.monitor_ -> monitor.
timestamp -> last_seen
Remove timestamp = timestamp from where clause and replace with table.current = y

Thanks!