Splunk Search

Diff the counts in correlated queries

goalkeeper
Explorer

I am very new to Splunk.
I have two log files, the first one,  let's call it accessLog, contains the access log for the http requests

A splunk query could give me the count for each request.


url                                        count
http://host1/query1      10
http://host1/query2      20

The second log file, let's call it errorLog, contains only error for the request, the line contains a keyword of the url.

A Splunk query could give me the result:

keyword           errorCount
query1              2
query2              8

I want to calculate the success ratio for each request:

URL                                                success ratio
http://host1/query1              80%
http://host2/query2              60%


It could be described as the following sql

Select url,  count(url),   (count(url) - (select (count (keyword) from ErrorLog where url is like '%keyword%'))/count(url) as successRatio
From accessLog
group by url

Could this be done in a Splunk query?

Thanks in advance.

Labels (3)
1 Solution

to4kawa
Ultra Champion

 

 

| makeresults
| rename COMMENT as "First search"
| eval _raw="url    count
http://host1/query1      10
http://host1/query2      20" 
| multikv forceheader=1 
| table url count 
| append 
    [| makeresults
    | rename COMMENT as "Second search" 
    | eval _raw="keyword           errorCount
query1              2
query2              8" 
    | multikv forceheader=1 
    | table keyword errorCount] 
| eval tmp=mvindex(split(url,"/"),-1) 
| eval key=coalesce(keyword,tmp) 
| selfjoin key 
| eval "success ratio"=(1-errorCount/count)*100 
| table url "success ratio"

But

index=yours (sourcetype=AccessLog OR (sourcetype=ErrorLog "keyword"))
and aggregate with stats command
This is Splunk way.

 

 

View solution in original post

to4kawa
Ultra Champion

 

 

| makeresults
| rename COMMENT as "First search"
| eval _raw="url    count
http://host1/query1      10
http://host1/query2      20" 
| multikv forceheader=1 
| table url count 
| append 
    [| makeresults
    | rename COMMENT as "Second search" 
    | eval _raw="keyword           errorCount
query1              2
query2              8" 
    | multikv forceheader=1 
    | table keyword errorCount] 
| eval tmp=mvindex(split(url,"/"),-1) 
| eval key=coalesce(keyword,tmp) 
| selfjoin key 
| eval "success ratio"=(1-errorCount/count)*100 
| table url "success ratio"

But

index=yours (sourcetype=AccessLog OR (sourcetype=ErrorLog "keyword"))
and aggregate with stats command
This is Splunk way.

 

 

goalkeeper
Explorer

Works like a charm. thanks again.

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...