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

[Jasmine]

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

[ITWhisperer]

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

[Jasmine]

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

[ITWhisperer]

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

[Jasmine]

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 ?

</>

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

</>

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

[ITWhisperer]

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

[Jasmine]

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

sourcetype="test" index=aaa env=test family=jas app=demo "Id" |append  [| makeresults 
    | eval fields1=split(_raw,":") | multikv noheader=t
| fields fields1
| spath
| stats values(*) as * by Id
]

2)

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

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

[ITWhisperer]

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 </>

[Jasmine]

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.

[ITWhisperer]

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

[Jasmine]

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:

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

  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?

[ITWhisperer]

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

[Jasmine]

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

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 

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

[ITWhisperer]

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.

[Jasmine]

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

[ITWhisperer]

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

[Jasmine]

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

[ITWhisperer]

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