How to export alerts from KATA to CSV [KATA/KEDRE]
Entry posted by svc_kms in How-to
18 views
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).
0 Comments
Recommended Comments
There are no comments to display.
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now