Bash - Parse JSON for use with InfluxDB

Question

I've done some simpler scripts successfully to parse data from json to import into InfluxDB (and to view in Grafana), but this one is much trickier than what I'm used to. Cisco UCCX/Finesse's VoiceCSQDetailsStats API looks like the below json. What I want to do is to have a loop going to parse the below json, and poll the individual user data (agentId, agentName, agentState, and agentState Duration based on each id) to InfluxDB using the bottom curls. How would I go about accomplishing this with bash?

Save the data below as the following variables:

jsonAgentId="id"
jsonAgentName="agentName"
jsonAgentState="agentState"
jsonAgentStateDuration="agentStateDuration"

VoiceCSQDetailsStats json:

{
  "id": "muser",
  "operation": "UPDATE",
  "VoiceCSQDetailsStats": {
    "agentId": "muser",
    "agentName": "My User",
    "agentState": "Not Ready",
    "skillGroup": "",
    "agentStateDuration": 982761,
    "reason": "Break",
    "AgentVoiceCSQNames": [
      {
        "agentVoiceCSQName": "Dispatcher"
      },
      {
        "agentVoiceCSQName": "NOCEscalation"
      },
      {
        "agentVoiceCSQName": "NOCHelpdesk"
      }
    ]
  }
}
{
  "id": "yuser",
  "operation": "UPDATE",
  "VoiceCSQDetailsStats": {
    "agentId": "yuser",
    "agentName": "Your User",
    "agentState": "Talking",
    "skillGroup": "",
    "agentStateDuration": 626160,
    "reason": "",
    "AgentVoiceCSQNames": [
      {
        "agentVoiceCSQName": "Dispatcher"
      },
      {
        "agentVoiceCSQName": "NOCHelpdesk"
      }
    ]
  }
}
{
  "id": "euser",
  "operation": "UPDATE",
  "VoiceCSQDetailsStats": {
    "agentId": "euser",
    "agentName": "Everyones User",
    "agentState": "Ready",
    "skillGroup": "",
    "agentStateDuration": 203631,
    "reason": "",
    "AgentVoiceCSQNames": [
      {
        "agentVoiceCSQName": "NOCHelpdesk"
      },
      {
        "agentVoiceCSQName": "NOCEscalation"
      }
    ]
  }
}
{
  "id": "duser",
  "operation": "UPDATE",
  "VoiceCSQDetailsStats": {
    "agentId": "duser",
    "agentName": "Dumb User",
    "agentState": "Not Ready",
    "skillGroup": "",
    "agentStateDuration": 175342,
    "reason": "Call Not Answered",
    "AgentVoiceCSQNames": [
      {
        "agentVoiceCSQName": "NOCEscalation"
      },
      {
        "agentVoiceCSQName": "NOCHelpdesk"
      }
    ]
  }
}
{
  "id": "fuser",
  "operation": "UPDATE",
  "VoiceCSQDetailsStats": {
    "agentId": "fuser",
    "agentName": "Foolish User",
    "agentState": "Not Ready",
    "skillGroup": "",
    "agentStateDuration": 1057520,
    "reason": "Offhook",
    "AgentVoiceCSQNames": [
      {
        "agentVoiceCSQName": "NOCEscalation"
      },
      {
        "agentVoiceCSQName": "NOCHelpdesk"
      }
    ]
  }
}
{
  "id": "druser",
  "operation": "UPDATE",
  "VoiceCSQDetailsStats": {
    "agentId": "druser",
    "agentName": "Drug User",
    "agentState": "Talking ( from CSQ: NOCHelpdesk )",
    "skillGroup": "NOCHelpdesk",
    "agentStateDuration": 167914,
    "reason": "Offhook",
    "AgentVoiceCSQNames": [
      {
        "agentVoiceCSQName": "NOCEscalation"
      },
      {
        "agentVoiceCSQName": "NOCHelpdesk"
      }
    ]
  }
}

Send to InfluxDB:

curl -i -XPOST "$influxdbIP:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=$jsonAgentId value=$jsonAgentName"
curl -i -XPOST "$influxdbIP:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=$jsonAgentId value=$jsonAgentState"
curl -i -XPOST "$influxdbIP:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=$jsonAgentId value=$jsonAgentStateDuration"

Using jq, I had considered doing the below, but I'll still need for it to loop through the users and stop once jq -c '.[n]' returns null.

curl 'http://10.10.66.16:9080/realtime/VoiceCSQDetailsStats' | jq -c '.[6]' | grep -oP '(?<="id":")[^."]*'
druser

curl 'http://10.10.66.16:9080/realtime/VoiceCSQDetailsStats' | jq -c '.[6]' | grep -oP '(?<="agentName":")[^."]*'
Drug User

curl 'http://10.10.66.16:9080/realtime/VoiceCSQDetailsStats' | jq -c '.[6]' | grep -oP '(?<="agentState":")[^."]*'
Ready

curl 'http://10.10.66.16:9080/realtime/VoiceCSQDetailsStats' | jq -c '.[6]' | grep -oP '(?<="agentStateDuration":)[^.,]*'
167914

