文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >从jq到csv的输出结果

问从jq到csv的输出结果
EN

Stack Overflow用户
提问于 2021-12-22 18:08:32
回答 1查看 109关注 0票数 0

我有以下数据集，希望导出到CSV中：

数据集：

代码语言：javascript
复制
{
  "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
    }
  }
}

和头文件如下：

代码语言：javascript
复制
"Finding Id","Issue Description","Risk if no Action Taken","Severity","Recommendations","Source Name","Object Name","Object Type","Host Version","Build","First Observed","Reference"

头键映射如下：

  • 查找Id = findingId
  • 问题描述= findingDescription
  • 不采取行动的风险= findingImpact
  • 严重=严重
  • 建议=建议
  • 源名称= sourceName
  • 对象名称= objectName
  • 对象类型= objectType
  • 主机版本=版本
  • Build = buildNumber
  • 第一次观测= firstObserved
  • Reference =kbLinkURL

不幸的是，我们必须为每个发现和产品执行一个API调用(例如:我们不能一次提取所有产品的所有发现-- API不允许我们执行这样的查询，因此，我们必须进行几次调用才能获得与其关联的对象的所有发现)。

尽管如此，将数据导出到csv的首选方法是什么？使用jq的@CSV是否有效，尽管我们必须遍历几个节点？

如有任何帮助/指导，将不胜感激。

谢谢!

注1：切普纳请求的数据集的剥离版本

代码语言：javascript
复制
{
  "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
    }
  }
}

最后得到的CSV文件：

代码语言：javascript
复制
"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"
json
csv
jq
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-12-22 19:07:12

我会将jq与间谍结合起来，下面是如何：

代码语言：javascript
复制
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

我使用jq提取我们需要的JSON部分，同时将输出压缩到JSON行(spyql需要)。然后，spyql负责其余部分，即连接数组(表达式是python，具有一些可选语法)，重命名列并生成CSV。

如果您想将firstObserved时间戳转换为datetime，您可以这样做(假设UTC时间戳)：

代码语言：javascript
复制
$ 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, datetime.utcfromtimestamp(json->affectedObjects->firstObserved/1000) 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,2021-08-24 11:59:11.877000,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,2021-08-24 11:59:11.877000,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,2021-08-24 11:59:11.877000,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,2021-08-24 11:59:11.877000,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,2021-08-24 11:59:11.877000,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,2021-08-24 11:59:11.877000,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,2021-08-24 11:59:11.877000,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,2021-11-03 19:40:48.112000,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,2021-11-03 19:40:48.112000,https://kb.vmware.com/s/article/80703

如果在日期时间不需要毫秒精度，则可以使用整数除法(即datetime.utcfromtimestamp(json->affectedObjects->firstObserved//1000))。

免责声明:我是spyql的作者

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/70453387

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档