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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...