curl 'http://10.10.66.16:9080/realtime/VoiceCSQDetailsStats' | jq -c '.[6]' | grep -oP '(?<="reason":")[^."]*'
Offhook

Don't do it in bash. Use an application that is specifically designed to parse JSON. [jq](https://stedolan.github.io/jq/) would be a good choice here. — JNevill, Aug 07 '18 at 17:55
Jq is what I was thinking, and have updated my initial post. However, I'll still need for it to loop and send to influxdb, so I'll need bash, python, or whatever else in order to do so. — eptesicus, Aug 07 '18 at 18:34
Just have [JQ flatten it into a file](https://stackoverflow.com/questions/39139107/how-to-format-a-json-string-as-a-table-using-jq) and then loop the file to run your curl. Looping through lines in a file is a very normal happy-path thing to do in bash. This `jq` and `bash` can both be used to do what they are good at without crossing paths. Basically the real problem statement here is "How do I flatten this json into flat file/delimited format on the command line so I can read it in a bash loop" and the answer is `jq`. — JNevill, Aug 07 '18 at 18:39

score 0 · Answer 1 · answered Sep 25 '18 at 08:11

You could extract the wanted field using jq and assign values to bash variables:

<file jq -r '.VoiceCSQDetailsStats | [ .agentId,.agentName,.agentState,.agentStateDuration ] | @tsv' \
| while IFS=$'\t' read jsonAgentId jsonAgentName jsonAgentState jsonAgentStateDuration; do
    echo -e "jsonAgentId=${jsonAgentId}\njsonAgentName=${jsonAgentName}\njsonAgentState=${jsonAgentState}\njsonAgentStateDuration=${jsonAgentStateDuration}\n";
done

The @tsv operator allows to get all values separated with a tab \t, then it can be easily parsed by the shell by setting the input field separator IFS accordingly.

You might want to replace the <file by curl ... | if you want to directly parse the JSON data.

score 0 · Answer 2 · answered Nov 07 '19 at 01:19

My solution is to have jq write the curl commands for you:

Starting Simple: Just get an object with the data you want

$ jq -c '.VoiceCSQDetailsStats | { id: .agentId, name: .agentName, state: .agentState, stateDuration: .agentStateDuration } | select(.id and .name and .state and .stateDuration)' < eptesicus.json
{"id":"muser","name":"My User","state":"Not Ready","stateDuration":982761}
{"id":"yuser","name":"Your User","state":"Talking","stateDuration":626160}
{"id":"euser","name":"Everyones User","state":"Ready","stateDuration":203631}
{"id":"duser","name":"Dumb User","state":"Not Ready","stateDuration":175342}
{"id":"fuser","name":"Foolish User","state":"Not Ready","stateDuration":1057520}
{"id":"druser","name":"Drug User","state":"Talking ( from CSQ: NOCHelpdesk )","stateDuration":167914}

Expanding on that: Have `jq` write your `curl` statements

$ influxdbIP=1.2.3.4 jq -r '.VoiceCSQDetailsStats | { id: .agentId, name: .agentName, state: .agentState, stateDuration: .agentStateDuration } | select(.id and .name and .state and .stateDuration) | .id as $id | ( .name, .state, .stateDuration) | "curl -i -XPOST \"\( $ENV | .influxdbIP):8086/write?db=nocdb\" --data-binary \"uccxstats,host=uccx,user=\($id) value=\(.)\""' < eptesicus.json
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=muser value=My User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=muser value=Not Ready"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=muser value=982761"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=yuser value=Your User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=yuser value=Talking"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=yuser value=626160"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=euser value=Everyones User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=euser value=Ready"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=euser value=203631"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=duser value=Dumb User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=duser value=Not Ready"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=duser value=175342"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=fuser value=Foolish User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=fuser value=Not Ready"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=fuser value=1057520"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=druser value=Drug User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=druser value=Talking ( from CSQ: NOCHelpdesk )"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=druser value=167914"

Finishing up

That select(…) will generate no curl requests if any of those fields are missing or null.

If you want to, you can expand that like this:

$ influxdbIP=1.2.3.4 jq -r '.VoiceCSQDetailsStats | { id: .agentId, name: .agentName, state: .agentState, stateDuration: .agentStateDuration } | select(.id and .name and .state and .stateDuration and ( .state != "Not Ready" ) ) | .id as $id | ( .name, .state, .stateDuration) | "curl -i -XPOST \"\( $ENV | .influxdbIP):8086/write?db=nocdb\" --data-binary \"uccxstats,host=uccx,user=\($id) value=\(.)\""' < eptesicus.json
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=yuser value=Your User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=yuser value=Talking"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=yuser value=626160"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=euser value=Everyones User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=euser value=Ready"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=euser value=203631"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=druser value=Drug User"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=druser value=Talking ( from CSQ: NOCHelpdesk )"
curl -i -XPOST "1.2.3.4:8086/write?db=nocdb" --data-binary "uccxstats,host=uccx,user=druser value=167914"

Note that it generates no curl requests for agents in state "Not Ready".

Bash - Parse JSON for use with InfluxDB

2 Answers2

Starting Simple: Just get an object with the data you want

Expanding on that: Have jq write your curl statements

Finishing up

Expanding on that: Have `jq` write your `curl` statements