How to export alerts from KATA to CSV [KATA/KEDRE]

[Antipova Anna]

Advice and Solutions (Forum Knowledgebase) Disclaimer. Read before using materials.

Security officers may need raw alerts data from KATA for further processing in Excel/etc.

Here's how to export all alerts from KATA database to .csv file:

KATA 3.7.2

sudo -u postgres bash -c "psql -d antiapt -c \"COPY (SELECT * FROM all_alerts) TO '/tmp/kata_alerts.csv' (format csv, delimiter ';', header, encoding 'UTF8');\""

Instead of simply copying all alerts, administrator may export only last N alerts, or play around with SQL queries:

sudo -u postgres psql antiapt -c "copy (select * from all_alerts limit N) to '/tmp/test_oneliner1.csv' (format csv, header, encoding 'UTF8');"

For example, if a specific time interval is required, it can be done like this:

sudo -u postgres bash -c "psql -d antiapt -c \"COPY (SELECT * FROM all_alerts WHERE update_time BETWEEN '2021-04-19 21:36:11'::timestamp AND '2021-05-01 13:29:57'::timestamp) TO '/tmp/kata_alerts.csv' (format csv, delimiter ';', header, encoding 'UTF8');\""

NB! Sometimes, filenames may have \r\n EOL symbols, which may affect CSV import to Excel. You can change \r\n to \n via Notepad++ or any other text processor.

P.S. To export all connected/not connected endpoints you can execute:

sudo -u postgres bash -c "psql -d antiapt -c \"COPY (SELECT * FROM agent_status) TO '/tmp/agent_status.csv' (format csv, delimiter ';', header, encoding 'UTF8');\""

KATA 4+/5+/6+

If the command above doesn't work or hangs, use the command below:

psql -U kluser -h 127.0.0.1 antiapt -c "select * from all_alerts;" > /tmp/all_alerts

Similar to previous, you can spice up the query to your taste, for example, to get time interval between now and then, execute:

psql -U kluser -h 127.0.0.1 antiapt -c "select * from all_alerts where update_time between '2021-04-19 21:36:11'::timestamp and now()::timestamp;" > /tmp/all_alerts

Then just open Excel and make import from Data -> From Text/CSV from /tmp/all_alerts (download it to local computer first).