Splunk Search

Count Stats by Two Fields in One Search

henryt1
Path Finder

So I'm running a search that looks like this:

(host="zakta01.inno-360.com" AND mwv-landscaping.inno-360.com AND "GET \/search" AND query=*) OR (host="web01.inno-360.com" AND source="/var/log/apache2/mwv_ssl_access.log" AND "/profile-services/talent")

From that comes two fields that I'm interested in getting the stats for: 'query' and 'q'. So if I wanted to just get the stats for one of them i would do:

... | stats count by query

My question is how would I combine them so I can get the stats for both 'query' and 'q' in one search?

Tags (2)

chimell
Motivator

Hi
use appendcols command

(host="zakta01.inno-360.com" AND mwv-landscaping.inno-360.com AND "GET \/search" AND query=*) OR (host="web01.inno-360.com" AND source="/var/log/apache2/mwv_ssl_access.log" AND "/profile-services/talent")| stats count as C1 by query|appendcols[search (host="zakta01.inno-360.com" AND mwv-landscaping.inno-360.com AND "GET \/search" AND query=*) OR (host="web01.inno-360.com" AND source="/var/log/apache2/mwv_ssl_access.log" AND "/profile-services/talent")| stats count as C2  by q]
0 Karma

cyphr0st
Explorer

Couldn't you just do "| chart count by query,q" to get the results for both fields?

0 Karma

kbouchard81
Engager

Hi I know its 4 years later but found this thread while looking for a solution and thought I show how I made work

source=access AND (user != "-") | rename user AS User | append [search source=access AND (access_user != "-") | rename access_user AS User] | stats dc(User) by host

I created one search and renamed the desired field from "user to "User". Then I did a sub-search within the search to rename the other desired field from access_user to USER. Then just stats count by new field name and gave me desired output.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Another option could be like this (without subsearch)

source=access user!="-" | eval User=coalesce(user,access_user) | stats dc(User) by host
0 Karma

kristian_kolb
Ultra Champion

Well perhaps you could do like this, IF query only exists in one type of log and q only exists in the other;

...| rename query AS q | stats count by q

UPDATE:

Well, I managed to do the same/similar thing for WinEventLog:Security and access_combined logs, effectively renaming the windows EventCode to http_status (or if it was the other way around). Thus I had 200, 404 and 500 as values of EventCode, or if it was 4624 etc as http_status codes.

Have you tried looking into FIELD_ALIAS which is more 'permanent' way doing this?

http://docs.splunk.com/Documentation/Splunk/4.3.1/Knowledge/Addaliasestofields

Hope this helps,

Kristian

0 Karma

kristian_kolb
Ultra Champion

see update above. /k

0 Karma

henryt1
Path Finder

You are correct about the query only existing in one type of log and q in the other. However when I try your suggestion it converts query to q and brings back all of those results, but it doesn't bring back the original q.

So for instance if query has 26 results and q has 7, when I rename it like you said and do 'stats count by q' it brings back 26 results still instead of 33.

0 Karma

sdaniels
Splunk Employee
Splunk Employee

You can do something like this...

... | stats count(query), count(q) by host

Not sure exactly what you are after but this will give you the counts for query and q by host in the same table.

henryt1
Path Finder

Correct, it doesn't give me anything. I'm looking for the count of both together.

0 Karma

sdaniels
Splunk Employee
Splunk Employee

Are you looking for count of how many of query and q you see in your result set? ... | stats count by query q - does not give you anything?

0 Karma

henryt1
Path Finder

For some reason it doesn't seem to be working. When I try that all I get is 'no results found'.

0 Karma

henryt1
Path Finder

Any other ideas?

0 Karma

sdaniels
Splunk Employee
Splunk Employee

"...| stats count by query, q " will give you the count based on both fields.

henryt1
Path Finder

So that's just counting the events by host. I needed the stats counted by 'query' and 'q' together. So instead of having to do:

... | stats count by query

and also:

... | stats count by q

I would just like to be able to do them in the same search. Any ideas?

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