Elasticsearch _CAT to excel spreadsheet

Wrote a little script to generate an excel spreadsheet with the results of a GET _cat/indices. Thought I would share.

from elasticsearch import Elasticsearch

import xlsxwriter
############################################################
# Settings

url         = "http://localhost"
port        = 9200
username    = 'username'
password    = 'password'

index       = "*" # A comma-separated list of index names to limit the returned information

### DONT CHANGE THESE
byte_format = 'b' # valid choices are: ‘b’, ‘k’, ‘m’, ‘g’
verbose     = True

output_file_name   = "elasticsearch_indices_report.xlsx"

### Formulas
total_primaries_visable         = '=SUBTOTAL(9,OFFSET(L8,0,0,COUNT(L8:L10000)))/1073741824'
total_primes_replics_visable    = '=SUBTOTAL(9,OFFSET(K8,0,0,COUNT(K8:K10000)))/1073741824'

total_primaries                 = '=SUM(OFFSET(L8,0,0,COUNT(L8:L10000)))/1073741824'
total_primes_repllics           = '=SUM(OFFSET(K8,0,0,COUNT(K8:K10000)))/1073741824'

percent_primes                  = '=F3/G3'
percent_primes_replicas         = '=F4/G4'

Displaying                      = '=SUBTOTAL(3,C:C)'

### Labels
e3  = 'Total Of Primaries'
e4  = 'Total Of Primaries and Replicas'

f2  = 'Visable (Gigbytes)'
g2  = 'ALL (GigBytes)'
h2  = 'Percent'
j2  = 'Displaying'

############################################################
# Write results to excel file
workbook    = xlsxwriter.Workbook(output_file_name)
sheet       = workbook.add_worksheet()

gigbyte_format  = workbook.add_format()
doc_count       = workbook.add_format()

gigbyte_format.set_num_format('#,##0.00')
doc_count.set_num_format('#,##0')

sheet.set_column("I:J", None, doc_count)

sheet.write("E3", e3)
sheet.write("E4", e4)

sheet.write("F2", f2)
sheet.write("G2", g2)
sheet.write("H2", h2)
sheet.write("J2", j2)

sheet.write("F3", total_primaries_visable, gigbyte_format)
sheet.write("F4", total_primes_replics_visable, gigbyte_format)

sheet.write("G3", total_primaries, gigbyte_format)
sheet.write("G4", total_primes_repllics, gigbyte_format)

sheet.write("H3", percent_primes)
sheet.write("H4", percent_primes_replicas)

sheet.write("K2", Displaying)


###### Aquire Data
es = Elasticsearch([url],  port=port, http_auth=(username,password))

es_data = es.cat.indices(index,bytes=byte_format,v=verbose)

############################################################
### Parsing Data

lines = iter(es_data.splitlines())

headers = next(lines).strip("\n").split()

data = []

for line in lines:
    row_data = line.strip("\n").split()

    for i, item in enumerate(row_data):
        try:
            row_data[i] = float(item)
        except ValueError:
            pass

    data.append(row_data)

sheet.write_row('C7', headers)

row_count       = len(row_data)
column_count    = len(headers)

sheet.autofilter(6, 2, row_count - 1, 2 + column_count - 1)

row = 7
for row_data in data:
    region = row_data[0]

    sheet.write_row(row, 2, row_data)

    # Move on to the next worksheet row.
    row += 1

workbook.close()

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