JSON Key/Field Extraction

jamesmcgee — Tue, 07 Sep 2021 11:35:52 GMT

I have a json formatted output, which according to jsonlint.com is valid JSON.... but, am having problems extracting out the data into relevant "fields", for indexing...

{
"code": null,
"msg": null,
"success": true,
"requestId": "XXX",
"deviceSn": "XXX",
"deviceId": "XXX",
"deviceType": "INVERTER",
"deviceState": 1,
"dataList": [{
"key": "SN1",
"value": "XXX",
"unit": null,
"name": "SN"
}, {
"key": "PM1",
"value": "F6",
"unit": null,
"name": "Product Type"
}, {
"key": "SS_CY1",
"value": "G99",
"unit": null,
"name": "Production Compliance Country"
}, {
"key": "P_CURVv1",
"value": "0",
"unit": null,
"name": "Power Curve Version"
}, {
"key": "Pr1",
"value": "4000",
"unit": "W",
"name": "Rated Power"
}, {
"key": "B_PTC1",
"value": "1",
"unit": null,
"name": "Battery Protocol"
}, {
"key": "PTCv1",
"value": "0001",
"unit": null,
"name": "Protocol Version"
}, {
"key": "HCIv1",
"value": "0033",
"unit": null,
"name": "HMI Version"
}, {
"key": "DSPv1",
"value": "0022",
"unit": null,
"name": "DSP Version"
}, {
"key": "DV1",
"value": "290.30",
"unit": "V",
"name": "DC Voltage PV1"
}, {
"key": "DV2",
"value": "301.60",
"unit": "V",
"name": "DC Voltage PV2"
}, {
"key": "DV3",
"value": "0.00",
"unit": "V",
"name": "DC Voltage PV3"
}, {
"key": "DV4",
"value": "0.00",
"unit": "V",
"name": "DC Voltage PV4"
}, {
"key": "DC1",
"value": "8.00",
"unit": "A",
"name": "DC Current PV1"
}, {
"key": "DC2",
"value": "7.60",
"unit": "A",
"name": "DC Current PV2"
}, {
"key": "DC3",
"value": "0.00",
"unit": "A",
"name": "DC Current PV3"
}, {
"key": "DC4",
"value": "0.00",
"unit": "A",
"name": "DC Current PV4"
}, {
"key": "DP1",
"value": "2322.40",
"unit": "W",
"name": "DC Power PV1"
}, {
"key": "DP2",
"value": "2292.16",
"unit": "W",
"name": "DC Power PV2"
}, {
"key": "DP3",
"value": "0",
"unit": "W",
"name": "DC Power PV3"
}, {
"key": "DP4",
"value": "0",
"unit": "W",
"name": "DC Power PV4"
}, {
"key": "AV1",
"value": "238.60",
"unit": "V",
"name": "AC Voltage R/U/A"
}, {
"key": "AV2",
"value": "0.00",
"unit": "V",
"name": "AC Voltage S/V/B"
}, {
"key": "AV3",
"value": "0.00",
"unit": "V",
"name": "AC Voltage T/W/C"
}, {
"key": "AC1",
"value": "12.10",
"unit": "A",
"name": "AC Current R/U/A"
}, {
"key": "AC2",
"value": "0.00",
"unit": "A",
"name": "AC Current S/V/B"
}, {
"key": "AC3",
"value": "0.00",
"unit": "A",
"name": "AC Current T/W/C"
}, {
"key": "APo_t1",
"value": "4610",
"unit": "W",
"name": "Total AC Output Power (Active)"
}, {
"key": "PI_AC",
"value": "0.00",
"unit": "A",
"name": "Paralleling Inverter AC Current"
}, {
"key": "PI_AV",
"value": "0.00",
"unit": "V",
"name": "Paralleling Inverter AC Voltage"
}, {
"key": "PI_p",
"value": "0.00",
"unit": "W",
"name": "Paralleling Inverter Power"
}, {
"key": "PI_CTS",
"value": "0",
"unit": null,
"name": "Paralleling Inverter CT Test Switch"
}, {
"key": "A_Fo1",
"value": "50.04",
"unit": "Hz",
"name": "AC Output Frequency R"
}, {
"key": "Eydy1",
"value": "7.30",
"unit": "kWh",
"name": "Yesterday Production"
}, {
"key": "Et_ge0",
"value": "891.00",
"unit": "kWh",
"name": "Cumulative Production (Active)"
}, {
"key": "Elast_mon1",
"value": "728",
"unit": "kWh",
"name": "Production Last Month (Active)"
}, {
"key": "Etdy_ge1",
"value": "8.70",
"unit": "kWh",
"name": "Daily Production (Active)"
}, {
"key": "Emon1",
"value": "86",
"unit": "kWh",
"name": "Monthly Production (Active)"
}, {
"key": "Eyr1",
"value": "891",
"unit": "kWh",
"name": "Yearly Production (Active)"
}, {
"key": "ST_PG1",
"value": "Grid connected",
"unit": null,
"name": "Grid Status"
}, {
"key": "PG_V1",
"value": "239.60",
"unit": "V",
"name": "Grid Voltage R/U/A"
}, {
"key": "PG_C1",
"value": "1.92",
"unit": "A",
"name": "Grid Current R/U/A"
}, {
"key": "PG_Pt1",
"value": "210",
"unit": "W",
"name": "Total Grid Power"
}, {
"key": "Q_PG1",
"value": "655340",
"unit": "Var",
"name": "Total Grid Reactive Power"
}, {
"key": "PG_PF1",
"value": "1.00",
"unit": null,
"name": "Grid Power Factor"
}, {
"key": "t_gc1",
"value": "271",
"unit": "kWh",
"name": "Cumulative Grid Feed-in"
}, {
"key": "Et_pu1",
"value": "752",
"unit": "kWh",
"name": "Cumulative Energy Purchased"
}, {
"key": "t_gc_tdy1",
"value": "0.00",
"unit": "kWh",
"name": "Daily Grid Feed-in"
}, {
"key": "Etdy_pu1",
"value": "0.10",
"unit": "kWh",
"name": "Daily Energy Purchased"
}, {
"key": "P_METER0",
"value": "210",
"unit": "W",
"name": "Meter Power"
}, {
"key": "Pgc1",
"value": "213",
"unit": "W",
"name": "Grid-tied Power"
}, {
"key": "Pog1",
"value": "0",
"unit": "W",
"name": "Purchased Power"
}, {
"key": "S_PGt1",
"value": "2910",
"unit": "VA",
"name": "Total Grid Apparent Power"
}, {
"key": "E_Puse_t1",
"value": "2650",
"unit": "W",
"name": "Total Consumption Power"
}, {
"key": "Et_use1",
"value": "1371",
"unit": "kWh",
"name": "Cumulative Consumption"
}, {
"key": "Eydy_ge1",
"value": "57.10",
"unit": "kWh",
"name": "Yesterday Consumption"
}, {
"key": "Etdy_use1",
"value": "8.60",
"unit": "kWh",
"name": "Daily Consumption"
}, {
"key": "B_ST1",
"value": "Charging",
"unit": null,
"name": "Battery Status"
}, {
"key": "B_V1",
"value": "51.90",
"unit": "V",
"name": "Battery Voltage"
}, {
"key": "B_C1",
"value": "28.00",
"unit": "A",
"name": "Battery Current"
}, {
"key": "B_left_cap1",
"value": "96",
"unit": "%",
"name": "SoC"
}, {
"key": "B_HLT_EXP1",
"value": "100",
"unit": "%",
"name": "SoH"
}, {
"key": "t_cg_n1",
"value": "304",
"unit": "kWh",
"name": "Total Charging Energy"
}, {
"key": "t_dcg_n1",
"value": "301",
"unit": "kWh",
"name": "Total Discharging Energy"
}, {
"key": "ydy_cg1",
"value": "6.70",
"unit": "kWh",
"name": "Yesterday Charging Energy"
}, {
"key": "ydy_dcg1",
"value": "7.20",
"unit": "kWh",
"name": "Yesterday Discharging Energy"
}, {
"key": "Etdy_cg1",
"value": "5.40",
"unit": "kWh",
"name": "Daily Charging Energy"
}, {
"key": "Etdy_dcg1",
"value": "5.20",
"unit": "kWh",
"name": "Daily Discharging Energy"
}, {
"key": "BMS_B_V1",
"value": "51.14",
"unit": "V",
"name": "BMS Voltage"
}, {
"key": "BMS_B_C1",
"value": "27.80",
"unit": "A",
"name": "BMS Current"
}, {
"key": "BMS_B_Ccg_thd1",
"value": "29.60",
"unit": "A",
"name": "BMS Battery Current Limiting Charging"
}, {
"key": "BMS_B_Cdcg_thd1",
"value": "74.00",
"unit": "A",
"name": "BMS Battery Current Limiting Discharging"
}, {
"key": "INV_T0",
"value": "52.10",
"unit": "â„ƒ",
"name": "Temperature- Inverter"
}, {
"key": "SYSTIM1",
"value": "21-09-07 12:25:19",
"unit": null,
"name": "System Time"
}, {
"key": "MODE_E_MNG1",
"value": "35",
"unit": null,
"name": "Energy Management Mode"
}, {
"key": "AVb1",
"value": "238.70",
"unit": "V",
"name": "Bypass AC Voltage"
}, {
"key": "Pb_lo1",
"value": "20",
"unit": "W",
"name": "Bypass Load Power"
}]
}

For each key in the data list, I'd like to parse it out into an indexable/reportable field... and, if I was really lucky, the extraction routine would use the name field from each key..

"key": "DP3",
"value": "0",
"unit": "W",
"name": "DC Power PV3"

Am reasonably comfortable with field extractions usually, and have searched various existing posts, but I don't seem to have come across a solution for this specific scenario.

Any/all help really appreciated.

Thanks

Re: JSON Key/Field Extraction

jamesmcgee — Tue, 07 Sep 2021 11:51:41 GMT

I should have included, that Splunk does seem to recognise there are values in they "keys", but doesn't extract them as individual fields...

So, I guess what I am looking for here, is for Splunk to pull out the dataList{}.name, and the corresponding dataList{}.value as a field/key-pair.

Thanks

topic Re: JSON Key/Field Extraction in Getting Data In

JSON Key/Field Extraction

Re: JSON Key/Field Extraction