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

Custom report
http://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

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%'

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