topic Re: how to show json data from different places in a table in Getting Data In

How to show json data from different places in a table?

Jasmine — Thu, 30 Mar 2023 15:01:19 GMT

Hi,

i have below json data in splunk logs at different places(different rows). All are belongs to the unique id : 123456JKL..

{"Id": "123456JKL", "Table1": "employee", "department": "admin"}
{"Id": "123456JKL", "Table2": "salary", "joineddate": "value"}
{"Id": "123456JKL", "pay": "{type:"test","name":"jas"}", "joineddate": "value"}

i want to show all json data under same Id in a single row in splunk dashboard. Need to group by common value "Id": "123456JKL"

Please help here

Re: how to show json data from different places in a table

ITWhisperer — Thu, 30 Mar 2023 09:35:05 GMT

| spath | stats values(*) as * by id

Re: how to show json data from different places in a table

Jasmine — Thu, 30 Mar 2023 10:07:06 GMT

i tried. but i dont see any data in table format .. but says 4 events.

this is my actual query

sourcetype="test" index=aaa env=test family=jas app=demo "Id" | spath | stats values(*) as * by Id

Re: how to show json data from different places in a table

ITWhisperer — Thu, 30 Mar 2023 10:26:02 GMT

Please can you share some anonymised sample events in a code block </> so that formatting is preserved?

Re: how to show json data from different places in a table

Jasmine — Thu, 30 Mar 2023 10:52:18 GMT

my search query in splunk is

</> sourcetype="test" index=aaa env=test family=jas app=demo "Id" </>

My search result from the above query is below with timestamps:

will this json format good to show in splunk dashboard ?

</>

i tied as below to show the columns in single row - (group by id - for each row)

ID , Table1, department, Table2, joineddate, pay,joineddate

</> sourcetype="test" index=aaa env=test family=jas app=demo "Id" | spath |stats values(*) as * by Id </>

but i dont see any data.. Please help me

Re: how to show json data from different places in a table

ITWhisperer — Thu, 30 Mar 2023 11:16:21 GMT

By code block I meant this </> button

For example, here is a runanywhere example for your usecase

| makeresults | fields - _time | eval _raw="{\"Id\": \"123456JKL\", \"Table1\": \"employee\", \"department\": \"admin\"} {\"Id\": \"123456JKL\", \"Table2\": \"salary\", \"joineddate\": \"value\"} {\"Id\": \"123456JKL\", \"pay\": \"{type:\\\"test\\\",\\\"name\\\":\\\"jas\\\"}\", \"joineddate\": \"value\"}" | multikv noheader=t | fields _raw | spath | stats values(*) as * by Id

Note that I have modified your example to make the JSON valid for embedded JSON strings

Here is the example where pay is an embedded JSON object rather than a string

| makeresults | fields - _time | eval _raw="{\"Id\": \"123456JKL\", \"Table1\": \"employee\", \"department\": \"admin\"} {\"Id\": \"123456JKL\", \"Table2\": \"salary\", \"joineddate\": \"value\"} {\"Id\": \"123456JKL\", \"pay\": {\"type\":\"test\",\"name\":\"jas\"}, \"joineddate\": \"value\"}" | multikv noheader=t | fields _raw | spath | stats values(*) as * by Id

Re: how to show json data from different places in a table

Jasmine — Thu, 30 Mar 2023 13:30:00 GMT

ok. I have three more questions:

1) How can I integrate makeresults with sourcetype , index, family... I tried as below. but not working. can you please suggest me

"pay": "{type:"test","name":"jas"}"

here I don't want to split in multiple columns, I want to show in single column- only for pay

3) do we have any built in function to embed \ in splunk as my input is as below from splunk search:

Re: how to show json data from different places in a table

ITWhisperer — Thu, 30 Mar 2023 13:49:56 GMT

I have used makeresults to simulate what I think you have in your events. This is only a guess as you haven't shared your raw events or may be you have. If your raw events are just JSON structures then you can do this:

