ElasticSearch SQL

gistfile1.txt

POST /_sql/translate
{
  "query": """SELECT * FROM "prod-iis-*" ORDER BY "sc-status" DESC""",
  "fetch_size": 99999
}
===================================================
GET /_sql?format=csv
{
  "query": """ 
      
      Select 
        p."cs-uri-stem" "url"
      from 
        "prod-iis-*"  p
      WHERE 
        p."cs-method" = 'GET' and 
        p."@timestamp" > 'now-1d' and 
        p."cs-uri-stem" like '%.html%'
      group by p."cs-uri-stem"
      order by 1
    """
      
}
-------------------------------------------------------------------
GET /_sql?format=csv
{
  "query": """ 
      
      Select
        histogram(l."@timestamp", interval 1 hour) as h,
        count(*) as c
      from 
        "logstash-*"  l
      WHERE 
        l.cx_environment = 'PRODUCTION' and
        l.cx_level = 'ERROR' and
        l.cx_appname = 'Web.Advisory' and
        l."@timestamp" > 'now-2w'
      group by 
        h,
        l.cx_appname, 
        l.cx_level 
      order by 1
    """
      
}
----------------------------------------------------------------


GET /_sql?format=csv
{
  "query": """ 
      
      SELECT
        p."cs-uri-stem" "url",
        ROUND(PERCENTILE(p."time-taken", 95)) as percentile_duration,
        min(p."time-taken") as min_duration,
        COUNT(*) count
        
      FROM
        "prod-iis-*"  p
        
      WHERE 
        p."cs-method" = 'POST' and 
        p."@timestamp" > 'now-1h'
        
      GROUP BY 
        p."cs-uri-stem"
        
      HAVING 
        PERCENTILE(p."time-taken", 95) > 3000 or min_duration > 3000
      
      ORDER BY 
        1
    """
      
}