Want to extract field from JSON from complex json

[gauravkumar85]

My row data will look like below _row=

{"id":"0","severity":"Information","message":"CPW Total= 844961,SEQ Total =244881, EAS Total=1248892, VRS Total=238, CPW Remaining=74572, SEQ Remaining=22, EAS Remaining =62751, VRS Remaining =0, InvetoryDate =4/15/2024 6:16:07 AM"}

I want to extract fields from message and it will look like below. I tried the through rgex but I am unable to extract. Please help to create extract for 

CPW Total	SEQ Total	EAS Total	VRS Total	CPW Remaining	SEQ Remaining	EAS Remaining	VRS Remaining	InvetoryDate
844961	244881	1248892	238	74572	22	62751	0	4/15/2024 6:16:07 AM

[ITWhisperer]

Your sample data is inconsistently formatted, e.g. sometimes there is a space before/after the =/, Please confirm the exact pattern your data will take so we don't waste effort on invalid data.

[gauravkumar85]

Thank you so much for prompt reply. Below is the fixed format of the data. Please help me on this. 

{"id":"0","severity":"Information","message":"CPWTotal=749860, SEQTotal=1026137, EASTotal=1062804, VRSTotal=238, CPWRemaining=5612, SEQRemaining=32746, EASRemaining=15, VRSRemaining=0, InvetoryDate=4/16/2024 7:34:25 PM"} 

Need to extract fields in below format. Your help really appreciated.  

CPW Total	SEQ Total	EAS Total	VRS Total	CPW Remaining	SEQ Remaining	EAS Remaining	VRS Remaining	InvetoryDate
844961	244881	1248892	238	74572	22	62751	0	4/15/2024 6:16:07 AM

[ITWhisperer · Accepted solution]

Assuming Invetory is spelled (in)correctly, you could try this - the rex at the end is required because this date has an embedded space and it is the last field in the message

| makeresults 
| eval _raw="{\"id\":\"0\",\"severity\":\"Information\",\"message\":\"CPWTotal=749860, SEQTotal=1026137, EASTotal=1062804, VRSTotal=238, CPWRemaining=5612, SEQRemaining=32746, EASRemaining=15, VRSRemaining=0, InvetoryDate=4/16/2024 7:34:25 PM\"}"
| spath
| rename message as _raw
| extract
| rex "InvetoryDate=(?<InvetoryDate>.*)"

If the fields were re-ordered or an extra field was in the message (without an embedded space),  then the rex would not be required

| makeresults 
| eval _raw="{\"id\":\"0\",\"severity\":\"Information\",\"message\":\"CPWTotal=749860, SEQTotal=1026137, EASTotal=1062804, VRSTotal=238, CPWRemaining=5612, SEQRemaining=32746, EASRemaining=15, VRSRemaining=0, InvetoryDate=4/16/2024 7:34:25 PM, Tail=True\"}"
| spath
| rename message as _raw
| extract

[gauravkumar85]

{"id":"0","severity":"Information","message":"[{\"TARGETSYSTEM\":\"SEQ\",\"ARUNAME\":\"CPW_02170\",\"TOTAL\":437330,\"PROCESSED\":436669,\"REMAINING\":661,\"ERROR\":0,\"SKIPPED\":112},{\"TARGETSYSTEM\":\"SEQ\",\"ARUNAME\":\"CPW_02171\",\"TOTAL\":78833,\"PROCESSED\":78832,\"REMAINING\":1,\"ERROR\":0,\"SKIPPED\":35},{\"TARGETSYSTEM\":\"SEQ\",\"ARUNAME\":\"CPW_02169H\",\"TOTAL\":100192,\"PROCESSED\":100192,\"REMAINING\":0,\"ERROR\":0,\"SKIPPED\":20016},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_00061\",\"TOTAL\":7,\"PROCESSED\":0,\"REMAINING\":7,\"ERROR\":0,\"SKIPPED\":0},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_01015\",\"TOTAL\":9,\"PROCESSED\":0,\"REMAINING\":9,\"ERROR\":0,\"SKIPPED\":0},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_00011H\",\"TOTAL\":17,\"PROCESSED\":0,\"REMAINING\":17,\"ERROR\":0,\"SKIPPED\":0},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_00079\",\"TOTAL\":0,\"PROCESSED\":0,\"REMAINING\":0,\"ERROR\":0,\"SKIPPED\":0},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_02191\",\"TOTAL\":0,\"PROCESSED\":0,\"REMAINING\":0,\"ERROR\":0,\"SKIPPED\":0},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_02184\",\"TOTAL\":0,\"PROCESSED\":0,\"REMAINING\":0,\"ERROR\":0,\"SKIPPED\":0},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_07009CS\",\"TOTAL\":0,\"PROCESSED\":0,\"REMAINING\":0,\"ERROR\":0,\"SKIPPED\":0},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_00304\",\"TOTAL\":1318,\"PROCESSED\":1318,\"REMAINING\":0,\"ERROR\":0,\"SKIPPED\":24},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_00314\",\"TOTAL\":6188,\"PROCESSED\":6188,\"REMAINING\":0,\"ERROR\":0,\"SKIPPED\":1},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_00355\",\"TOTAL\":505,\"PROCESSED\":462,\"REMAINING\":43,\"ERROR\":0,\"SKIPPED\":11},{\"TARGETSYSTEM\":\"CPW\",\"ARUNAME\":\"CPW_00364\",\"TOTAL\":12934,\"PROCESSED\":2804,\"REMAINING\":10130,\"ERROR\":0,\"SKIPPED\":1},{\"\":\"EAS\",\"ARUNAME\":\"CPW_02130\",\"TOTAL\":0,\"PROCESSED\":0,\"REMAINING\":0,\"ERROR\":0,\"SKIPPED\":0}]"}

