topic Re: Splunk json dymanic field extraction in Splunk Search

Help with Splunk json dymanic field extraction

Sanjana — Tue, 27 Sep 2022 14:09:30 GMT

Hey all,

I am trying to extract dynamic field from json .

{"period":{"start":"2023-04-17","end":"2023-05-14"},"check-ins":{"203":{"avail":5,"price":5},"204":{"avail":5,"price":5},"205":{"avail":5,"price":5},"206":{"avail":5,"price":5},"207":{"avail":5,"price":5},"208":{"avail":5,"price":5},"209":{"avail":5,"price":5},"210":{"avail":5,"price":5},"211":{"avail":5,"price":5},"212":{"avail":5,"price":5},"213":{"avail":5,"price":5},"214":{"avail":5,"price":5},"215":{"avail":5,"price":5},"216":{"avail":5,"price":5},"217":{"avail":5,"price":5},"218":{"avail":5,"price":5},"219":{"avail":19,"price":5},"220":{"avail":19,"price":5},"221":{"avail":19,"price":5},"222":{"avail":19,"price":5},"223":{"avail":19,"price":5},"224":{"avail":19,"price":5},"225":{"avail":19,"price":5},"226":{"avail":19,"price":5},"227":{"avail":19,"price":5},"228":{"avail":19,"price":5},"229":{"avail":20,"price":5},"230":{"avail":20,"price":5}}}

I need to extract 203,204,205.........till 230 as per data mentioned above then each extracted value will be added in period.start field . At the end I need that date value after addition

Thanks in advance

Re: Splunk json dymanic field extraction

yuanliu — Tue, 27 Sep 2022 05:50:13 GMT

This is very much like https://community.splunk.com/t5/Splunk-Search/How-to-convert-check-ins-index-into-date-then-calculate-desync/m-p/614663/highlight/false#M213616. You can use the same strategy.

| eval period_start = strptime('period.start', "%F") | foreach check-ins.*.avail [eval suffix = mvappend(suffix, "<<MATCHSTR>>")] | mvexpand suffix | eval datevalue = period_start + 86400 * suffix | fields datevalue | fieldformat datevalue = strftime(datevalue, "%F")

Using your sample data, you get

datevalue
2023-11-05
2023-11-06
2023-11-07
2023-11-08
2023-11-09
2023-11-10
2023-11-11
2023-11-12
2023-11-13
2023-11-14
2023-11-15
2023-11-16
2023-11-17
2023-11-18
2023-11-19
2023-11-20
2023-11-21
2023-11-22
2023-11-23
2023-11-24
2023-11-25
2023-11-26
2023-11-27
2023-11-28
2023-11-29
2023-11-30
2023-12-01
2023-12-02

Here, I assume you want to display in calendar format, hence fieldformat. If you want the date value to be numeric, just omit that. (The advantage of fieldformat is that the field itself is still in numeric.)

Re: Splunk json dymanic field extraction

Sanjana — Tue, 27 Sep 2022 09:46:06 GMT

Hello @yuanliu ,

I tried above response.but no luck. I am giving you exact scenario which I m trying to work.

This is my actual json structure of splunk data.

