Open-AudIT
https://www.open-audit.org/phpBB3/

[help] Report to pull PC/monitor information
https://www.open-audit.org/phpBB3/viewtopic.php?f=20&t=6196
Page 1 of 1

Author:  cburbs [ Wed Aug 21, 2013 1:57 am ]
Post subject:  [help] Report to pull PC/monitor information

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.

Attachments:
Monitor_PC_Report.txt [7.2 KiB]
Downloaded 881 times

Author:  Mark [ Wed Aug 21, 2013 9:41 am ]
Post subject:  Re: [help] Report to pull PC/monitor information

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).

Author:  cburbs [ Fri Aug 23, 2013 3:02 am ]
Post subject:  Re: [help] Report to pull PC/monitor information

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

Author:  Mark [ Fri Aug 23, 2013 8:53 am ]
Post subject:  Re: [help] Report to pull PC/monitor information

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.

Author:  cburbs [ Thu Aug 11, 2016 4:03 am ]
Post subject:  Re: [help] Report to pull PC/monitor information

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

Author:  jpa [ Thu Aug 11, 2016 4:40 am ]
Post subject:  Re: [help] Report to pull PC/monitor information

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?

[code]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 [/code]

How to get there via find and replace.
[code]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
[/code]

Author:  cburbs [ Thu Aug 11, 2016 6:25 am ]
Post subject:  Re: [help] Report to pull PC/monitor information

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

Author:  jpa [ Thu Aug 11, 2016 6:33 am ]
Post subject:  Re: [help] Report to pull PC/monitor information

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.
[code]system.id -> system.system_id[/code]

Author:  Mark [ Thu Aug 11, 2016 12:36 pm ]
Post subject:  Re: [help] Report to pull PC/monitor information

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

Author:  omega4471 [ Thu Aug 11, 2016 5:14 pm ]
Post subject:  Re: [help] Report to pull PC/monitor information

Aviable only for linux not for windows.

Author:  cburbs [ Fri Aug 12, 2016 3:45 am ]
Post subject:  Re: [help] Report to pull PC/monitor information

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!

Page 1 of 1 All times are UTC + 10 hours
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/