0

I have the below JSON file:

{
"result": [
{
  "hostid": "12607",
  "name": "love",
  "host": "loveyou",
  "status": "0",
  "groups": [
    {
      "groupid": "47",
      "name": "Flower"
    },
    {
      "groupid": "145",
      "name": "Sun"
    }
  ],
  "triggers": [
    {
      "triggerid": "211498",
      "description": "The host is unavailable by ICMP",
      "status": "0"
    },
    {
      "triggerid": "211499",
      "description": "CPU load above {$CPU}% ({ITEM.LASTVALUE})",
      "status": "0"
    },
    {
      "triggerid": "211500",
      "description": "The host has just been restarted (SysUptime {ITEM.LASTVALUE})",
      "status": "0"
    },
    {
      "triggerid": "211501",
      "description": "Sem resposta SNMP. Community {$SNMP_COMMUNITY}",
      "status": "0"
    },
    {
      "triggerid": "211574",
      "description": "Memory Usage is over {$MEM}% ({ITEM.LASTVALUE})",
      "status": "0"
    },
    {
      "triggerid": "211575",
      "description": "Free disk space is less than {$DISK}% on volume C:\\\\ Label:  Serial Number 1ab4e15c ({ITEM.LASTVALUE})",
      "status": "0"
    },
    {
      "triggerid": "211576",
      "description": "Free disk space is less than {$DISK}% on volume E:\\\\ Label:NETAPP_LUN_01  Serial Number 84048285 ({ITEM.LASTVALUE})",
      "status": "0"
    },
    {
      "triggerid": "211577",
      "description": "Free disk space is less than {$DISK}% on volume F:\\\\ Label:NETAPP_LUN_02  Serial Number 6426fd9 ({ITEM.LASTVALUE})",
      "status": "0"
    },
    {
      "triggerid": "211578",
      "description": "Free disk space is less than {$DISK}% on volume G:\\\\ Label:NETAPP_LUN_03  Serial Number 184b60f9 ({ITEM.LASTVALUE})",
      "status": "0"
    },
    {
      "triggerid": "211579",
      "description": "Free disk space is less than {$DISK}% on volume H:\\\\ Label:NETAPP_LUN_04  Serial Number 88541457 ({ITEM.LASTVALUE})",
      "status": "0"
    },
    {
      "triggerid": "211580",
      "description": "Free disk space is less than {$DISK}% on volume I:\\\\ Label:NETAPP_LUN_07  Serial Number f23669bc ({ITEM.LASTVALUE})",
      "status": "0"
    }
  ],
  "interfaces": [
    {
      "interfaceid": "2394",
      "ip": "192.168.1.190"
    },
    {
      "interfaceid": "2399",
      "ip": "192.168.1.190"
    }
  ]
}

I would like to have a CSV file in this format:

NAME, GROUP, IP,           DESCRIPTION
love, Sun, 192.168.1.190, The host is unavailable by ICMP
love, Sun, 192.168.1.190 , CPU load above {$CPU}% ({ITEM.LASTVALUE})
love, Sun, 192.168.1.190 , The host has just been restarted

Is this possible by using only jq?

I used together with jq the sed command:

CLIENTE=`echo "$RESULT" | jq -r .result[].groups[].name | sort | grep -m1 Cliente`
VISIBLENAME=`echo "$RESULT" | jq -r .result[].name | sed 's/ //g'`
HOST=`echo "$RESULT" | jq -r .result[].host | sed 's/ /_/g'`
IP=`echo "$RESULT" | jq -r .result[].interfaces[0].ip`
TRIGGERS=`echo "$RESULT" | jq -r .result[].triggers[].description | sed 's/ /_/g'`
TRIGSTATUS=`echo "$RESULT" | jq -r .result[].triggers[].status | sed 's/0/enabled/g;s/1/disabled/g'`
PRIORITY=`echo "$RESULT" | jq -r .result[].triggers[].priority | sed 's/0/notclassified/g;s/1/information/g;s/2/warning/g;s/3/average/g;s/4/high/g;s/5/disaster/g'`
ACTIVEHOST=`echo "$RESULT" | jq -r .result[].status | sed 's/0/monitored/g;s/1/unmonitored/g'`
TSTATUS=`paste <(echo "$TRIGGERS") <(echo "$TRIGSTATUS") <(echo "$PRIORITY")| sed 's/[[:space:]]/;/g'

echo "$CLIENTE;$VISIBLENAME;$HOST;$IP;$j;$ACTIVEHOST";
  • 4
    Yes it is possible. Do you have any draft of what have you attempted so far? – vfbsilva May 31 at 19:45
  • Yes jq is the correct tool. You might need to pipe it with awk as well. – Dudi Boy May 31 at 20:03
  • @vfbsilva, i used together with jq the sed command: – danielledosreissilva Jun 4 at 14:06
1

Yes, in fact it's easier to do it in only JQ. You have to build up the row that you want as an array, using JQ filters, and then use the @csv filter to output the row in CSV format (together with -r so that the CSV doesn't get further JSON-escaped).

I'm not clear on exactly what your requirements are, since your code doesn't really match your example output, and it's not obvious what to do with the repeated/nested data, but here's a partly working example:

jq -r '.result[] | . as $result 
  | .triggers[] 
  | [$result.name, $result.groups[0].name, $result.interfaces[0].ip, .description] 
  | @csv'

which, given your test data, outputs

"love","Flower","192.168.1.190","The host is unavailable by ICMP"
"love","Flower","192.168.1.190","CPU load above {$CPU}% ({ITEM.LASTVALUE})"
"love","Flower","192.168.1.190","The host has just been restarted (SysUptime {ITEM.LASTVALUE})"
"love","Flower","192.168.1.190","Sem resposta SNMP. Community {$SNMP_COMMUNITY}"
"love","Flower","192.168.1.190","Memory Usage is over {$MEM}% ({ITEM.LASTVALUE})"
"love","Flower","192.168.1.190","Free disk space is less than {$DISK}% on volume C:\\ Label:  Serial Number 1ab4e15c ({ITEM.LASTVALUE})"
"love","Flower","192.168.1.190","Free disk space is less than {$DISK}% on volume E:\\ Label:NETAPP_LUN_01  Serial Number 84048285 ({ITEM.LASTVALUE})"
"love","Flower","192.168.1.190","Free disk space is less than {$DISK}% on volume F:\\ Label:NETAPP_LUN_02  Serial Number 6426fd9 ({ITEM.LASTVALUE})"
"love","Flower","192.168.1.190","Free disk space is less than {$DISK}% on volume G:\\ Label:NETAPP_LUN_03  Serial Number 184b60f9 ({ITEM.LASTVALUE})"
"love","Flower","192.168.1.190","Free disk space is less than {$DISK}% on volume H:\\ Label:NETAPP_LUN_04  Serial Number 88541457 ({ITEM.LASTVALUE})"
"love","Flower","192.168.1.190","Free disk space is less than {$DISK}% on volume I:\\ Label:NETAPP_LUN_07  Serial Number f23669bc ({ITEM.LASTVALUE})"

The quoting is more than you asked for, but it's completely valid CSV.

  • Very niceeeeeeee!!! That's what I wanted. Thank you very much @hobbs, – danielledosreissilva Jun 4 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.