Wazuh Opensearch query

[Matteo]

Hi

where can I create a query in the interface of wazuh (4.3) that allows me to use a language similar to sql? For example
SELECT ip FROM wazuh-alert WHERE desc = 'ssl login failed' GROUP BY ip HAVING count (ip)> 3?
Than I need to export the results in csv

Thanks

[Alberto Rodriguez]

Hello Matteo

You can use Dev Tools: click on the left menu, then click Dev Tools, under Management. Example:

POST _plugins/_sql
{
  "query": "SELECT * FROM wazuh-alerts-4.x-2022.06.28 LIMIT 50"
}

​

[Matteo]

Thanks Alberto

how can I save the result in CSV and schedule the query?

[Alberto Rodriguez]

Based on opensearch SQL plugin documentation: https://opensearch.org/docs/latest/search-plugins/sql/index/#rest-api, you can use the format modifier to get the results in CSV format:

POST _plugins/_sql?format=csv
{
  "query": "SELECT * FROM wazuh-alerts-4.x-2022.06.28 LIMIT 50"
}

This kind of query can’t be scheduled using the web user interface yet. But you can configure a Cron Job that uses this query with curl for instance and save the results into a file or whatever.

Don’t hesitate to let me know if you have any questions about how to achieve this.
Regards

--
You received this message because you are subscribed to the Google Groups "Wazuh mailing list" group.
To unsubscribe from this group and stop receiving emails from it, send an email to wazuh+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/926c1204-30e6-4a2a-9fc3-f84962a2791en%40googlegroups.com.

[Matteo]

Thanks Alberto

can I have an example of use of curl command?

With postman I connect to wazuh api but I can't undestand how can i query to Opensearch

[Alberto Rodriguez]

Sure,

curl -XPOST "https://10.0.2.34:9200/_plugins/_sql?format=csv" -k -u admin:password  -H 'Content-Type: application/json' -d'
{
  "query": "SELECT * FROM wazuh-alerts-4.x-2022.06.28 LIMIT 50"
}
'

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/ebda6609-07a6-4771-b649-89d4673f4705n%40googlegroups.com.

--

[Matteo]

Hi Alberto

when I put this command, i receive

curl: (7) Failed connect to wazuh.domain.local:9200; Connection refused

[Alberto Rodriguez]

Hello Matteo

  The curl command should be run into the indexer host. Check if your indexer/elasticsearch is listening, for instance: `netstat -tunap | grep 9200`. Maybe you have it running on another port. I forgot to mention, check it with local IP instead of localhost or 127.0.0.1. Please let me know about the netstat command or if you tried with the local IP. 

[Matteo]

Hello Alberto

Here results of netstat

[root@wazuh-server wazuh-user]# netstat -tunap | grep 9200
tcp        0      0 127.0.0.1:56848         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50888         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50624         127.0.0.1:9200          ESTABLISHED 2106/filebeat      
tcp        0      0 127.0.0.1:50612         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:52052         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:52060         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50884         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:37008         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:52056         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:59538         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:52054         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50626         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:56852         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:56838         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:56846         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50892         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:56840         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:37018         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:56842         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:44628         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:52058         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:56844         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:56850         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50628         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50616         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50886         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:59536         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:50890         127.0.0.1:9200          ESTABLISHED 853/node            
tcp        0      0 127.0.0.1:44626         127.0.0.1:9200          ESTABLISHED 853/node            
tcp6       0      0 127.0.0.1:9200          :::*                    LISTEN      2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:44628         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50886         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50892         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:37018         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:56844         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:52058         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:52056         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:56850         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:56846         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:56842         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50612         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50616         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50626         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:56838         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50884         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50890         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50624         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:52054         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50628         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:44626         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:50888         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:56840         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:56848         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:56852         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:59538         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:59536         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:37008         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:52060         ESTABLISHED 2108/java          
tcp6       0      0 127.0.0.1:9200          127.0.0.1:52052         ESTABLISHED 2108/java     

now curl is ok from wazuh server, but

How can i connect from another PC to 9200? 

Now I receive connection refused

Thanks