topic Re: calculate sum of multiple fields in different lines identified using regex in Splunk Search

calculate sum of multiple fields in different lines identified using regex

karthik2146 — Tue, 29 Sep 2020 10:29:06 GMT

I want to calculate sum of multiple fields which occur in different lines in logs
I have logs like

bmwcar=10
bmwtruck=5
nissantruck=5
renaultcar=4
mercedescar=10
suzukicar=10
tatatruck=5
bmwcar=2
nissantruck=15

i want to have timechart with sum of all cars and sum of all truck, so my output should be car=36, truck=30.
i can do it like index="xxxx" sourcetype="web_stats" | timechart span=1d eval (sum(bmwcar)+sum(renaultcar)....etc) but this list is not fixed as a new car can be logged any time in future.

so, i am using regex (.*car) and (.*truck) but i am not able to sum up all cars together and trucks together.

index="xxxx" sourcetype="web_stats"  *car OR *truck | rex "(?<vehicle>(.*car=[\d]+) | (.*truck=[\d]+) )"  | table vehicle, _time | mvexpand vehicle | rex field=vehicle ".*=(?<cnt>(\d+))" | search cnt!=0 | timechart span=1d sum(cnt)

by the above query, either i can get sum of all cars and trucks together or cars and trucks in a separate chart using separate quereies. but i wanted to have cars and trucks in a same chart in a single query.
could you suggest any way to do it?

Re: calculate sum of multiple fields in different lines identified using regex

richgalloway — Fri, 05 Aug 2016 11:46:04 GMT

Try this.

index="xxxx" sourcetype="web_stats" *car OR *truck | rex "(?<vehicle>(.*car=[\d]+) | (.*truck=[\d]+) )"  | rex field=vehicle ".*(?<type>[^=]+)=(?<cnt>\d+)" | timechart span=1d sum(cnt) by type

Re: calculate sum of multiple fields in different lines identified using regex

sundareshr — Fri, 05 Aug 2016 12:31:25 GMT

Try this

index="xxxx" sourcetype="web_stats" *car OR *truck | rex "(?<make>\w+)(?<type>car|truck)=(?<cnt>\d+)" | timechart span=1d sum(cnt) as total by type

*OR*

index="xxxx" sourcetype="web_stats" *car OR *truck | rex "(?<make>\w+)(?<type>car|truck)=(?<cnt>\d+)" | chart sum(cnt) as total over type by make

Re: calculate sum of multiple fields in different lines identified using regex

karthik2146 — Fri, 05 Aug 2016 13:29:04 GMT

Thanks for answer but sad that it did not work. The regex only selects truck that too one brand.
When i did
index="xxxx" sourcetype="web_stats" *car OR *truck | rex "(?\w+)(?car|truck)=(?\d+)" | table _time, make, type, cnt

i get only truck records and make as bmw and their respective count. I dont know the problem, you have any idea?

Re: calculate sum of multiple fields in different lines identified using regex

karthik2146 — Fri, 05 Aug 2016 13:31:46 GMT

Thanks for the answer. but the regex selected only truck of a specifc make.

Re: calculate sum of multiple fields in different lines identified using regex

somesoni2 — Fri, 05 Aug 2016 14:41:02 GMT

Give this a try

 index="xxxx" sourcetype="web_stats"  *car OR *truck | rex "car=(?<car>\d+)" | rex "truck=(?<truck>\d+)" | timechart span=1d sum(car) as car sum(truck) as truck

Re: calculate sum of multiple fields in different lines identified using regex

karthik2146 — Mon, 08 Aug 2016 13:10:02 GMT

It worked but it selected only few cars and trucks. I now understood why the regex was not working. Actually my log will be posted once in 15 minutes, each log entry will have multiple lines, like this

bmwcar=10
bmwtruck=5
nissantruck=5
renaultcar=4

after 15 minutes

bmwcar=5
bmwtruck=4
nissantruck=8
renaultcar=3

like this every 15 minutes,

the regex selects only the first car or truck, so it does not give me correct sum. Could you let me know, how the regex can be modified to select all trucks and cars in each log entry

Re: calculate sum of multiple fields in different lines identified using regex

karthik2146 — Mon, 08 Aug 2016 13:10:13 GMT

The regex selected only few cars and trucks. I now understood why the regex was not working. Actually my log will be posted once in 15 minutes, each log entry will have multiple lines, like this

bmwcar=10
bmwtruck=5
nissantruck=5
renaultcar=4

after 15 minutes

bmwcar=5
bmwtruck=4
nissantruck=8
renaultcar=3

like this every 15 minutes,

the regex selects only the first car or truck, so it does not give me correct sum. Could you let me know, how the regex can be modified to select all trucks and cars in each log entry?

Re: calculate sum of multiple fields in different lines identified using regex

sundareshr — Mon, 08 Aug 2016 14:02:51 GMT

Try this

index="xxxx" sourcetype="web_stats" *car OR *truck | rex field=x max_match=0 "(?<make>\w+)(?<type>car|truck)=(?<cnt>\d+)" | eval z=mvzip(make, mvzip(type, cnt)) | mvexpand z | rex field=z "(?<make>\w+),(?<type>\w+),(?<cnt>\d+)" | stats sum(cnt) as total by type

Re: calculate sum of multiple fields in different lines identified using regex

somesoni2 — Mon, 08 Aug 2016 14:08:18 GMT

Just add max_match=0 to both the rex command. Like this, and that should take care of that.

 index="xxxx" sourcetype="web_stats"  *car OR *truck | rex max_match=0 "car=(?<car>\d+)" | rex max_match=0 "truck=(?<truck>\d+)" | timechart span=1d sum(car) as car sum(truck) as truck