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

Custom report
https://www.open-audit.org/phpBB3/viewtopic.php?f=20&t=6047
Page 1 of 1

Author:  0x13hst [ Mon Mar 11, 2013 11:56 pm ]
Post subject:  Custom report

Good Day All,

Could some one help me write MySQL select for my custom report, i trying do by my self, but without good results.

Columns:
[code]Floor, IP, Host name, User, Windows, Windows key, Office Version, Office key, CPU, RAM, HDD, Phone, Internal phone
[/code]
Floor, Phone and Internal phone are in MySQL table sys_man_additional_fields and variables are in sys_man_additional_fields_data.field_varchar

Thanks

Author:  jpa [ Wed Mar 13, 2013 4:23 am ]
Post subject:  Re: Custom report

Deleted my first partial attempt. This should be better. I left out HDD because I don't know what you want for that. No guarantees but works for me. There might be a better way to retrieve the custom values but I don't know it.

[code]
Select
system.system_id,
system.hostname,
sys_man_additional_fields_data_phone.field_varchar As phone,
sys_man_additional_fields_data_internal_phone.field_varchar As internal_phone,
sys_man_additional_fields_data_floor.field_varchar As floor,
system.os_name,
system.pc_memory,
system.man_os_name,
system.man_ip_address,
sys_sw_windows.windows_user_name,
sys_sw_software_key_windows.key_text As windows_key,
sys_sw_software_key_office.key_name As office,
sys_sw_software_key_office.key_text As office_key,
sys_hw_processor.processor_description
From
system Left Join
sys_man_additional_fields_data sys_man_additional_fields_data_phone
On sys_man_additional_fields_data_phone.system_id = system.system_id
Left Join
sys_man_additional_fields_data sys_man_additional_fields_data_internal_phone
On sys_man_additional_fields_data_internal_phone.system_id =
system.system_id Left Join
sys_man_additional_fields sys_man_additional_fields_phone
On sys_man_additional_fields_data_phone.field_id =
sys_man_additional_fields_phone.field_id Left Join
sys_man_additional_fields sys_man_additional_fields_internal_phone
On sys_man_additional_fields_data_internal_phone.field_id =
sys_man_additional_fields_internal_phone.field_id Left Join
sys_man_additional_fields_data sys_man_additional_fields_data_floor
On sys_man_additional_fields_data_floor.system_id = system.system_id
Left Join
sys_man_additional_fields sys_man_additional_fields_floor
On sys_man_additional_fields_data_floor.field_id =
sys_man_additional_fields_floor.field_id Inner Join
oa_group_sys On oa_group_sys.system_id = system.system_id Left Join
sys_sw_windows On sys_sw_windows.system_id = system.system_id And
system.timestamp = sys_sw_windows.timestamp Left Join
sys_sw_software_key sys_sw_software_key_windows
On sys_sw_software_key_windows.system_id = system.system_id And
system.timestamp = sys_sw_software_key_windows.timestamp Inner Join
sys_sw_software_key sys_sw_software_key_office
On sys_sw_software_key_office.system_id = system.system_id And
system.timestamp = sys_sw_software_key_office.timestamp Left Join
sys_hw_processor On sys_hw_processor.system_id = system.system_id And
system.timestamp = sys_hw_processor.timestamp
Where
(sys_man_additional_fields_floor.field_name = 'Floor' Or
sys_man_additional_fields_floor.field_name Is Null) And
sys_sw_software_key_office.key_name Like '%office%' And
(sys_man_additional_fields_phone.field_name = 'Phone' Or
sys_man_additional_fields_phone.field_name Is Null) And
(sys_man_additional_fields_internal_phone.field_name = 'Internal phone' Or
sys_man_additional_fields_internal_phone.field_name Is Null) And
oa_group_sys.group_id = @group And
sys_sw_software_key_windows.key_name Like '%windows%'
[/code]

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