"content":
{"kIndexKey_EventMessage":
{
"period":{"start":"2022-10-03","end":"2022-10-19"},
"check-ins":{
"12":{"avail":13,"price":0},
"13":{"avail":13,"price":0},
"14":{"avail":13,"price":0},
"15":{"avail":13,"price":0},
"16":{"avail":13,"price":0},
"17":{"avail":13,"price":0},
"18":{"avail":13,"price":0},
"19":{"avail":13,"price":0},
"20":{"avail":13,"price":0},
"21":{"avail":13,"price":0},
"22":{"avail":13,"price":0},
"23":{"avail":13,"price":0},
"24":{"avail":13,"price":0},
"25":{"avail":13,"price":0},
"26":{"avail":13,"price":0},
"27":{"avail":13,"price":0},
"28":{"avail":13,"price":0}
}
}

My search is something like below:

index="index1"
| fields _raw
| spath content.kIndexKey_EventMessage{1}.check-ins
| spath input=content.kIndexKey_EventMessage{1}.check-ins
|spath output=period_start path=content.kIndexKey_EventMessage{1}.period.start

| eval period_start = strptime('period.start', "%F")
| foreach check-ins.*.avail
[eval suffix = mvappend(suffix, "<<MATCHSTR>>")]
| mvexpand suffix
| eval datevalue = period_start + 86400 * suffix
| fields datevalue
| fieldformat datevalue = strftime(datevalue, "%F")

After executing this ->Getting below erro without any data as you shown

Field 'suffix' does not exist in the data.

Could you please help me where I m getting wrong in this query

Re: Splunk json dymanic field extraction

kamlesh_vaghela — Tue, 27 Sep 2022 10:17:24 GMT

@Sanjana

Can you please try this?

YOUR_SEARCH | spath content.kIndexKey_EventMessage{1}.check-ins | spath input=content.kIndexKey_EventMessage{1}.check-ins | spath output=period_start path=content.kIndexKey_EventMessage{1}.period.start | eval period_start = strptime('period_start', "%F") | foreach *.avail [ eval suffix = mvappend(suffix, "<<MATCHSTR>>")] | mvexpand suffix | eval datevalue = period_start + 86400 * suffix | fields datevalue | fieldformat datevalue = strftime(datevalue, "%F")

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Splunk json dymanic field extraction

Sanjana — Tue, 27 Sep 2022 14:49:39 GMT

Hey @kamlesh_vaghela

Thanks for the response. It worked fine.

Sorry to bug you again. I have two queries running separate fine.

1. Query

MY_SEARCH

| spath content.kIndexKey_EventMessage{1}.check-ins
| spath input=content.kIndexKey_EventMessage{1}.check-ins
| spath output=period_start path=content.kIndexKey_EventMessage{1}.period.start
| eval period_start = strptime('period_start', "%F")
| foreach *.avail
[ eval suffix = mvappend(suffix, "<<MATCHSTR>>")]
| mvexpand suffix
| eval datevalue = period_start + 86400 * suffix
| fields datevalue
| fieldformat datevalue = strftime(datevalue, "%F")
|table datevalue

Output

datevalue

2022-09-27

2022-09-28

2022-09-29

2022-09-30

2.Query:

My_Search

| fields _raw
|spath output=chain path=content.kIndexKey_EventMessage{1}.chain
|spath output=start path=content.kIndexKey_EventMessage{1}.period.start
| where like(chain, "IHG%")
| spath content.kIndexKey_EventMessage{1}.check-ins
| spath input=content.kIndexKey_EventMessage{1}.check-ins
| foreach *.check-ins
[| eval "<<MATCHSEG1>>"='<<FIELD>>'+'start']
| foreach *.avail
[| eval "<<MATCHSEG1>>"='<<FIELD>>']
| foreach *.price
[| eval "<<MATCHSEG1>>"='<<MATCHSEG1>>'+'<<FIELD>>']
| dedup _raw,_time
| fields - *.avail *.price check_ins check-ins _time
| untable _raw check_ins nb_desync
| search nb_desync > 0
| stats sum(nb_desync) as nb_desync by check_ins | sort -nb_desync
|join type=left
[search index="int-acrs-cde_appevent-shared"
|spath output=chain path=content.kIndexKey_EventMessage{1}.chain
| where like(chain, "IHG")
|spath output=nb_avail_desync path=content.kIndexKey_EventMessage{1}.total.avail
|spath output=nb_price_desync path=content.kIndexKey_EventMessage{1}.total.price
| eval tot_desync=nb_avail_desync+nb_price_desync
| stats sum(tot_desync) as total ]
| eval percent = round(nb_desync*100/total,1)
| search percent > 0
| fields - total_Desync , percent,total

Output:

check_ins nb_desync

0	13
1	13
2	13

I am trying to combine both query such that my output should look something as mentioned below:

1st column from 1st query as date

2nd column form 2nd query as nb_desync

datevalue nb_desync

2022-09-27	13
2022-09-28	13
2022-09-29	13
2022-09-30	13

Thanks in advance!! Looking for your response.

Re: Splunk json dymanic field extraction

kamlesh_vaghela — Tue, 27 Sep 2022 15:47:10 GMT

@Sanjana

Can you please let us know how you are relating 1st query output with 2nd query output? if it is one-to-one mapping then just try the below search.

My Sample Search :

If there is some other correlation logic then please share _raw for 2nd query and logic.