sourcetype="test" index=aaa env=test family=jas app=demo "Id" | spath | stats values(*) as * by Id

If not, please share your raw events in a code block </>

Re: how to show json data from different places in a table

Jasmine — Thu, 30 Mar 2023 15:58:26 GMT

Please find my events:

2023-03-30 13:41:52,578 - __main__ - INFO - {"Id": "123456JKL", "Table1": "employee", "department": "admin"} 2023-03-30 13:41:52,474 - __main__ - INFO - {"Id": "123456JKL", "Table2": "salary", "joineddate": "value"} 2023-03-30 13:41:51,873 - __main__ - INFO - {"Id": "123456JKL", "pay": "{type:"test","name":"jas"}", "joineddate": "value", "address": "US"}

I want to show all the info belong to one ID in single row.

Re: how to show json data from different places in a table

ITWhisperer — Thu, 30 Mar 2023 16:56:35 GMT

Given that you don't appear to have well-formed JSON data, and assuming the formatting is as you have shown, try this

| makeresults | fields - _time | eval _raw="2023-03-30 13:41:52,578 - __main__ - INFO - {\"Id\": \"123456JKL\", \"Table1\": \"employee\", \"department\": \"admin\"} 2023-03-30 13:41:52,474 - __main__ - INFO - {\"Id\": \"123456JKL\", \"Table2\": \"salary\", \"joineddate\": \"value\"} 2023-03-30 13:41:51,873 - __main__ - INFO - {\"Id\": \"123456JKL\", \"pay\": \"{type:\"test\",\"name\":\"jas\"}\", \"joineddate\": \"value\", \"address\": \"US\"}" | multikv noheader=t | fields _raw ``` The lines above generate your sample data ``` | rex max_match=0 "\{?\"(?<field>[^\"]+)\":\s\"(?<value>(\{.*?\}|[^\{\}]*?))\"(,\s|\})" | eval count=mvcount(field) | eval row=mvrange(0,count) | streamstats count as event | mvexpand row | eval field = mvindex(field,row) | eval {field} = mvindex(value,row) | fields - row field value _raw count | stats values(*) as * by event | fields - event | stats values(*) as * by Id

Re: how to show json data from different places in a table

Jasmine — Thu, 30 Mar 2023 17:19:24 GMT

This looks good. But i cannot pass static msg in eval as below:

| eval _raw="2023-03-30 13:41:52,578 - __main__ - INFO - {\"Id\": \"123456JKL\", \"Table1\": \"employee\", \"department\": \"admin\"} 2023-03-30 13:41:52,474 - __main__ - INFO - {\"Id\": \"123456JKL\", \"Table2\": \"salary\", \"joineddate\": \"value\"} 2023-03-30 13:41:51,873 - __main__ - INFO - {\"Id\": \"123456JKL\", \"pay\": \"{type:\"test\",\"name\":\"jas\"}\", \"joineddate\": \"value\", \"address\": \"US\"}"

as i mentioned earlier, my search would be

sourcetype="test" index=aaa env=test family=jas app=demo "Id"

after searching my result would be as below:

for this search result i want to show in single row.. Hope this helps?

also do we have any inbuild function to embed slashes in json values?

Re: how to show json data from different places in a table

ITWhisperer — Thu, 30 Mar 2023 18:44:14 GMT

sourcetype="test" index=aaa env=test family=jas app=demo "Id" | rex max_match=0 "\{?\"(?<field>[^\"]+)\":\s\"(?<value>(\{.*?\}|[^\{\}]*?))\"(,\s|\})" | eval count=mvcount(field) | eval row=mvrange(0,count) | streamstats count as event | mvexpand row | eval field = mvindex(field,row) | eval {field} = mvindex(value,row) | fields - row field value _raw count | stats values(*) as * by event | fields - event | stats values(*) as * by Id

