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/ |