Splunk Search

How to fill null values in JSon field

nzamorano123
Engager
Spoiler
How to fill null values in JSon field

hello community, good afternoon
I am trapped in a challenge which I cannot achieve how to obtain the expected result.

Currently I have a log that contains a field in JSon format:

{
"data": {
"steps": [
{
"code": "11501",
"index": "0",
"counter": "1",
"stepType": "Req & Resp",
"optional": "False",
"parallel": "False",
"parents": "-1",
"children": "1",
"begin": "2021-03-24T10:00:02.597-03:00",
"serviceOut": "2021-03-24T10:00:02.598-03:00",
"serviceIn": "2021-03-24T10:00:02.857-03:00",
"end": "2021-03-24T10:00:02.859-03:00",
"timedout": "False",
"lateResp": "False",
"status": "C",
"return": ""
},
{
"code": "11502",
"index": "1",
"counter": "2",
"stepType": "Req & Resp",
"optional": "False",
"parallel": "False",
"parents": "0",
"children": "2",
"begin": "2021-03-24T10:00:02.860-03:00",
"serviceOut": "2021-03-24T10:00:02.886-03:00",
"serviceIn": "2021-03-24T10:00:03.238-03:00",
"end": "2021-03-24T10:00:03.243-03:00",
"timedout": "False",
"lateResp": "False",
"status": "C",
"return": "03546"
},
{
"code": "11505",
"index": "4",
"counter": "3",
"stepType": "Req & Resp",
"optional": "False",
"parallel": "False",
"parents": "3",
"children": "5",
"begin": "2021-03-24T10:00:03.246-03:00",
"serviceOut": "2021-03-24T10:00:03.250-03:00",
"serviceIn": "2021-03-24T10:00:03.293-03:00",
"end": "2021-03-24T10:00:03.294-03:00",
"timedout": "False",
"lateResp": "False",
"status": "C",
"return": "03546"
}
]
}
}
Query Splunk:

index="pagos_moviles" source="/veritran/vt-net/log/MP_TRANSAC_LOG_BECP_ND*.log" trxl_resp!="000"

| spath input=trxl_tech_detail | fields _time trxl_tech_detail data.steps*
| fillnull value="-" data.steps*
|rename data.steps{}.begin AS begin, data.steps{}.children AS step, data.steps{}.code AS code, data.steps{}.counter AS counter, data.steps{}.end AS end, data.steps{}.index AS index, data.steps{}.lateResp AS lateResp, data.steps{}.optional AS optional, data.steps{}.parallel AS parallel, data.steps{}.parents AS parents, data.steps{}.return AS return, data.steps{}.serviceIn AS serviceIn, data.steps{}.serviceOut AS serviceOut, data.steps{}.status AS status, data.steps{}.stepType AS stepType, data.steps{}.timedout AS timedout| eval campos=mvzip(begin,mvzip(step,mvzip(code,mvzip(end,mvzip(index,mvzip(status,mvzip(return,timedout)))))))
| mvexpand campos | eval campos=split(campos,",")|eval begin =mvindex(campos,0)
|eval step=mvindex(campos,1)
|eval code =mvindex(campos,2)
|eval end =mvindex(campos,3)
|eval index =mvindex(campos,4)
|eval status=mvindex(campos,5)
|eval return=mvindex(campos,6)
|eval timedout =mvindex(campos,7)| table _time trxl_tech_detail campos Servicio Operacion begin step code counter end index return status timedout trxl_resp Detalle Tipo_Error trxl_transaction_code
| fillnull value="-" return


Result: 

evidencia.jpg

 

As you can see in the photo, it only expands 2 rows and they should be 3. I also noticed that the return value does not always exist and I think this is what finally causes it not to expand all the json.

Is there a way to fill the null values in the json with some character?

In advance, thank you very much and excuse me for my English but it is not my native language.

 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Yes, the issue is with the null values for return (although in your example, return is an empty string not null) - try extracting the array, mvexpand, then extract the fields - this saves on doing the mvzip and split as well.

index="pagos_moviles" source="/veritran/vt-net/log/MP_TRANSAC_LOG_BECP_ND*.log" trxl_resp!="000"

| spath input=trxl_tech_detail path=data.steps{}
| mvexpand data.steps{}
| spath input=data.steps{}
| table _time trxl_tech_detail campos Servicio Operacion begin step code counter end index return status timedout trxl_resp Detalle Tipo_Error trxl_transaction_code
| fillnull value="-" return

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Yes, the issue is with the null values for return (although in your example, return is an empty string not null) - try extracting the array, mvexpand, then extract the fields - this saves on doing the mvzip and split as well.

index="pagos_moviles" source="/veritran/vt-net/log/MP_TRANSAC_LOG_BECP_ND*.log" trxl_resp!="000"

| spath input=trxl_tech_detail path=data.steps{}
| mvexpand data.steps{}
| spath input=data.steps{}
| table _time trxl_tech_detail campos Servicio Operacion begin step code counter end index return status timedout trxl_resp Detalle Tipo_Error trxl_transaction_code
| fillnull value="-" return

nzamorano123
Engager
 

 This worked perfectly. Thank you very much for your help.

I understand that I was skipping the step of specifying to spath the data that I wanted to expand.

Again, thank you very much!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...