topic Want to extract field from JSON from complex json in Splunk Search

Want to extract field from JSON from complex json

gauravkumar85 — Tue, 16 Apr 2024 09:41:43 GMT

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

Re: Want to extract field from JSON from complex json

ITWhisperer — Tue, 16 Apr 2024 13:35:04 GMT

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.

Re: Want to extract field from JSON from complex json

gauravkumar85 — Tue, 16 Apr 2024 14:19:56 GMT

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

Re: Want to extract field from JSON from complex json

ITWhisperer — Tue, 16 Apr 2024 14:58:29 GMT

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

Re: Want to extract field from JSON from complex json

yuanliu — Tue, 16 Apr 2024 18:25:27 GMT

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:

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.

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

Re: Want to extract field from JSON from complex json

gauravkumar85 — Wed, 17 Apr 2024 21:38:11 GMT

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

Re: Want to extract field from JSON from complex json

ITWhisperer — Thu, 18 Apr 2024 07:34:04 GMT

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

Re: Want to extract field from JSON from complex json

gauravkumar85 — Mon, 22 Apr 2024 08:57:02 GMT

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

Re: Want to extract field from JSON from complex json

ITWhisperer — Mon, 22 Apr 2024 09:02:30 GMT

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.