Modify the result of a query using csv lookup

[AdsicSplunk]

Before asking the question, here is a brief description of what I have done and doing.
Below query is working fine which returns the results as I need them:-

index="test" | rex "\"(?[^\"]+)\"\s+(?\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})\s(?[^\s]+)\s(?[^\s]+)\s(?\d+)\s(?[^\s]+)\s(?[^\s]+)\s(?[^\s]+)\s(?.+)" | stats values(EndpointURI),count as TotalHits count(eval(ResponseCode!=200)) as ErrorCount max(eval(ResponseTime*1000)) as MaxResponseTime min(eval(ResponseTime*1000)) as MinResponseTime avg(eval(ResponseTime*1000)) as AvgRT by EndpointURI ConsumerIP | eval AvgRT=round(AvgRT,2) | rename AvgRT as AvgResponseTime | lookup ConsumerIPLookup ConsumerIP OUTPUT ConsumerName | lookup EndpointURILookup EndpointURI OUTPUT Provider,ServiceName,Version | streamstats count as "S.No" | fields S.No,Provider,ServiceName,Version,ConsumerName,TotalHits,ErrorCount,MaxResponseTime(ms),MinResponseTime(ms),AvgResponseTime(ms)

Result of the above query:-

1 Provider1 Service1 1.0 Consumer1 3 1 572 220 396.67
2 Provider1 Service1 1.0 Consumer2 3 1 50 25 300.00
3 Provider1 Service1 1.0 Consumer2 7 4 100 50 200.00
4 Provider1 Service1 1.0 Consumer3 14 12 214 106 602.64

EndpointURILookup.csv contents:-

EndpointURI,Provider,ServiceName,Version
/abc/Provider1/Service1,Provider1,Service1,1.0

ConsumerIPLookup.csv Contents:-

ConsumerIP,ConsumerName
10.10.10.1,Consumer1
10.10.10.2,Consumer2
10.10.10.3,Consumer2
10.10.10.4,Consumer3

What i need to know how can I modify/update the result of my query so that it returns the sum of Totalhits for those ConsumerNames who have multiple ConsumerIPs. For example, in ConsumerIPLookup.csv, Consumer 2 has multiple IP and Consumer Name is "Consumer2", I need to sum so that one record should come calculating TotalHits,ErrorCount,MaxResponseTime,,MinReponseTime,AvgResponseTime.

The result should be like below:-

1 Provider1 Service1 1.0 Consumer1 3 1 572 220 396.67
2 Provider1 Service1 1.0 Consumer2 10 5 75 36 250.00
4 Provider1 Service1 1.0 Consumer3 14 12 214 106 602.64

How can i achieve this without losing any other stats? Is there any method to save csv lookup contents in a variable and use that variable to put condition and calculate the stats from my actual log?

[p_gurav]

After | fields S.No,Provider,ServiceName,Version,ConsumerName,TotalHits,ErrorCount,MaxResponseTime(ms),MinResponseTime(ms),AvgResponseTime(ms)

Can you try adding:

| stast values(S.No) values(Provider) values(ServiceName) values(Version) sum(TotalHits) sum(ErrorCount) sum(MaxResponseTime(ms)) sum(MinResponseTime(ms)) sum(AvgResponseTime(ms)) by ConsumerName

[AdsicSplunk]

Thank you @p_gaurav!!

I tried this by modifying the above query and it worked for me. This was very helpful. 🙂

[niketn]

@AdsicSplunk, please accept the answer by @p_gurav to mark this question as answered.

[p_gurav]

Happy to help!! I converted this to answer, you can accept it if it helped. 🙂