Stats Count Splunk Query

Question

I wonder whether someone can help me please.

I'd made the following post about Splunk query I'm trying to write:

https://answers.splunk.com/answers/724223/in-a-table-powered-by-a-stats-count-search-can-you.html

I received some great help, but despite working on this for a few days now concentrating on using eval if statements, I still have the same issue with the "Successful" and "Unsuccessful" columns showing blank results. So I thought I'd cast the net a little wider and ask please whether someone maybe able to look at this and offer some guidance on how I may get around the problem.

Many thanks and kind regards

Chris

score 0 · Answer 1 · answered Feb 26 '19 at 05:20

I tried exploring your use-case with splunkd-access log and came up with a simple SPL to help you. In this query I am actually joining the output of 2 searches which aggregate the required results (Not concerned about the search performance).

Give it a try. If you've access to _internal index, this will work as is. You should be able to easily modify this to suit your events (eg: replace user with ClientID).

index=_internal source="/opt/splunk/var/log/splunk/splunkd_access.log" 
| stats count as All sum(eval(if(status <= 303,1,0))) as Successful sum(eval(if(status > 303,1,0))) as Unsuccessful by user 
| join user type=left 
    [ search index=_internal source="/opt/splunk/var/log/splunk/splunkd_access.log" 
    | chart count BY user status ]

I updated your search from splunk community answers (should look like this):

w2_wmf(RequestCompleted)`request.detail.Context="*test" 
| dedup eventId 
| rename request.ClientID as ClientID detail.statusCode AS statusCode 
| stats count as All sum(eval(if(statusCode <= 303,1,0))) as Successful sum(eval(if(statusCode > 303,1,0))) as Unsuccessful by ClientID 
| join ClientID type=left 
    [ search w2_wmf(RequestCompleted)`request.detail.Context="*test" 
    | dedup eventId 
    | rename request.ClientID as ClientID detail.statusCode AS statusCode 
    | chart count BY ClientID statusCode ]

Hi @Anant Naugai, thank you for coming back to me. The only hesitation I have about this is the join because I have read that they are quite inefficient and have a cut off of 50,000 records, and I know that my extracts will have more than this, unless I've misunderstood? Many thanks and kind regards — IRHM, Feb 27 '19 at 07:02

adb · Answer 2 · 2019-02-28T07:15:46.587

0

I answered in Splunk

https://answers.splunk.com/answers/724223/in-a-table-powered-by-a-stats-count-search-can-you.html?childToView=729492#answer-729492

but using dummy encoding, it looks like

w2_wmf(RequestCompleted)`request.detail.Context="*test"
  | dedup eventId
  | rename request.ClientId as ClientID, detail.statusCode as Status
  | eval X_{Status}=1
  | stats count as Total sum(X_*) as X_* by ClientID
  | rename X_* as *

Will give you ClientID, count and then a column for each status code found, with a sum of each code in that column.

As I gather you can't get this working, this query should show dummy encoding in action

`index=_internal sourcetype=*access
 | eval X_{status}=1
 | stats count as Total sum(X_*) as X_* by source, user
 | rename X_* as *`

This would give an output of something like

edited Feb 28 '19 at 07:15

answered Feb 27 '19 at 01:38

adb

103
1
12

Hi @adb. Thank you for coming back to me with this. So I've tried the code as you suggested , but unfortunately it doesn't work how I'd like the output to be. The status codes are not shown in any column, and using the second solution you kindly provided in Splunk answers the Successful and Unsuccesful columns are blank. Many thanks and kind regards – IRHM Feb 27 '19 at 06:55
Hi @IHRM, that doesn't make sense. The cause that would give you no columns, and missing successful and unsuccessful is if you do not have the `rename detail.statusCode as Status` correct, then there is no 'Status' field for the subsequent commands. Can you do a simple `stats count by Status`, as this would check if the rename is working, otherwise it will show no results. – adb Feb 27 '19 at 21:12
Hi @adb. I just used the query you posted on here and Splunk answers. Many thanks. – IRHM Feb 28 '19 at 06:54
What if you do w2_wmf(RequestCompleted)`request.detail.Context="*test" | dedup eventId | rename request.ClientId as ClientID, detail.statusCode as Status | stats count as Total by Status and w2_wmf(RequestCompleted)`request.detail.Context="*test" | dedup eventId | rename request.ClientId as ClientID, detail.statusCode as Status | stats count as Total by detail.statusCode – adb Feb 28 '19 at 07:04
If you have access to the internal access logs index, you can see the principle in action using the following query `index=_internal sourcetype=*access | eval X_{status}=1 | stats count as Total sum(X_*) as X_* by source, user | rename X_* as *` – adb Feb 28 '19 at 07:11
Hi @Adb. Unfortunately I don't have access to the logs. When I tried your query I receive the following error: Error in 'stats' command: The argument 'detail.apiContext=*vat' is invalid. Many thanks and kind regards – IRHM Mar 01 '19 at 08:23

Stats Count Splunk Query

2 Answers2