topic Re: Need help in regex in Splunk Search

Need help in regex

pswalia06 — Thu, 19 Apr 2018 05:34:29 GMT

{"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 ?

Re: Need help in regex

somesoni2 — Thu, 19 Apr 2018 16:40:32 GMT

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

Re: Need help in regex

richgalloway — Thu, 19 Apr 2018 16:44:10 GMT

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

Re: Need help in regex

TISKAR — Fri, 20 Apr 2018 09:33:44 GMT

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

Re: Need help in regex

pswalia06 — Sat, 21 Apr 2018 05:03:24 GMT

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

Re: Need help in regex

TISKAR — Sat, 21 Apr 2018 12:17:11 GMT

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

Re: Need help in regex

woodcock — Sun, 22 Apr 2018 17:25:15 GMT

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*