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!

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