Re: how to show json data from different places in a table

Jasmine — Fri, 31 Mar 2023 06:12:27 GMT

Hi,

I tried the above one.

I am getting as below:

Field 'row' does not exist in the data.

Complete 3 events (3/31/23 12:58:00.000 AM to 3/31/23 1:58:03.000 AM)

throws error at | mvexpand row

when i execute upto below piece of code

I got the output as below :

2023-03-31 05:14:16,447 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Table1\": \"employee\", \"Time1\": \"3.04\"}"

2023-03-31 05:14:16,393 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Table2\": \"salary\", \"Time2\": \"4.05\"}"

2023-03-31 05:20:16,393 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Table2\": \"salary1\",\"PayLoad\": {\"type\":\"test\",\"name\":\"jas\"}"

Please help me

Re: how to show json data from different places in a table

ITWhisperer — Fri, 31 Mar 2023 06:54:25 GMT

This is obviously made-up data - it has been constructed differently to the previous examples which my suggestion was based on. It seems I am wasting my time trying to construct a solution based on fake data. If you want something that works with your actual data, please provide an accurate representation of your data. You can of course mask sensitive elements, but the structure and formatting should be as accurate as possible.

Re: how to show json data from different places in a table

Jasmine — Fri, 31 Mar 2023 07:30:23 GMT

Thanks for quick reply.. I am sorry. I believe i am giving same data. Per your advise i have handled backslashes for json data. (this is the only difference between my earlier one and current one). Your previous query helped me a lot. but makeresults i am not able integrate with sourcetype.

To be more clear

My search in splunk would be

sourcetype="test" index=aaa env=test family=jas app=demo "Id"

After search i get below data and have to show in same row based on Id.

2023-03-31 05:14:16,447 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Table1\": \"employee\", \"Time1\": \"3.04\"}" 2023-03-31 05:14:16,393 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Table2\": \"salary\", \"Time2\": \"4.05\"}" 2023-03-31 05:20:16,393 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Table2\": \"salary1\",\"PayLoad\": {\"type\":\"test\",\"name\":\"jas\"}"

Please help me

Re: how to show json data from different places in a table

ITWhisperer — Fri, 31 Mar 2023 08:09:57 GMT

Your formatting has changed again - your third line is not valid JSON - was it supposed to be?

sourcetype="test" index=aaa env=test family=jas app=demo "Id" | rex max_match=0 "\{?\\\"(?<field>[^\\\]+)\\\\\":\s(\\\\\")?(?<value>(\{.*\}|[^\\\]*))(\\\\\")?(,\s|\}|)" | eval count=mvcount(field) | eval row=mvrange(0,count) | streamstats count as event | mvexpand row | eval field = mvindex(field,row) | eval {field} = mvindex(value,row) | fields - row field value _raw count | stats values(*) as * by event | fields - event | stats values(*) as * by Id

Re: how to show json data from different places in a table

Jasmine — Fri, 31 Mar 2023 08:50:04 GMT

Thank you very much for your patience and quick reply.

This is what i required.. Thanks once again.

Clarifications: It also provides date_hour, date_mday,date_minute, punct.

how can we remove the fields which are not required

do we have control to change the order of columns?

Yes as you said third line is not properly formatted. this is my raw data.. to format properly can i just add backslashes with each items or i can work for any other format to show in splunk(single cell)

{"type":"data","Id":"test","source":"In","sub":[{"eng":"30","cost":10.0,"status":"sold"}]}

Thanks

Re: how to show json data from different places in a table

ITWhisperer — Fri, 31 Mar 2023 09:01:02 GMT

With valid JSON data you can use spath (as I originally suggest) - if you want to keep to just one level of expansion of the JSON structure you could do it separately and remove the expanded fields

| spath input=_raw sub{} output=sub | spath input=_raw | fields - sub{}.*

You might need to replace "_raw" with the field name that your JSON is in