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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...