# Elasticsearch \_CAT to excel spreadsheet

**URL:** <https://discuss.elastic.co/t/elasticsearch-cat-to-excel-spreadsheet/124567>\
**Category:** Elasticsearch\
**Created:** [March 19, 2018, 1:52pm UTC](https://discuss.elastic.co/t/elasticsearch-cat-to-excel-spreadsheet/124567 "2018-03-19T13:52:38Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jeremy\_Foran](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jeremy_foran/32/15434_2.png) [@Jeremy\_Foran](https://discuss.elastic.co/u/Jeremy_Foran)\
**Post date:** [March 19, 2018, 1:52pm UTC](https://discuss.elastic.co/t/elasticsearch-cat-to-excel-spreadsheet/124567/1 "2018-03-19T13:52:38Z")

</div>

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()
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 16, 2018, 1:52pm UTC](https://discuss.elastic.co/t/elasticsearch-cat-to-excel-spreadsheet/124567/2 "2018-04-16T13:52:43Z")

</div>

Closed on April 16, 2018, 1:52pm UTC

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