我有以下資料集,我想將其匯出為 CSV:
資料集:
{
"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
}
}
}
標題如下:
"Finding Id","Issue Description","Risk if no Action Taken","Severity","Recommendations","Source Name","Object Name","Object Type","Host Version","Build","First Observed","Reference"
標題鍵映射如下:
- 查找 ID = 查找 ID
- 問題描述 = 發現描述
- 不采取行動的風險 = 發現影響
- 嚴重性 = 嚴重性
- 推薦 = 推薦
- 源名稱 = 源名稱
- 物件名稱 = 物件名稱
- 物件型別 = 物件型別
- 主機版本 = 版本
- 構建 = 構建編號
- 第一次觀察 = firstObserved
- 參考 = kbLinkURLs
不幸的是,我們必須對每個發現和產品執行 API 呼叫(例如:我們無法一次提取所有產品的所有發現 - API 不允許我們執行此類查詢,因此,我們必須使多次呼叫以獲取所有結果及其關聯物件。)
話雖如此,將資料匯出到 csv 的首選方法是什么?盡管我們必須遍歷多個節點,但使用 jq 的 @CSV 會起作用嗎?
任何幫助/指導將不勝感激。
謝謝!
注 1: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
}
}
}
生成的 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);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"
uj5u.com熱心網友回復:
您可以在通過使用洗掉產生的空值后的最后一部分應用@csv,以防止生成連續的冗余逗號,例如delpaths([path(.[] | select(.==null))])
jq -r '.data.activeFindings.findings[]
| [.findingId , .findingDescription, .findingImpact, .severity, (.recommendations | join(",")) , .affectedObjects[].sourceName, .affectedObjects[].objectName, .affectedObjects[].objectType, .affectedObjects[].version, .affectedObjects[].buildNumber, .firstObserved, (.kbLinkURLs | join(",")) ]
| delpaths([path(.[] | select(.==null))])
| @csv'
Demo
uj5u.com熱心網友回復:
我會將 jq 與spyql結合使用,方法如下:
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。
免責宣告:我是 spyql 的作者
轉載請註明出處,本文鏈接:https://www.uj5u.com/gongcheng/391107.html
