(cont.) | eval c0_key = json_keys(c0)
| foreach c0_key mode=json_array
[eval c0_job = mvappend(c0_job, json_object("key", <<ITEM>>, "job", json_extract(c0, <<ITEM>>)))]
| mvexpand c0_job
| fields c0_job You now get c0_job {"key":0,"job":{"jobname":"A001_GVE_ADHOC_AUDIT","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":1,"job":{"jobname":"BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":2,"job":{"jobname":"BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS_WEEKLY","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":3,"job":{"jobname":"D001_GVE_SOFT_MATCHING_GDH_CA","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":4,"job":{"jobname":"D100_AKS_CDWH_SQOOP_TRX_ORG","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":5,"job":{"jobname":"D100_AKS_CDWH_SQOOP_TYP_123","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":6,"job":{"jobname":"D100_AKS_CDWH_SQOOP_TYP_45","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":7,"job":{"jobname":"D100_AKS_CDWH_SQOOP_TYP_ENPW","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":8,"job":{"jobname":"D100_AKS_CDWH_SQOOP_TYP_T","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":9,"job":{"jobname":"DREAMPC_CALC_ML_NAMESAPCE","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":10,"job":{"jobname":"DREAMPC_MEMORY_AlERT_SIT","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":11,"job":{"jobname":"DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":12,"job":{"jobname":"DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS_WEEKLY","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":13,"job":{"jobname":"DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":14,"job":{"jobname":"DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS_WEEKLY","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":15,"job":{"jobname":"DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":16,"job":{"jobname":"DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS_WEEKLY","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":17,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":18,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH_WEEKLY","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":19,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_SAMCONTDEPOT","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":20,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TLXLSP_TRXN","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":21,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":22,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR_WEEKLY","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":23,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":24,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON_WEEKLY","status":"ENDED OK","Timestamp":"20240317 13:25:23"}} {"key":25,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} {"key":26,"job":{"jobname":"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI_WEEKLY","status":"ENDED NOTOK","Timestamp":"20240317 13:25:23"}} (I could have placed "jobname", etc., directly into root with more SPL magic but it is not worth it.) Then, you just extract all data using standard spath. Put everything together, | rex mode=sed "s/^([^_]+)_/\1row_/"
| rex "^[^:]+\s*:\s*(?<json_frame>.+)"
```| eval good = if(json_valid(json_frame), "yes", "no")```
| spath input=json_frame path=row_c0
| eval row_key = json_keys(row_c0)
```| eval r_c0 = json_extract(row_c0, "0") . json_extract(row_c0, "1")```
| eval c0 = ""
| foreach row_key mode=json_array
[eval c0 = c0 . json_extract(row_c0, <<ITEM>>)]
| fields - _* json_frame row_*
| rex field=c0 mode=sed "s/} *\"/}, \"/g s/\" *\"/\", \"/g s/$/}/"
```| eval good = if(json_valid(c0), "yes", "no")```
| eval c0_key = json_keys(c0)
| foreach c0_key mode=json_array
[eval c0_job = mvappend(c0_job, json_object("key", <<ITEM>>, "job", json_extract(c0, <<ITEM>>)))]
| mvexpand c0_job
| spath input=c0_job
| fields - c0* You then get job.Timestamp job.jobname job.status key 20240317 13:25:23 A001_GVE_ADHOC_AUDIT ENDED NOTOK 0 20240317 13:25:23 BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS ENDED NOTOK 1 20240317 13:25:23 BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS_WEEKLY ENDED NOTOK 2 20240317 13:25:23 D001_GVE_SOFT_MATCHING_GDH_CA ENDED NOTOK 3 20240317 13:25:23 D100_AKS_CDWH_SQOOP_TRX_ORG ENDED NOTOK 4 20240317 13:25:23 D100_AKS_CDWH_SQOOP_TYP_123 ENDED NOTOK 5 20240317 13:25:23 D100_AKS_CDWH_SQOOP_TYP_45 ENDED OK 6 20240317 13:25:23 D100_AKS_CDWH_SQOOP_TYP_ENPW ENDED NOTOK 7 20240317 13:25:23 D100_AKS_CDWH_SQOOP_TYP_T ENDED NOTOK 8 20240317 13:25:23 DREAMPC_CALC_ML_NAMESAPCE ENDED NOTOK 9 20240317 13:25:23 DREAMPC_MEMORY_AlERT_SIT ENDED NOTOK 10 20240317 13:25:23 DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS ENDED NOTOK 11 20240317 13:25:23 DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS_WEEKLY ENDED NOTOK 12 20240317 13:25:23 DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS ENDED OK 13 20240317 13:25:23 DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS_WEEKLY ENDED OK 14 20240317 13:25:23 DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS ENDED OK 15 20240317 13:25:23 DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS_WEEKLY ENDED OK 16 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH ENDED OK 17 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH_WEEKLY ENDED OK 18 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_SAMCONTDEPOT ENDED NOTOK 19 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TLXLSP_TRXN ENDED NOTOK 20 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR ENDED OK 21 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR_WEEKLY ENDED OK 22 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON ENDED NOTOK 23 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON_WEEKLY ENDED OK 24 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI ENDED NOTOK 25 20240317 13:25:23 DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI_WEEKLY ENDED NOTOK 26 Only this way, I can be confident that this is what the app/equipment/device is trying to tell me. Here is a data emulation you can play with and compare with real data | makeresults
| eval _raw = "Dataframe row : {\"_c0\":{\"0\":\"{\",\"1\":\" \\\"0\\\": {\",\"2\":\" \\\"jobname\\\": \\\"A001_GVE_ADHOC_AUDIT\\\"\",\"3\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"4\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"5\":\" }\",\"6\":\" \\\"1\\\": {\",\"7\":\" \\\"jobname\\\": \\\"BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS\\\"\",\"8\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"9\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"10\":\" }\",\"11\":\" \\\"2\\\": {\",\"12\":\" \\\"jobname\\\": \\\"BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS_WEEKLY\\\"\",\"13\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"14\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"15\":\" }\",\"16\":\" \\\"3\\\": {\",\"17\":\" \\\"jobname\\\": \\\"D001_GVE_SOFT_MATCHING_GDH_CA\\\"\",\"18\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"19\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"20\":\" }\",\"21\":\" \\\"4\\\": {\",\"22\":\" \\\"jobname\\\": \\\"D100_AKS_CDWH_SQOOP_TRX_ORG\\\"\",\"23\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"24\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"25\":\" }\",\"26\":\" \\\"5\\\": {\",\"27\":\" \\\"jobname\\\": \\\"D100_AKS_CDWH_SQOOP_TYP_123\\\"\",\"28\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"29\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"30\":\" }\",\"31\":\" \\\"6\\\": {\",\"32\":\" \\\"jobname\\\": \\\"D100_AKS_CDWH_SQOOP_TYP_45\\\"\",\"33\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"34\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"35\":\" }\",\"36\":\" \\\"7\\\": {\",\"37\":\" \\\"jobname\\\": \\\"D100_AKS_CDWH_SQOOP_TYP_ENPW\\\"\",\"38\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"39\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"40\":\" }\",\"41\":\" \\\"8\\\": {\",\"42\":\" \\\"jobname\\\": \\\"D100_AKS_CDWH_SQOOP_TYP_T\\\"\",\"43\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"44\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"45\":\" }\",\"46\":\" \\\"9\\\": {\",\"47\":\" \\\"jobname\\\": \\\"DREAMPC_CALC_ML_NAMESAPCE\\\"\",\"48\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"49\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"50\":\" }\",\"51\":\" \\\"10\\\": {\",\"52\":\" \\\"jobname\\\": \\\"DREAMPC_MEMORY_AlERT_SIT\\\"\",\"53\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"54\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"55\":\" }\",\"56\":\" \\\"11\\\": {\",\"57\":\" \\\"jobname\\\": \\\"DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS\\\"\",\"58\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"59\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"60\":\" }\",\"61\":\" \\\"12\\\": {\",\"62\":\" \\\"jobname\\\": \\\"DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS_WEEKLY\\\"\",\"63\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"64\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"65\":\" }\",\"66\":\" \\\"13\\\": {\",\"67\":\" \\\"jobname\\\": \\\"DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS\\\"\",\"68\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"69\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"70\":\" }\",\"71\":\" \\\"14\\\": {\",\"72\":\" \\\"jobname\\\": \\\"DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS_WEEKLY\\\"\",\"73\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"74\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"75\":\" }\",\"76\":\" \\\"15\\\": {\",\"77\":\" \\\"jobname\\\": \\\"DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS\\\"\",\"78\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"79\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"80\":\" }\",\"81\":\" \\\"16\\\": {\",\"82\":\" \\\"jobname\\\": \\\"DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS_WEEKLY\\\"\",\"83\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"84\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"85\":\" }\",\"86\":\" \\\"17\\\": {\",\"87\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH\\\"\",\"88\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"89\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"90\":\" }\",\"91\":\" \\\"18\\\": {\",\"92\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH_WEEKLY\\\"\",\"93\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"94\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"95\":\" }\",\"96\":\" \\\"19\\\": {\",\"97\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_SAMCONTDEPOT\\\"\",\"98\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"99\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"100\":\" }\",\"101\":\" \\\"20\\\": {\",\"102\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TLXLSP_TRXN\\\"\",\"103\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"104\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"105\":\" }\",\"106\":\" \\\"21\\\": {\",\"107\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR\\\"\",\"108\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"109\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"110\":\" }\",\"111\":\" \\\"22\\\": {\",\"112\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR_WEEKLY\\\"\",\"113\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"114\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"115\":\" }\",\"116\":\" \\\"23\\\": {\",\"117\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON\\\"\",\"118\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"119\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"120\":\" }\",\"121\":\" \\\"24\\\": {\",\"122\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON_WEEKLY\\\"\",\"123\":\" \\\"status\\\": \\\"ENDED OK\\\"\",\"124\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"125\":\" }\",\"126\":\" \\\"25\\\": {\",\"127\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI\\\"\",\"128\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"129\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"130\":\" }\",\"131\":\" \\\"26\\\": {\",\"132\":\" \\\"jobname\\\": \\\"DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI_WEEKLY\\\"\",\"133\":\" \\\"status\\\": \\\"ENDED NOTOK\\\"\",\"134\":\" \\\"Timestamp\\\": \\\"20240317 13:25:23\\\"\",\"135\":\" }\" } }"
``` data emulation above ``` As noted above, I artificially inserted two closing curly brackets into _raw. If the app/equipment/device willfully drops them, you can insert them back with something simple as | eval _raw = _raw . "}}" Hope this helps.
... View more