Home > front end >  export results from jq to csv
export results from jq to csv

Time：12-23

I'm having the following dataset which I'd like to export into a CSV:

Dataset:

{
  "data": {
    "activeFindings": {
      "findings": [
        {
          "findingId": "risk#80703",
          "accountId": "00000000-000000-0000000-000000",
          "products": [
            "GWSERVER01"
          ],
          "findingDisplayName": "risk#80703",
          "severity": "CRITICAL",
          "findingDescription": "PSOD with re-formatting a valid dedup metadata block.",
          "findingImpact": "Potential ESXi host crash",
          "recommendations": [
            "This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523)",
            "This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804)"
          ],
          "kbLinkURLs": [
            "https://kb.vmware.com/s/article/80703"
          ],
          "recommendationsVCF": [
            "This issue is resolved with VMware Cloud Foundation 4.1"
          ],
          "kbLinkURLsVCF": [
            "https://kb.vmware.com/s/article/80703"
          ],
          "categoryName": "Storage",
          "findingTypes": [
            "UPGRADE"
          ],
          "firstObserved": 1629806351877,
          "totalAffectedObjectsCount": 12,
          "affectedObjects": [
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server01.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            },
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server02.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            },
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server03.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            },
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server04.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            },
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server05.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            },
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server06.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            },
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server07.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            }
          ]
        }
      ],
      "totalRecords": 1,
      "timeTaken": 56
    }
  }
}
{
  "data": {
    "activeFindings": {
      "findings": [
        {
          "findingId": "risk#80703",
          "accountId": "00000000-000000-0000000-000000",
          "products": [
            "GWSERVER02.corp.contoso.org"
          ],
          "findingDisplayName": "risk#80703",
          "severity": "CRITICAL",
          "findingDescription": "PSOD with re-formatting a valid dedup metadata block.",
          "findingImpact": "Potential ESXi host crash",
          "recommendations": [
            "This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523)",
            "This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804)"
          ],
          "kbLinkURLs": [
            "https://kb.vmware.com/s/article/80703"
          ],
          "recommendationsVCF": [
            "This issue is resolved with VMware Cloud Foundation 4.1"
          ],
          "kbLinkURLsVCF": [
            "https://kb.vmware.com/s/article/80703"
          ],
          "categoryName": "Storage",
          "findingTypes": [
            "UPGRADE"
          ],
          "firstObserved": 1635968448112,
          "totalAffectedObjectsCount": 2,
          "affectedObjects": [
            {
              "sourceName": "GWSERVER02.corp.contoso.org",
              "objectName": "server10.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17167734",
              "solutionTags": [],
              "firstObserved": 1635968448112
            },
            {
              "sourceName": "GWSERVER02.corp.contoso.org",
              "objectName": "server11.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17167734",
              "solutionTags": [],
              "firstObserved": 1635968448112
            }
          ]
        }
      ],
      "totalRecords": 1,
      "timeTaken": 51
    }
  }
}

And header would be as follow:

"Finding Id","Issue Description","Risk if no Action Taken","Severity","Recommendations","Source Name","Object Name","Object Type","Host Version","Build","First Observed","Reference"

Header keys mapping as follow:

  • Finding Id = findingId
  • Issue Description = findingDescription
  • Risk if no Action Taken = findingImpact
  • Severity = severity
  • Recommendations = recommendations
  • Source Name = sourceName
  • Object Name = objectName
  • Object Type = objectType
  • Host Version = version
  • Build = buildNumber
  • First Observed = firstObserved
  • Reference = kbLinkURLs

