Index Search Percentege (_sql or _search)

Elastic Stack Elasticsearch
1

Hi Everyone,

I'd like to get the total percentage of _doc for each Server in a _search or _sql API request.

In both options I can get total docs for the server and the total of all docs, Now I need a way to do calculate the percentage. But I don't know how, Following my Requests:

The sql solution will be the best solution for my necessity. But I can use the _search too.

Calc that I can't to do:

(100 * doc_count_server / Total_doc)

_Search Request:

  "query": {
    "range": {
      "@timestamp": {
				"time_zone": "-03:00",        
        "gte": "now-15m", 
        "lte": "now"                  
      }
    }
  },
	"size": 0,
  "aggs": {
    "Total_doc": {
      "value_count": {
        "field": "S_ComputerName"
      }
  	},
		"total_per_server": {
              "terms": {
                "field": "S_ComputerName"
             }
        }
     }

Result:

{
     "took": 159,
     "timed_out": false,
     "_shards": {
       "total": 4,
       "successful": 4,
       "skipped": 0,
       "failed": 0
     },
     "hits": {
       "total": {
         "value": 10000,
         "relation": "gte"
       },
       "max_score": null,
       "hits": []
     },
     "aggregations": {
       "Total_doc": {
         "value": 30976
       },
       "total_per_server": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
           {
             "key": "SERPW32B",
             "doc_count": 15542
           },
           {
             "key": "SERPW32C",
             "doc_count": 15413
           },
           {
             "key": "SERPW32A",
             "doc_count": 21
           }
         ]
       }
     }
   }

_SQL request:

{"query": "select \"S_ComputerName\" , count(1) FROM \"INDEX*\" a WHERE \"@timestamp\" > CURRENT_TIMESTAMP() - INTERVAL 30 MINUTES and \"CS_URI_Stem\" LIKE '%start.swe' group by \"S_ComputerName\" "}

Result:

{
      "columns": [
        {
          "name": "S_ComputerName",
          "type": "keyword"
        },
        {
          "name": "count(1)",
          "type": "long"
        }
      ],
      "rows": [
        [
          "SERPW32A",
          44
        ],
        [
          "SERPW32B",
          32156
        ],
        [
          "SERPW32C",
          32248
        ]
      ]
    }
2

If I understand correctly, and since you want the GROUP BY, you'd need to use subselects, to also have the count(*) and then do the calculation in one query. I'm afraid that this is not possible currently as the support for subselects is quite limited.

You'd have to execute 2 queries and do the calculation on the client side.

1 Like
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.