A Complemento vem se especializando na monitoração de negócios com a utilização do Zabbix, um software livre já mundialmente reconhecido para monitoramento de serviços e infraestrutura de TI.

O Software Livre Zabbix viabiliza a monitoração da atividade de negócio (BAM Business Activity Monitoring) para qualquer empresa, independente de seu porte.

Nesta prova de conceito, temos como exemplo empresas que possuem o costume de realizar controles em Planilhas do Google, desde de a gestão de projetos até suas próprias finanças.

Mostraremos então como realizar o monitoramento do valor de uma célula de uma planilha do Google com o Zabbix, simulando um cenário onde se monitora o valor em caixa de uma área ou empresa.

Para isso, vamos criar uma planilha no google drive com o seguinte conteúdo:

Agora, precisamos criar um aplicativo que controlará os acessos à API do Google. Acesse o endereço https://console.developers.google.com/ (Cadastre-se se necessário).

Crie um novo projeto

Defina um nome para seu projeto, aceite os termos e clique em Criar.

Aguarde um momento e seu projeto será criado. Se você não for redirecionado automaticamente, selecione o projeto no menu superior.

Adicionando API do Google Drive ao Projeto

A seguir, será necessário adicionar a API do Google Drive em seu projeto. Para isso, acesse APIs e autenticação --> APIs.

Pesquise por Drive e selecione "Drive API". Clique em "Ativar API".

Permissionamento

Essa, sem dúvida, é a parte mais complicada. Agora precisamos configurar os mecanismos de autenticação. Acesse APIs e autenticação --> Credenciais.

Clique em Adicionar credenciais --> Conta de Serviço

Selecione então o tipo de chave P12. Clique em Criar e faça download da chave em alguma pasta de preferência.

Repare que foi criado uma conta de e-mail com endereço @developer.gserviceaccount.com.

Agora, precisamos converter o arquivo P12 para o formato PEM.

Execute o comando abaixo em seu servidor linux:

cat nome_do_arquivo.p12 | openssl pkcs12 -nodes -nocerts -passin pass:notasecret | openssl rsa > nome_do_arquivo.pem

O arquivo ".pem" gerado, será utilizado posteriormente para nos autenticarmos na API do Google Drive.

Compartilhando a planilha criada, com a conta de Serviço

Agora, acesse novamente a planilha recém criada e clique em Compartilhar

Coloque o email da Conta de Serviço criada no projeto do Google Developer, disponibilizando apenas a opção "Pode visualizar" conforme abaixo:

Confirme o compartilhamento:

Testando a autenticação

Faça download do utilitário google_oauth2.sh do site abaixo:

Google OAuth 2.0 in Bash

Agora vamos fazer 3 ajustes neste arquivo:

1 - Local onde você armazenou o arquivo pem gerado com o comando openssl

2 - Email de serviço criado no google developer console.

3 - Escopo da autenticação. O valor que vem por padrão neste script é para autenticação na API do Youtube. Temos que modificar como na figura para obter autenticação no escopo da API do Google Drive.

Vamos testar! Execute o comando abaixo:

sh google_oauth2.sh

Se tudo der certo, o retorno será parecido com o abaixo:

access_token = ya29.7gEasFshfcP8JYI4v1YTrJ5n2YwImTcHg3FBqiH3_H_sndIPtOjgXYWTRbUiNO2eiBcQ

Com isto finalizamos a primeira parte do no sso desafio. Agora vamos obter a planilha e tratar a informação, para isso, vamos aproveitar o arquivo google_oauth2.sh

Obtendo a planilha e os valores das celulas

Vamos alterar agora o script, comentando a última linha, adicionando o # no início da mesma:

# echo "access_token = $access_token" # valid for 3600 seconds

E adicionamos o código abaixo no final do arquivo:

