topic Re: Count Stats by Two Fields in One Search in Splunk Search

Count Stats by Two Fields in One Search

henryt1 — Mon, 28 Sep 2020 11:53:01 GMT

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?

Re: Count Stats by Two Fields in One Search

sdaniels — Wed, 30 May 2012 14:17:20 GMT

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.

Re: Count Stats by Two Fields in One Search

henryt1 — Wed, 30 May 2012 15:18:16 GMT

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?

Re: Count Stats by Two Fields in One Search

sdaniels — Wed, 30 May 2012 15:32:37 GMT

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

Re: Count Stats by Two Fields in One Search

henryt1 — Fri, 01 Jun 2012 15:55:52 GMT

Any other ideas?

Re: Count Stats by Two Fields in One Search

henryt1 — Fri, 01 Jun 2012 18:28:10 GMT

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

Re: Count Stats by Two Fields in One Search

sdaniels — Fri, 01 Jun 2012 18:31:39 GMT

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?

Re: Count Stats by Two Fields in One Search

henryt1 — Mon, 04 Jun 2012 15:39:48 GMT

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

Re: Count Stats by Two Fields in One Search

kristian_kolb — Mon, 04 Jun 2012 16:05:12 GMT

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

Re: Count Stats by Two Fields in One Search

henryt1 — Mon, 04 Jun 2012 17:21:21 GMT

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.

Re: Count Stats by Two Fields in One Search

kristian_kolb — Mon, 04 Jun 2012 17:53:26 GMT

see update above. /k

Re: Count Stats by Two Fields in One Search

kbouchard81 — Tue, 29 Sep 2020 08:32:46 GMT

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.

Re: Count Stats by Two Fields in One Search

somesoni2 — Mon, 25 Jan 2016 20:08:29 GMT

Another option could be like this (without subsearch)

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

Re: Count Stats by Two Fields in One Search

cyphr0st — Tue, 08 Mar 2016 19:49:39 GMT

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

Re: Count Stats by Two Fields in One Search

chimell — Wed, 09 Mar 2016 09:36:20 GMT

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]