Unfortunately, we have to perform an API call per each finding & product (eg: we're not able to pull all the findings for all products at once - the API does not allow us to perform such query and thus, we have to make several calls to get all the findings with its associated objects.)

With that said, what would be the preferred approach to export the data into a csv ? Would using jq's @CSV work though we would have to loop through several nodes ?

Any help/guidance would be appreciated.

Thanks!

Note 1: A stripped version of the dataset as requested by chepner

{
  "data": {
    "activeFindings": {
      "findings": [
        {
          "findingId": "risk#80703",
          "accountId": "00000000-000000-0000000-000000",
          "products": [
            "GWSERVER01"
          ],
          "findingDisplayName": "risk#80703",
          "severity": "CRITICAL",
          "findingDescription": "PSOD with re-formatting a valid dedup metadata block.",
          "findingImpact": "Potential ESXi host crash",
          "recommendations": [
            "This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523)",
            "This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804)"
          ],
          "kbLinkURLs": [
            "https://kb.vmware.com/s/article/80703"
          ],
          "recommendationsVCF": [
            "This issue is resolved with VMware Cloud Foundation 4.1"
          ],
          "kbLinkURLsVCF": [
            "https://kb.vmware.com/s/article/80703"
          ],
          "categoryName": "Storage",
          "findingTypes": [
            "UPGRADE"
          ],
          "firstObserved": 1629806351877,
          "totalAffectedObjectsCount": 12,
          "affectedObjects": [
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server01.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            },
            {
              "sourceName": "GWSERVER01.corp.contoso.org",
              "objectName": "server02.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17499825",
              "solutionTags": [],
              "firstObserved": 1629806351877
            },
          ]
        }
      ],
      "totalRecords": 1,
      "timeTaken": 56
    }
  }
}
{
  "data": {
    "activeFindings": {
      "findings": [
        {
          "findingId": "risk#80703",
          "accountId": "00000000-000000-0000000-000000",
          "products": [
            "GWSERVER02.corp.contoso.org"
          ],
          "findingDisplayName": "risk#80703",
          "severity": "CRITICAL",
          "findingDescription": "PSOD with re-formatting a valid dedup metadata block.",
          "findingImpact": "Potential ESXi host crash",
          "recommendations": [
            "This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523)",
            "This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804)"
          ],
          "kbLinkURLs": [
            "https://kb.vmware.com/s/article/80703"
          ],
          "recommendationsVCF": [
            "This issue is resolved with VMware Cloud Foundation 4.1"
          ],
          "kbLinkURLsVCF": [
            "https://kb.vmware.com/s/article/80703"
          ],
          "categoryName": "Storage",
          "findingTypes": [
            "UPGRADE"
          ],
          "firstObserved": 1635968448112,
          "totalAffectedObjectsCount": 2,
          "affectedObjects": [
            {
              "sourceName": "GWSERVER02.corp.contoso.org",
              "objectName": "server10.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17167734",
              "solutionTags": [],
              "firstObserved": 1635968448112
            },
            {
              "sourceName": "GWSERVER02.corp.contoso.org",
              "objectName": "server11.corp.contoso.org",
              "objectType": "ESX",
              "version": "6.7.0",
              "buildNumber": "17167734",
              "solutionTags": [],
              "firstObserved": 1635968448112
            }
          ]
        }
      ],
      "totalRecords": 1,
      "timeTaken": 51
    }
  }
}

And the resulted CSV file:

"Finding Id","Issue Description","Risk if no Action Taken","Severity","Recommendations","Source Name","Object Name","Object Type","Host Version","Build","First Observed","Reference"
"risk#80703","PSOD with re-formatting a valid dedup metadata block.","Potential ESXi host crash","CRITICAL","This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804);This issue is resolved with VMware Cloud Foundation 4.1","GWSERVER01.corp.contoso.org","server01.corp.contoso.org","HostSystem","6.7.0","17499825","1629806351877","https://kb.vmware.com/s/article/80703"
"risk#80703","PSOD with re-formatting a valid dedup metadata block.","Potential ESXi host crash","CRITICAL","This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804);This issue is resolved with VMware Cloud Foundation 4.1","GWSERVER01.corp.contoso.org","server02.corp.contoso.org","HostSystem","6.7.0","17499825","1629806351877","https://kb.vmware.com/s/article/80703"
"risk#80703","PSOD with re-formatting a valid dedup metadata block.","Potential ESXi host crash","CRITICAL","This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804);This issue is resolved with VMware Cloud Foundation 4.1","GWSERVER02.corp.contoso.org","server10.corp.contoso.org","HostSystem","6.7.0","17167734","1635968448112","https://kb.vmware.com/s/article/80703"
"risk#80703","PSOD with re-formatting a valid dedup metadata block.","Potential ESXi host crash","CRITICAL","This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804);This issue is resolved with VMware Cloud Foundation 4.1","GWSERVER02.corp.contoso.org","server11.corp.contoso.org","HostSystem","6.7.0","17167734","1635968448112","https://kb.vmware.com/s/article/80703"