# Spreadsheet ID
DocID="1BURiaFbpd1U9U_Ygmc2QaocJyd624zmDSgL3CQ9mdmU"
Line=$1
Col=$2
# obtem o arquivo csv
cell=$(curl -H "Authorization: Bearer $access_token" https://docs.google.com/spreadsheets/export?id=$DocID'&'exportFormat=csv 2>/dev/null | awk "FNR == $Line" | awk -v col=$Col -v FS='"' '{print $col}')
# Realizamos algumas substituições para tratar o número como floar no Zabbix
echo $cell | sed -e "s/R$ //g" | sed -e "s/\.//g" | sed -e "s/\,/\./g"

O código do arquivo ficará assim:

#! /bin/bash

# The approx. time to run this script is 145 ms.

# First we extract the private PEM key from the .p12 file:
# openssl pkcs12 -nocerts -passin 'notasecret' -in file.p12 -out ~/google/google.privatekey.pem 
KEY='Complemento.pem'

# The fields are ordered by their hash values.
# In Google Client for Java HashMap is used to stack all JSON fields, so String.hashCode() is used for ordering.
header='{"alg":"RS256","typ":"JWT"}'

aud='https://accounts.google.com/o/oauth2/token'
exp=$(date --date='+1 hour' +%s)
iat=$(date +%s)
iss='978944929194-kihvm0c7h50g9i8guhtqaukg7mokb1m7@developer.gserviceaccount.com' # TODO: Put your Service Account's Email address
scope='https://www.googleapis.com/auth/drive'

# The fields are ordered by their hash values.
# In Google Client for Java HashMap is used to stack all JSON fields, so String.hashCode() is used for ordering.
claim="{\"aud\":\"$aud\",\"exp\":$exp,\"iat\":$iat,\"iss\":\"$iss\",\"scope\":\"$scope\"}"

#echo "exp = $exp"
#echo "iat = $iat"

header_b64=$(echo -n "$header" | base64 -w 0 | sed 's/+/-/g;s/\//_/g;s/=//g') # base64url
claim_b64=$(echo -n "$claim" | base64 -w 0 | sed 's/+/-/g;s/\//_/g;s/=//g') # base64url
signature_b64=$(echo -n "$header_b64.$claim_b64" | openssl dgst -sha256 -sign $KEY | base64 -w 0 | sed 's/+/-/g;s/\//_/g;s/=//g')

jwt=$(echo -n "$header_b64.$claim_b64.$signature_b64")
#echo $jwt

result=$(curl -s -m 60 --data-urlencode grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer --data-urlencode assertion=$jwt https://accounts.google.com/o/oauth2/token)
access_token=$(echo "$result" | grep -oP '"access_token" : "*\K([a-zA-Z0-9\.\-_])*')

# echo "access_token = $access_token" # valid for 3600 seconds

# Spreadsheet ID
DocID="1BURiaFbpd1U9U_Ygmc2QaocJyd624zmDSgL3CQ9mdmU"
Line=$1
Col=$2
# obtem o arquivo csv
cell=$(curl -H "Authorization: Bearer $access_token" https://docs.google.com/spreadsheets/export?id=$DocID'&'exportFormat=csv 2>/dev/null | awk "FNR == $Line" | awk -v col=$Col -v FS='"' '{print $col}')
# Realizamos algumas substituições para tratar o número como floar no Zabbix
echo $cell | sed -e "s/R$ //g" | sed -e "s/\.//g" | sed -e "s/\,/\./g" 

Substitua o ID da planilha na variável DocID

Note que temos que passar dois parâmetros para este script. O primeiro é o número da linha, o segundo, o número da coluna. Vamos testar agora chamando o comando da seguinte maneira:

sh google_oauth2.sh 3 2

Se tudo deu certo, você receberá o seguinte valor:

20000.00

Pronto!

Para melhorar, renomeie o script para algo como sheetReader.sh

Configurando o Agente do Zabbix

Agora é só criar um UserParameter no agente Zabbix do servidor. Edite o arquivo zabbix_agentd.conf e adicione a seguinte linha:
UserParameter=spreadsheet.contas[*],/caminho/para/sheetReader.sh $1 $2

Reinicie o agente!

Acesse o Zabbix e crie um item para seu host que monitora este user parameter, do tipo Numérico Fracionário.

Você pode criar um item por valor monitorado, por exemplo. Crie um item para monitorar o valor "Contas a Pagar" (chave spreadsheet.contas[2 2]), outro item para monitorar "Contas a Receber" (chave spreadsheet.contas[3 2]) e mais para monitorar o "Valor em Caixa" (chave spreadsheet.contas[4 2])

Possibilidades

Posteriormente você pode criar um gráfico e acompanhar a evolução de cada índice:

Também é possvel criar alertas, em situações como:

• Caixa está abaixo de X
• Caixa + Contas a Receber está menor que Contas a Pagar
• Contas a Receber está sem alteração a X dias