Wiki.Cyli.Org

This script converts a bunch of json queries into a single .csv file with headers

#!/bin/bash
# This script queries https://centralservices.com/corporate/companyaccounts
# and pulls down a json array containing a list of company codes and the data
# base that they're connected to at the time of running.
#
# The data is dumped to a .csv file to be imported into graylog;
# https://go2docs.graylog.org/5-0/making_sense_of_your_log_data/lookup_tables.html
#
# This script requires that jq be installed
#
# Steve Jones, 2023-12-15
#
# The web request is;
# curl -s 'https://centralservices.com/corporate/companyaccounts?\
# embeds=databaseserver&fields=EntityID,DatabaseServerID,DatabaseServer.\
# DatabaseServerID,DatabaseServer.DatabaseServerName&pageSize=3&pageNumber=1'
#
# Data from the web request is presented as;
# [
#   {
#     "EntityID": "abbot",
#     "DatabaseServerID": 9,
#     "DatabaseServer": {
#       "DatabaseServerID": 9,
#       "DatabaseServerName": "xdb11"
#     }
#   },
#   {
#     "EntityID": "aces",
#     "DatabaseServerID": 11,
#     "DatabaseServer": {
#       "DatabaseServerID": 11,
#       "DatabaseServerName": "xdb13"
#     }
#   },
#   {
#     "EntityID": "ahunts",
#     "DatabaseServerID": 28,
#     "DatabaseServer": {
#       "DatabaseServerID": 28,
#       "DatabaseServerName": "xdb18"
#     }
#   }
# ]
#
# An issue that presents itselfs is that duplicates appear, my guess is that they
# occur due to multiple users are logged into the same database.
#
# The jq query is;
# jq -r '.[] | [.EntityID, .DatabaseServer.DatabaseServerName] | @csv'
#
# An added feature is that the .csv file needs to have headers, that string is;
# "companycode","dbserver"
#
# And the final result needs to look like;
# "companycode","dbserver"
# "abbot","xdb11"
# "aces","xdb13"
# "ahunts","xdb18"
# "...","..."
#
#
# All good script start with declaring their variables;
# Keep track of what page we're downloading
Page=1
 
# This mess below is just to wrap lines
URL0="https://centralservices.com/corporate/companyaccounts"
URL1="?embeds=databaseserver&fields=EntityID,DatabaseServerID,DatabaseServer.DatabaseServerID,"
URL2="DatabaseServer.DatabaseServerName&pageSize=200&pageNumber="
Site="${URL0}${URL1}${URL2}"
 
JQuery='.[] | [.EntityID, .DatabaseServer.DatabaseServerName] | @csv'
 
# Make two temorary files to store some data
NewFile=$(mktemp)
TmpFile=$(mktemp)
 
# This is were the output needs to end up at
OldFile="/etc/graylog/xdbs_companycode_new.csv"
 
# Keep track of how many lines are added to ${File}
OldCount=0
LineCount=1
CountChange="$((LineCount - OldCount))"
 
while [ ! "${CountChange}" == "0" ] ; do
        curl -s "${Site}${Page}" | jq -r "${JQuery}" >> ${NewFile}
        ((Page++))
        temp=$(wc --lines ${NewFile})
        LineCount=${temp%% *}
        CountChange="$((LineCount - OldCount))"
        OldCount=${LineCount}
        done
 
# Sorting because the uniq command below prefers it
sort ${NewFile} > ${TmpFile}
 
# Add the header
echo '"companycode","dbserver"' > ${NewFile}
 
# Get rid of duplicate entries
uniq ${TmpFile} >> ${NewFile}
 
# Put the file where it's needed
mv ${NewFile} ${OldFile}
 
# Delete the remaining temp file
rm -f ${TmpFile}