Facing issue while extracting required fields and while using mvexpand command

[Renunaren]

Hi Team,

The below is the event which we have received into the splunk,

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":" }"

we have tried to extract the required fields such as Timestamp, Jobname, Status from the above events using the below splunk query

index=app_events_dwh2_de_int _raw=*jobname* | rex max_match=0 "\\\\\\\\\\\\\"jobname\\\\\\\\\\\\\":\s*\\\\\\\\\\\\\"(?<Name>[^\\\]+)"  | rex max_match=0 "\\\\\\\\\\\\\"status\\\\\\\\\\\\\":\s*\\\\\\\\\\\\\"(?<State>[^\\\]+)"   | rex max_match=0 "Timestamp\\\\\\\\\\\\\": \\\\\\\\\\\\\"(?<TIME>\d+\s*\d+\:\d+\:\d+)"  | rex max_match=0 "execution_time_in_seconds\\\\\\\\\\\\\": \\\\\\\\\\\\\"(?<EXECUTION_TIME>[\d\.\-]+)"

| table  "TIME", "Name", "State", "EXECUTION_TIME"  | mvexpand  TIME

But the issue we want to extract only those status jobs with status as " ENDED NOTOK". But we are unable to extract them. Also when we use mvexpand command for the table, it is showing multiple duplicate values.

We request you to kindly look into this and help us on this issue.

[ITWhisperer]

It looks like you have too many backslashes (and execution_time_in_seconds does not appear in your sample)

| rex max_match=0 "\\\\\\\"jobname\\\\\\\":\s*\\\\\\\"(?<Name>[^\\\]+)"
| rex max_match=0 "\\\\\\\"status\\\\\\\":\s*\\\\\\\"(?<State>[^\\\]+)"
| rex max_match=0 "Timestamp\\\\\\\": \\\\\\\"(?<TIME>\d+\s*\d+\:\d+\:\d+)"
| rex max_match=0 "execution_time_in_seconds\\\\\\\": \\\\\\\"(?<EXECUTION_TIME>[\d\.\-]+)"

[Renunaren]

Hello ITWhisperer,

Thank you for your response. I have extracted all the mentioned fields earlier as well. But is there any possibility in extraction, so that we can only extract the status of ENDED NOTOK jobs only.

[yuanliu]

(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)

[yuanliu]

(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.

[ITWhisperer]

| rex max_match=0 "\\\\\\\"jobname\\\\\\\":\s*\\\\\\\"(?<Name>[^\\\]+).*?\\\\\\\"status\\\\\\\":\s*\\\\\\\"(?<State>ENDED NOTOK).*?Timestamp\\\\\\\": \\\\\\\"(?<TIME>\d+\s*\d+\:\d+\:\d+)"

[Renunaren]

Hi ITWhisperer,

Thank you for your message. This has worked partially. But we are unable to get the full count of those jobs with State ENDED NOTOK. Please help me out on this.

[ITWhisperer]

You are going to have to be more expansive about what worked, what didn't work, which events didn't get counted, etc.