I want below two views from same data

First View:

Second view:

[gauravkumar85]

Thanks for you response. Your solution is working fine and create below query for search. 

index = **** host=***| spath | eval message="{\"message\":".message."}" | spath input=message message{} output=collection | mvexpand collection | spath input=collection | stats sum(TOTAL) as Total, sum(PROCESSED) as Processed sum(SKIPPED) as Skipped by TARGETSYSTEM

I am using above query. Below chart is created using above query. Now I want to display inventory with date in chart. 

I want display like below

[ITWhisperer]

This other chart seems to be related to a different search, particularly as it appears to have a date on the x-axis which does not appear as a column in your search.

[ITWhisperer]

Assuming this is supposed to be good JSON (which it isn't) and that you had missed a field name on the last object in the collection, you could try this.

| spath
``` Fix up message to make a valid JSON field ```
| eval message="{\"message\":".message."}"
``` Get the collection from message ```
| spath input=message message{} output=collection
``` Expand the collection into separate events ```
| mvexpand collection
``` Extract the fields ```
| spath input=collection
``` Assume you want the totals by ARUNAME ```
| stats sum(TOTAL) as Total, sum(PROCESSED) as Processed sum(REMAINING) as Remaining sum(ERROR) as Error sum(SKIPPED) as Skipped by ARUNAME

For the first view, you would remove the by clause from the stats command

[yuanliu]

Assuming Invetory is spelled (in)correctly, you could try this - the rex at the end is required because this date has an embedded space and it is the last field in the message

If the fields were re-ordered or an extra field was in the message (without an embedded space),  then the rex would not be required

---

The problem is less embedded space, more lack of embedded quotation marks/proper field separator.  It is semantically more pleasing to fix structure with rex than using rex to extract one data snippet when most are extracted with extract command. (But if you have any influence on developers, beg them to add quotation marks - more on this later.)

| rex field=message mode=sed "s/Date=/&\"/ s/$/\"/"
| rename message as _raw
| extract

It would give you the same result like

CPWRemaining	CPWTotal	EASRemaining	EAStatal	InvetoryDate	SEQRemaining	SEQTotal	VRSRemaining	VRSTotal	id	severity
5612	749860	15	1062804	4/16/2024 7:34:25 PM	32746	1026137	0	238	0	Information

About feedback to developers.  @ITWhisperer gave one option by taking advantage of a side effect/gem feature) from Splunk's extract command by adding a comma at the end of every key-value pair.  They do not have to swap order, but also by simply adding a literal comma after each value, like this:

{"id":"0","severity":"Information","message":"CPWTotal=749860, SEQTotal=1026137, EASTotal=1062804, VRSTotal=238, CPWRemaining=5612, SEQRemaining=32746, EASRemaining=15, VRSRemaining=0, InvetoryDate=4/16/2024 7:34:25 PM,"}

A more robust fix (that does not rely on Splunk's "generosity") is to properly quote the value.  Any language can extract that without the programmer's attention.

{"id":"0","severity":"Information","message":"CPWTotal=749860, SEQTotal=1026137, EASTotal=1062804, VRSTotal=238, CPWRemaining=5612, SEQRemaining=32746, EASRemaining=15, VRSRemaining=0, InvetoryDate=\"4/16/2024 7:34:25 PM\""}

The logic should be simple enough: Numeric data, no quote, string data, quote.