CODEWITHSUNDEEP
splunksplunk-query
IRHM
IRHM

Reputation: 1326

Stats Count Splunk Query

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

Upvotes: 3

Views: 4467

Answers (2)

adb
adb

Reputation: 153

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

enter image description here

Upvotes: 0

Anant Naugai
Anant Naugai

Reputation: 556

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 ]

Upvotes: 0

Related Questions