Security

 View Only
  • 1.  export syslog with SQL querrie

    Posted Sep 23, 2024 05:00 AM

    Hi

    Trying to get an export of syslog with sql querrie, but i cannot seem to find the schema of clearpass database så i can write the querry?

    Kinda like this:

    SELECT authlog.timestamp, authlog.username, authlog.nas_ip_address, authlog.nas_port_id, authlog.service, authlog.authentication_method, authlog.eap_type, authlog.cert_common_name, authlog.cert_serial_number, authlog.cert_issuer, authlog.cert_valid_from, authlog.cert_valid_to, authlog.cert_revocation_status, authlog.cert_subject_ou, authlog.cert_subject_country, authlog.ocsp_status, authlog.ocsp_error_details, authlog.result, authlog.failure_reason, sessionlog.session_id, sessionlog.client_ip, sessionlog.client_mac, sessionlog.start_time, sessionlog.end_time, sessionlog.duration, tacacslog.device_ip, tacacslog.command, tacacslog.privilege_level, tacacslog.response, tacacslog.result FROM authlog LEFT JOIN sessionlog ON authlog.session_id = sessionlog.session_id LEFT JOIN tacacslog ON authlog.session_id = tacacslog.session_id WHERE authlog.service IN ('RADIUS', 'TACACS+');

    but cannot find the correct schema to make the export querry validate.



  • 2.  RE: export syslog with SQL querrie

    Posted Sep 24, 2024 02:18 PM

    Have you tried setting a password for appexternal and using a psql CLI client to connect to the database?

    Also, to get at the tipsLogDb in recent CP versions you'll need to specify that it connect with "--port 5433":

    psql --host=<IP or DNS of Insight host> --port 5433 --username=appexternal --list