(The steps are a bit long so this post is split into two) Part 1. Even if you accidentally extract the data you wanted, your code will not be robust. Instead of trying to rex the piece of info you...
See more...
(The steps are a bit long so this post is split into two) Part 1. Even if you accidentally extract the data you wanted, your code will not be robust. Instead of trying to rex the piece of info you are seeking, try to restore the underlying data structure first, i.e., try to rex and restore the compliant JSON. Is it correct that the data you illustrated is just one part in a stream of data that make up a larger frame? Is it possible to illustrate an entire frame, however many events there may be? If my speculation has any merit, I suspect that this data stream is formulated such that once you string together the _c0.1, and _c0.2, c0.100, etc., you would get a valid JSON object, or a fragment of a valid JSON for key _c0. Let's test this out step by step. Note: the data you illustrated seems to be missing two closing curly brackets (}). So I add them in. There is another problem: Splunk treats leading underscore (_) specially. For some reason even fromjson is not handling _c0 correctly. So, I also add a prefix to this key name. It doesn't change semantics; you can change back to _c0 in the end. | rex mode=sed "s/^([^_]+)_/\1row_/" ``` prefix key name with "row" ```
| rex "^[^:]+\s*:\s*(?<json_frame>.+)" ``` extract JSON format "row_c0" ```
```| eval good = if(json_valid(json_frame), "yes", "no")```
| spath input=json_frame path=row_c0
| fields - _* json_frame
| eval row_key = json_keys(row_c0)
| eval c0 = ""
| foreach row_key mode=json_array
[eval c0 = c0 . json_extract(row_c0, <<ITEM>>)] Using the modified sample data (see below), I get c0 row_c0 { "0": { "jobname": "A001_GVE_ADHOC_AUDIT" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "1": { "jobname": "BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "2": { "jobname": "BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS_WEEKLY" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "3": { "jobname": "D001_GVE_SOFT_MATCHING_GDH_CA" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "4": { "jobname": "D100_AKS_CDWH_SQOOP_TRX_ORG" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "5": { "jobname": "D100_AKS_CDWH_SQOOP_TYP_123" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "6": { "jobname": "D100_AKS_CDWH_SQOOP_TYP_45" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "7": { "jobname": "D100_AKS_CDWH_SQOOP_TYP_ENPW" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "8": { "jobname": "D100_AKS_CDWH_SQOOP_TYP_T" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "9": { "jobname": "DREAMPC_CALC_ML_NAMESAPCE" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "10": { "jobname": "DREAMPC_MEMORY_AlERT_SIT" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "11": { "jobname": "DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "12": { "jobname": "DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS_WEEKLY" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "13": { "jobname": "DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "14": { "jobname": "DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS_WEEKLY" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "15": { "jobname": "DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "16": { "jobname": "DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS_WEEKLY" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "17": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "18": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH_WEEKLY" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "19": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_SAMCONTDEPOT" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "20": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TLXLSP_TRXN" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "21": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "22": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR_WEEKLY" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "23": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "24": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON_WEEKLY" "status": "ENDED OK" "Timestamp": "20240317 13:25:23" } "25": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } "26": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI_WEEKLY" "status": "ENDED NOTOK" "Timestamp": "20240317 13:25:23" } {"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":" }" } So, my hypothesis is only partially correct. Obviously c0 resembles a JSON object but without proper comma separation; it also doesn't have the closing curly bracket. The intention of c0 appears to be an order list (as opposed to array). So, I will rectify the format to fulfill my interpretation. | rex field=c0 mode=sed "s/} *\"/}, \"/g s/\" *\"/\", \"/g s/$/}/"
```| eval good = if(json_valid(c0), "yes", "no")``` You now get the real c0: c0 { "0": { "jobname": "A001_GVE_ADHOC_AUDIT", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "1": { "jobname": "BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "2": { "jobname": "BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TSYS_WEEKLY", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "3": { "jobname": "D001_GVE_SOFT_MATCHING_GDH_CA", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "4": { "jobname": "D100_AKS_CDWH_SQOOP_TRX_ORG", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "5": { "jobname": "D100_AKS_CDWH_SQOOP_TYP_123", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "6": { "jobname": "D100_AKS_CDWH_SQOOP_TYP_45", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "7": { "jobname": "D100_AKS_CDWH_SQOOP_TYP_ENPW", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "8": { "jobname": "D100_AKS_CDWH_SQOOP_TYP_T", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "9": { "jobname": "DREAMPC_CALC_ML_NAMESAPCE", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "10": { "jobname": "DREAMPC_MEMORY_AlERT_SIT", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "11": { "jobname": "DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "12": { "jobname": "DREAM_BDV_NBR_PRE_REQUISITE_TLX_LSP_3RD_PARTY_TRNS_WEEKLY", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "13": { "jobname": "DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "14": { "jobname": "DREAM_BDV_NBR_STG_TLX_LSP_3RD_PARTY_TRNS_WEEKLY", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "15": { "jobname": "DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "16": { "jobname": "DREAM_BDV_NBR_TLX_LSP_3RD_PARTY_TRNS_WEEKLY", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "17": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "18": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_GDH_WEEKLY", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "19": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_SAMCONTDEPOT", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "20": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TLXLSP_TRXN", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "21": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "22": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADEABR_WEEKLY", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "23": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "24": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_TRADESON_WEEKLY", "status": "ENDED OK", "Timestamp": "20240317 13:25:23" }, "25": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }, "26": { "jobname": "DREAM_BDV_NEW_BUSINESS_REPORTING_PRE_REQUISITE_ZCI_WEEKLY", "status": "ENDED NOTOK", "Timestamp": "20240317 13:25:23" }} From here, I will assume that the order of this list has some semantics and apply the same tricks. (You really need to talk to developers or read the manual of this application/equipment/device that send these data frames.) (to continue)