Export formatted. Export agents as csv

[Fco. Javier]

Hello;

I know that I can get a CSV file from the Wazuh console through the "Export formatted" button ( https://documentation.wazuh.com/current/user-manual/agents/listing/wazuh-dashboard.html ).

My problem is that I need to be able to get said file from the CLI/bash/python  without having to open the browser. I have been trying to make a query to the API ( /agents ) and treat the result obtained with jq but I have problems when parsing the groups of the agents if they belong to several groups since according to jq, the groups are in a array ( and we enter something that is beyond my capabilities :-(   )

Example with jq. The export file is obtained after a call to the Wazuh API ( /agents ). For convenience, this example is made only with the fields id, name, group, ip, os.uname

cat export.json | jq -r '.data.affected_items[] | .id + "," + .name + "," + .group[] + "," + .ip + "," + .os.uname'

002,SRVWIN01,default,192.168.50.10,Microsoft Windows Server 2012 R2 Standard
002,SRVWIN01,windows_servidores,192.168.50.10,Microsoft Windows Server 2012 R2 Standard
002,SRVWIN01,web_server,192.168.50.10,Microsoft Windows Server 2012 R2 Standard
004,SRVWIN02,default,192.168.50.12,Microsoft Windows Server 2012 Standard
004,SRVWIN02,windows_servidores,192.168.50.12,Microsoft Windows Server 2012 Standard
007,SRVWIN03,default,192.168.50.13,Microsoft Windows Server 2012 Standard
007,SRVWIN03,windows_servidores,192.168.50.13,Microsoft Windows Server 2012 R2 Standard
008,SRVWIN04,default,192.168.50.14,Microsoft Windows Server 2012 R2 Standard
018,SRVLINUX,default,192.168.50.20,Linux |srvlinux |3.10.0-862.9.1.el7.x86_64 |#1 SMP Mon Jul 16 16:29:36 UTC 2018 |x86_64

As we can see, the server SRVWIN01 is assigned three groups and appears three times instead of appearing on a single line indicating the three groups. Desired output

002,SRVWIN01,default windows_servidores web_server,192.168.50.10,Microsoft Windows Server 2012 R2 Standard

04,SRVWIN02,default windows_servidores,192.168.50.12,Microsoft Windows Server 2012 Standard
007,SRVWIN03,default windows_servidores,192.168.50.13,Microsoft Windows Server 2012 Standard
008,SRVWIN04,default,192.168.50.14,Microsoft Windows Server 2012 R2 Standard
018,SRVLINUX,default,192.168.50.20,Linux |srvlinux |3.10.0-862.9.1.el7.x86_64 |#1 SMP Mon Jul 16 16:29:36 UTC 2018 |x86_64

In summary:

• Could someone tell me how to obtain a .csv file of the list of agents from the CLI without having to do it through a browser?

• How could I get a .csv file of the agents list using jq whose format is similar to the one obtained from the 'Export formatted' button (https://documentation.wazuh.com/current/user-manual/agents/listing/wazuh- dashboard.html) ?

thank you all

[Carlos Dams]

Hi Fco. Javier,

Thanks for using Wazuh!

Allow me some time to evaluate this, 

are you using a script to get this export file? could you provide the full details of this script or the commands used?

Thanks,

[Fco. Javier]

Hi Carlos;

Sure! Here is the script I use.

It should be noted that the API query is made via HTTP and not HTTPS. For convenience I only extract some fields ( id, name, group, ip, os.uname ).

#!/bin/bash

echo "- Obteniendo token..."
TOKEN=$(curl -s -u USER:PASSWORd -k -X GET "http://localhost:55000/security/user/authenticate?raw=true")

echo "- Obteniendo agentes - export.json..."
curl -s -k -XGET "http://localhost:55000/agents?pretty=true&select=id,name,group,ip,os.uname" -H  "Authorization: Bearer $TOKEN" --output export.json

echo "- Aplicando jq..."
# .group[]? --> Omite agentes si le falta algún campo
# .group[]  --> Si a algún agente le falta algún campo devuelve error.
cat export.json | jq -r '.data.affected_items[] | .id + "," + .name + "," + .group[]? + "," + .ip + "," + .os.uname'

Regards,

PD: I have published this same message before but later I realized that it contained some error and I deleted it.

[Fco. Javier]

Hello;

You know... Break the problem into smaller parts.

Finally I have been able to get the information more or less as I wanted although in a slightly more shabby way. For now, here my solution in case someone else can use it.

#!/bin/bash

echo "- Obteniendo token..."

TOKEN=$(curl -s -u USER:PASSWORD -k -X GET "http://localhost:55000/security/user/authenticate?raw=true")

echo "- Obteniendo info de los agentes..."
curl -s -k -XGET "http://localhost:55000/agents?pretty=true&select=id,name,ip,os.uname" -H  "Authorization: Bearer $TOKEN" | jq -r '.data.affected_items[]| .id + "," + .name + "," + .ip + "," + .os.uname' >> tmp.txt

# Por cada cliente:
# - Obtener el ID de cada cliente
# - Guardar en 'info' datos del agente.
# - Consultar los grupos de cada agente y guardarlo en variable 'grupos'.
# - Generar mensaje de salida con id de cliente, info y grupos. Guardar en fichero csv.csv.
echo "- Obteniendo grupos y generando salida deseada..."
for i in `cut -d"," -f1 tmp.txt`
do
        info=`grep "${i}," tmp.txt | cut -d"," -f2- `
        grupos=`curl -s -k -XGET "http://localhost:55000/agents?pretty=true&select=group&q=id=$i" -H "Authorization: Bearer $TOKEN" | jq -r -c '.data.affected_items[].group'`
        echo "${i},${info},${grupos}" >> csv.csv
done

I'm sure it has to do with some much more efficient way, if someone can give another possibility I would appreciate it.

[Fco. Javier]

Sorry, I made a small logic error in the script. Here the updated and I hope the final one.

#!/bin/bash

echo "- Obteniendo token..."
TOKEN=$(curl -s -u USER:PASSWORD -k -X GET "http://localhost:55000/security/user/authenticate?raw=true")

echo "- Obteniendo info de los agentes..."
curl -s -k -XGET "http://localhost:55000/agents?pretty=true&select=id,name,ip,os.uname" -H  "Authorization: Bearer $TOKEN" | jq -r '.data.affected_items[]| .id + "," + .name + "," + .ip + "," + .os.uname' >> tmp.txt

# Por cada cliente:
# - Obtener el ID de cada cliente
# - Guardar en 'info' datos del agente.
# - Consultar los grupos de cada agente y guardarlo en variable 'grupos'.
# - Generar mensaje de salida con id de cliente, info y grupos. Guardar en fichero csv.csv.
echo "- Obteniendo grupos y generando salida deseada..."
for i in `cut -d"," -f1 tmp.txt`
do

info=`grep "^${i}," tmp.txt | cut -d"," -f2- `

grupos=`curl -s -k -XGET "http://localhost:55000/agents?pretty=true&select=group&q=id=$i" -H "Authorization: Bearer $TOKEN" | jq -r -c '.data.affected_items[].group'`
echo "${i},${info},${grupos}" >> csv.csv
done

Example to get agent ID, name, IP and groups to which it belongs:

$bash export.v3fix.sh
- Obteniendo token...

- Obteniendo info de los agentes...

- Obteniendo grupos y generando salida deseada...

000,wazuh,127.0.0.1,Linux |wazuh |4.19.0-10-amd64 |#1 SMP Debian 4.19.132-1 (2020-07-24) |x86_64,null
002,SRVWIN01,192.168.50.10,Microsoft Windows Server 2012 R2 Standard,["default","windows_servidores","web_server"]
004,SRVWIN02,192.168.50.12,Microsoft Windows Server 2012 Standard,["default","windows_servidores"]
007,SRVWIN03,192.168.50.13,Microsoft Windows Server 2012 R2 Standard,["default","windows_servidores"]
008,SRVWIN04,192.168.50.14,Microsoft Windows Server 2012 R2 Standard,["default"]
018,SRVLINUX,192.168.50.20,Linux |srvlinux |3.10.0-862.9.1.el7.x86_64 |#1 SMP Mon Jul 16 16:29:36 UTC 2018 |x86_64,["default"]

Thank you all and thank you for making this product and this community possible. And if someone knows a more efficient solution to this issue, please comment it.

[Carlos Dams]

I think you already figured out a good option, 

As another option here I am sharing a python script that brings all the information from the Wazuh Agents

[Fco. Javier]

Thanks Carlos, I think I'll take yours :-)