Splunk Search

Modify the result of a query using csv lookup

AdsicSplunk
New Member

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?

0 Karma

p_gurav
Champion

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
0 Karma

AdsicSplunk
New Member

Thank you @p_gaurav!!

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

p_gurav
Champion

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

0 Karma
Get Updates on the Splunk Community!

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...