Splunk Search

Need help in regex

pswalia06
Explorer

{"runDate":"2018-04-18T00:31:46 EDT","dataDate":"20180319","jobName":"experianCounters","counterList":[{"counterName":"experian.input.recordCount","counterValue":[{"subscriberCount":1000100,"distinctSubCount":1000100}]},{"counterName":"experian.output.recordCount","counterValue":[{"subscriberCount":1000100,"distinctSubCount":1000100}]},{"counterName":"experian.output.subscriberCountByAge","counterValue":[{"age":"","count":19953},{"age":"18 - 24","count":30289},{"age":"25 - 34","count":149793},{"age":"35 - 44","count":349856},{"age":"45 - 54","count":250509},{"age":"55 - 64","count":149719},{"age":"65 - 74","count":30008},{"age":"75+","count":19973}]},{"counterName":"experian.output.subscriberCountByGender","counterValue":[{"gender":"","count":79725},{"gender":"B","count":49954},{"gender":"F","count":419773},{"gender":"M","count":450648}]},{"counterName":"experian.output.subscriberCountByIncome","counterValue":[{"income":"","count":19813},{"income":"$1,000 - $14,999","count":39982},{"income":"$100,000 - $124,999","count":100406},{"income":"$125,000 - $149,999","count":29909},{"income":"$15,000 - $24,999","count":60021},{"income":"$150,000 - $174,999","count":20067},{"income":"$175,000 - $199,999","count":79649},{"income":"$200,000 - $249,999","count":20042},{"income":"$25,000 - $34,999","count":80456},{"income":"$35,000 - $49,999","count":99999},{"income":"$50,000 - $74,999","count":249983},{"income":"$75,000 - $99,999","count":199773}]}],"projectName":"RND","releaseVersion":"1.4.0.RNN"}

I want to find the value of age with there corresponding value ? Can you one help me ?

Tags (1)
0 Karma

woodcock
Esteemed Legend

Try this:

| makeresults 
| eval _raw="{\"runDate\":\"2018-04-18T00:31:46 EDT\",\"dataDate\":\"20180319\",\"jobName\":\"experianCounters\",\"counterList\":[{\"counterName\":\"experian.input.recordCount\",\"counterValue\":[{\"subscriberCount\":1000100,\"distinctSubCount\":1000100}]},{\"counterName\":\"experian.output.recordCount\",\"counterValue\":[{\"subscriberCount\":1000100,\"distinctSubCount\":1000100}]},{\"counterName\":\"experian.output.subscriberCountByAge\",\"counterValue\":[{\"age\":\"\",\"count\":19953},{\"age\":\"18 - 24\",\"count\":30289},{\"age\":\"25 - 34\",\"count\":149793},{\"age\":\"35 - 44\",\"count\":349856},{\"age\":\"45 - 54\",\"count\":250509},{\"age\":\"55 - 64\",\"count\":149719},{\"age\":\"65 - 74\",\"count\":30008},{\"age\":\"75+\",\"count\":19973}]},{\"counterName\":\"experian.output.subscriberCountByGender\",\"counterValue\":[{\"gender\":\"\",\"count\":79725},{\"gender\":\"B\",\"count\":49954},{\"gender\":\"F\",\"count\":419773},{\"gender\":\"M\",\"count\":450648}]},{\"counterName\":\"experian.output.subscriberCountByIncome\",\"counterValue\":[{\"income\":\"\",\"count\":19813},{\"income\":\"$1,000 - $14,999\",\"count\":39982},{\"income\":\"$100,000 - $124,999\",\"count\":100406},{\"income\":\"$125,000 - $149,999\",\"count\":29909},{\"income\":\"$15,000 - $24,999\",\"count\":60021},{\"income\":\"$150,000 - $174,999\",\"count\":20067},{\"income\":\"$175,000 - $199,999\",\"count\":79649},{\"income\":\"$200,000 - $249,999\",\"count\":20042},{\"income\":\"$25,000 - $34,999\",\"count\":80456},{\"income\":\"$35,000 - $49,999\",\"count\":99999},{\"income\":\"$50,000 - $74,999\",\"count\":249983},{\"income\":\"$75,000 - $99,999\",\"count\":199773}]}],\"projectName\":\"RND\",\"releaseVersion\":\"1.4.0.RNN\"}"
| rex max_match=0 "{\"age\":\"(?<age>[^\"]+)\",\"count\":(?<count>\d+)"
| foreach age count [eval <<FIELD>>_0=mvindex(<<FIELD>>, 0)
                   | eval <<FIELD>>_1=mvindex(<<FIELD>>, 1)
                   | eval <<FIELD>>_2=mvindex(<<FIELD>>, 2)
                   | eval <<FIELD>>_3=mvindex(<<FIELD>>, 3)
                   | eval <<FIELD>>_4=mvindex(<<FIELD>>, 4)
                   | eval <<FIELD>>_5=mvindex(<<FIELD>>, 5)
                   | eval <<FIELD>>_6=mvindex(<<FIELD>>, 6) ]
| foreach age_* [eval {<<FIELD>>}=count_<<MATCHSTR>>]
| fields - age* count*
0 Karma

TISKAR
Builder

Hello,

