Open-AudIT

What's on your network?
It is currently Tue Jan 16, 2018 11:12 pm

All times are UTC + 10 hours




Post new topic Reply to topic  [ 11 posts ] 
Author Message
PostPosted: Wed Aug 21, 2013 1:57 am 
Offline
Contributor

Joined: Thu May 03, 2007 10:38 am
Posts: 116
Location: Midwest
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 235 times
Top
 Profile  
Reply with quote  
PostPosted: Wed Aug 21, 2013 9:41 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
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.


Top
 Profile  
Reply with quote  
PostPosted: Fri Aug 23, 2013 3:02 am 
Offline
Contributor

Joined: Thu May 03, 2007 10:38 am
Posts: 116
Location: Midwest
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


Top
 Profile  
Reply with quote  
PostPosted: Fri Aug 23, 2013 8:53 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
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.


Top
 Profile  
Reply with quote  
PostPosted: Thu Aug 11, 2016 4:03 am 
Offline
Contributor

Joined: Thu May 03, 2007 10:38 am
Posts: 116
Location: Midwest
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


Top
 Profile  
Reply with quote  
PostPosted: Thu Aug 11, 2016 4:40 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1223
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


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


Top
 Profile  
Reply with quote  
PostPosted: Thu Aug 11, 2016 6:25 am 
Offline
Contributor

Joined: Thu May 03, 2007 10:38 am
Posts: 116
Location: Midwest
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


Top
 Profile  
Reply with quote  
PostPosted: Thu Aug 11, 2016 6:33 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1223
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


Top
 Profile  
Reply with quote  
PostPosted: Thu Aug 11, 2016 12:36 pm 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
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.


Top
 Profile  
Reply with quote  
PostPosted: Thu Aug 11, 2016 5:14 pm 
Offline
Helper

Joined: Wed Aug 08, 2012 1:49 am
Posts: 59
Aviable only for linux not for windows.


Top
 Profile  
Reply with quote  
PostPosted: Fri Aug 12, 2016 3:45 am 
Offline
Contributor

Joined: Thu May 03, 2007 10:38 am
Posts: 116
Location: Midwest
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!


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 6 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group