ES|QL Invoke-WebRequest how to

Elastic Stack Elasticsearch
1

Hello,

I’m trying to send a webrequest with a powershell script:

{
"query": 
"""
FROM packetbeat-tls 
| WHERE dnsdomain.keyword == "%domain%" AND @timestamp > NOW() - 7days 
| KEEP host.name.keyword, source.ip.keyword 
| STATS 
 all = COUNT(*) WHERE NOT host.name.keyword:"%target%", 
 target = COUNT(*) WHERE host.name.keyword:"%target%" BY source.ip.keyword 
| EVAL present = CASE( 
  all > 0 AND target >= 0, "OK", 
  all == 0 AND target > 0, "ERROR", 
  all == 0 AND target == 0, "WARN" ) 
| WHERE `present`=="ERROR" 
| KEEP present, source.ip.keyword
"""
}

*%x% == Variables

The request works well with DevTools but not within powershell.

The request:

Invoke-WebRequest -Method Post -Uri "$url/_query/async?allow_partial_results=true" -Body $body -Headers $Headers -UseBasicParsing -ContentType 'application/json'

I get this error:

Invoke-WebRequest : {"error":{"root_cause":[{"type":"x_content_parse_exception","reason":"[1:13] Unexpected character ('"' (code 34)): was expecting comma to separate Object entries\n at [Source: (byte[])"{"query": """FROM…etc

How can I send powershell query for ESQL ?

2

Hello @a11

Created a ps1 file with the script and was able to launch it via PowerShell using below command :

.\esql-test.ps1

Output :

    present    |      ip
---------------+---------------
ERROR          |93.9.229.168
ERROR          |90.44.97.144
ERROR          |226.154.89.231
ERROR          |160.20.100.193
ERROR          |96.239.18.242

Below is the ps1 file with code :

$Headers = @{
  Authorization = "ApiKey <apikey>"
}

$esql = @"
FROM kibana_sample_data_logs
| WHERE geo.dest : "US"
  AND @timestamp > NOW() - 7days
| KEEP host, ip
| STATS
    all = COUNT(*) WHERE NOT host :"%artifacts%",
    target = COUNT(*) WHERE host :"%elastic%"
  BY ip
| EVAL present = CASE(
    all < 4 AND target >= 0, "OK",
    all == 4 AND target >= 0, "ERROR",
    all == 0 AND target == 0, "WARN"
  )
| WHERE present == "ERROR"
| KEEP present, ip
| LIMIT 100
"@

$body = @{
  query = $esql
} | ConvertTo-Json

$url = "http://localhost:9200"

$response = Invoke-WebRequest `
  -Method Post `
  -Uri "$url/_query?format=txt" `
  -Headers $Headers `
  -ContentType "application/json" `
  -Body $body

$response.Content

Thanks!!

1 Like