Count Stats by Two Fields in One Search

[henryt1]

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?

[chimell]

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]

[cyphr0st]

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

[kbouchard81]

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.

[somesoni2]

Another option could be like this (without subsearch)

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

[kristian_kolb]

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

[kristian_kolb]

see update above. /k

[henryt1]

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.

[sdaniels]

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]

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

[sdaniels]

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?

[henryt1]

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

[henryt1]

Any other ideas?

[sdaniels]

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

[henryt1]

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?