If you try this,

  | makeresults
 | eval _raw="{\"runDate\":\"2018-04-18T00:31:46 EDT\",\"dataDate\":\"20180319\",\"jobName\":\"experianCounters\",\"counterList\":[{\"counterName\":\"experian.input.recordCount\",\"counterValue\":[{\"subscriberCount\":1000100,\"distinctSubCount\":1000100}]},{\"counterName\":\"experian.output.recordCount\",\"counterValue\":[{\"subscriberCount\":1000100,\"distinctSubCount\":1000100}]},{\"counterName\":\"experian.output.subscriberCountByAge\",\"counterValue\":[{\"age\":\"\",\"count\":19953},{\"age\":\"18 - 24\",\"count\":30289},{\"age\":\"25 - 34\",\"count\":149793},{\"age\":\"35 - 44\",\"count\":349856},{\"age\":\"45 - 54\",\"count\":250509},{\"age\":\"55 - 64\",\"count\":149719},{\"age\":\"65 - 74\",\"count\":30008},{\"age\":\"75+\",\"count\":19973}]},{\"counterName\":\"experian.output.subscriberCountByGender\",\"counterValue\":[{\"gender\":\"\",\"count\":79725},{\"gender\":\"B\",\"count\":49954},{\"gender\":\"F\",\"count\":419773},{\"gender\":\"M\",\"count\":450648}]},{\"counterName\":\"experian.output.subscriberCountByIncome\",\"counterValue\":[{\"income\":\"\",\"count\":19813},{\"income\":\"$1,000 - $14,999\",\"count\":39982},{\"income\":\"$100,000 - $124,999\",\"count\":100406},{\"income\":\"$125,000 - $149,999\",\"count\":29909},{\"income\":\"$15,000 - $24,999\",\"count\":60021},{\"income\":\"$150,000 - $174,999\",\"count\":20067},{\"income\":\"$175,000 - $199,999\",\"count\":79649},{\"income\":\"$200,000 - $249,999\",\"count\":20042},{\"income\":\"$25,000 - $34,999\",\"count\":80456},{\"income\":\"$35,000 - $49,999\",\"count\":99999},{\"income\":\"$50,000 - $74,999\",\"count\":249983},{\"income\":\"$75,000 - $99,999\",\"count\":199773}]}],\"projectName\":\"RND\",\"releaseVersion\":\"1.4.0.RNN\"}"
| rex mode=sed "s/(\"[^,]*\":)(\"\")/\1\"null\"/g"
 | spath
 | rename counterList{}.counterValue{}.age as age, counterList{}.counterValue{}.count as count, counterList{}.counterName as counterName, counterList{}.counterValue{}.distinctSubCount as distinctSubCount, counterList{}.counterValue{}.gender as gender, counterList{}.counterValue{}.income as income, counterList{}.counterValue{}.subscriberCount as subscriberCount
 | eval index=mvrange(0,mvcount(count))| mvexpand index| eval age=mvindex(age,index), count=mvindex(count,index), gender=mvindex(gender,index), income=mvindex(income,index), subscriberCount=mvindex(subscriberCount,index)
 | table age count gender income subscriberCount
0 Karma

pswalia06
Explorer

This is not displaying what i want. if you look into the age the key is empty for the first column and because of that the value of is showing incorrect
For example
{\"age\":\"\",\"count\":19953}
{\"age\":\"18 - 24\",\"count\":30289}

but when i am running the command
18-24 is displaying 19953

Secondly i want to run the job like below because every time the job will run

index=orion-platform sourcetype=palomar-metrics source="experianCounters.json" | spath
| rename counterList{}.counterValue{}.age as age, counterList{}.counterValue{}.count as count, counterList{}.counterName as counterName, counterList{}.counterValue{}.distinctSubCount as distinctSubCount, counterList{}.counterValue{}.gender as gender, counterList{}.counterValue{}.income as income, counterList{}.counterValue{}.subscriberCount as subscriberCount
| eval index=mvrange(0,mvcount(count))| mvexpand index| eval age=mvindex(age,index), count=mvindex(count,index), gender=mvindex(gender,index), income=mvindex(income,index), subscriberCount=mvindex(subscriberCount,index)
| table age count gender income subscriberCount

0 Karma

TISKAR
Builder

Good Observation, I modfied my proposition, can you test please

0 Karma

richgalloway
SplunkTrust
SplunkTrust

At search time, try ... | rex max_match=0 "\"age\":\"(?<age>[^\"]+)" | .... You may have to experiment with the number of escape characters.

---
If this reply helps you, Karma would be appreciated.
0 Karma

somesoni2
Revered Legend

This seems like a valid JSON contruct, so you can use spath command to extract your fields

your base search
| spath 
| table counterList{}.counterValue{}.age counterList{}.counterValue{}.count

Since there are multiple values, you'll get multivalued field for both age and count. You can expand it to single row like this

your base search
| spath 
| table counterList{}.counterValue{}.age counterList{}.counterValue{}.count | rename counterList{}.counterValue{}.* as *
| eval temp=mvzip(age,count,"##")
| mvexpand temp | rex field=temp "(?<age>[^#]+)##(?<count>.+)" | fields - temp
Get Updates on the Splunk Community!

Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! &#x1f308; In the United States, as well as many countries around the ...

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...

Admin Your Splunk Cloud, Your Way

Join us to maximize different techniques to best tune Splunk Cloud. In this Tech Enablement, you will get ...