CodePudding user response：

I would combine jq with spyql, here's how:

jq -c '.data.activeFindings.findings[]' full_sample.json | spyql "SELECT json->findingId AS 'Finding Id', json->findingDescription  AS 'Issue Description', json->findingImpact AS 'Risk if no Action Taken', json->severity AS Severity, ';'.join(json->recommendations) AS Recommendations, json->affectedObjects->sourceName AS 'Source Name', json->affectedObjects->objectName AS 'Object Name', json->affectedObjects->objectType AS 'Object Type', json->affectedObjects->version AS 'Host Version', json->affectedObjects->buildNumber AS Build, json->affectedObjects->firstObserved AS 'First Observed', ';'.join(json->kbLinkURLsVCF) AS Reference FROM json EXPLODE json->affectedObjects TO csv"      
Finding Id,Issue Description,Risk if no Action Taken,Severity,Recommendations,Source Name,Object Name,Object Type,Host Version,Build,First Observed,Reference
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER01.corp.contoso.org,server01.corp.contoso.org,ESX,6.7.0,17499825,1629806351877,https://kb.vmware.com/s/article/80703
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER01.corp.contoso.org,server02.corp.contoso.org,ESX,6.7.0,17499825,1629806351877,https://kb.vmware.com/s/article/80703
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER01.corp.contoso.org,server03.corp.contoso.org,ESX,6.7.0,17499825,1629806351877,https://kb.vmware.com/s/article/80703
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER01.corp.contoso.org,server04.corp.contoso.org,ESX,6.7.0,17499825,1629806351877,https://kb.vmware.com/s/article/80703
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER01.corp.contoso.org,server05.corp.contoso.org,ESX,6.7.0,17499825,1629806351877,https://kb.vmware.com/s/article/80703
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER01.corp.contoso.org,server06.corp.contoso.org,ESX,6.7.0,17499825,1629806351877,https://kb.vmware.com/s/article/80703
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER01.corp.contoso.org,server07.corp.contoso.org,ESX,6.7.0,17499825,1629806351877,https://kb.vmware.com/s/article/80703
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER02.corp.contoso.org,server10.corp.contoso.org,ESX,6.7.0,17167734,1635968448112,https://kb.vmware.com/s/article/80703
risk#80703,PSOD with re-formatting a valid dedup metadata block.,Potential ESXi host crash,CRITICAL,This issue is resolved in VMware ESXi 6.7 upgrade to Patch 05 (17700523);This issue is resolved in VMware ESXi 7.0 upgrade to Update 1 (16850804),GWSERVER02.corp.contoso.org,server11.corp.contoso.org,ESX,6.7.0,17167734,1635968448112,https://kb.vmware.com/s/article/80703

I am using jq to extract the part of the JSON we need, while compressing the output to JSON lines (required by spyql). Then, spyql takes care of the rest, namely joining arrays (expressions are python with some optional syntax sugar), renaming columns and generating the CSV.

Disclaimer: I am the author of spyql

CodePudding user response：

You can apply @csv at the last part after removing the yielded null values through use of delpaths([path(.[] | select(.==null))]) in order to prevent generating successive redundant commas such as

jq -r '.data.activeFindings.findings[] | [.findingId , .findingDescription, .findingImpact, .severity, (.recommendations | join(",")) , .sourceName, .objectName, .objectType, .version, .buildNumber, .firstObserved, (.kbLinkURLs | join(",")) ] | delpaths([path(.[] | select(.==null))]) | @csv'

Demo

Page link：https//www.codepudding.com/frontend/238151.html

Prev:How to separate a data frame based on a column's range of values with pandas?
Next:Typescript: undefined calculated property after deserializing in JSON
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding