Splunk Search

How to get the correct count records instead of counting the field name twice?

ksbuchanan
Explorer

Here is my search:

source="WinEventLog:Security" EventCode="4723" OR EventCode="529" | eval UserName=coalesce(User_Name,Account_Name) | stats count by UserName |sort -count |head 10

The problem is that the field "Account_Name" appears more than once in the record, so the count is effectively doubled. How can I get the correct count of the records instead of counting the field name twice?

0 Karma
1 Solution

ksbuchanan
Explorer

i found a solution. i am doing a distinct count on the record number, and that returns the correct number of records

host="rh-dc*" EventCode=4723
| stats dc(RecordNumber) by Account_Name
| sort -count

View solution in original post

ksbuchanan
Explorer

i found a solution. i am doing a distinct count on the record number, and that returns the correct number of records

host="rh-dc*" EventCode=4723
| stats dc(RecordNumber) by Account_Name
| sort -count

richgalloway
SplunkTrust
SplunkTrust

@ksbuchanan If your problem is resolved, please accept an answer to help future readers.

---
If this reply helps you, Karma would be appreciated.

ksbuchanan
Explorer

i found a solution i am doing a distinct count on the record number, and that returns the correct number of records

host="rh-dc*" EventCode=4723
| stats dc(RecordNumber) by Account_Name
| sort -count

0 Karma

micahkemp
Champion

Excellent. You my consider converting your comment to an answer and accepting it so that this question no longer appears open and others have an easier time finding the correct answer.

0 Karma

micahkemp
Champion

In your coalesce you can use mvindex to grab just the first occurrence of Account_Name:

| eval UserName=coalesce(User_Name,mvindex(Account_Name, 0))

ksbuchanan
Explorer

I downvoted this post because this solution didn't work. i tried it and it gave the same answer (which was 2x the number of actual records).

0 Karma

ppablo
Retired

Hi @ksbuchanan

Glad you found a solution to your question 🙂 Just fyi for this Splunk community forum, please reserve downvotes for suggestions that could be harmful in a Splunk environment or is against known best practices. If you have feedback on someone's post, just commenting on it will be more constructive instead of making people lose karma points who was just trying to help out. For the solution(s) that do work, upvote those so they make it up to the top of the list for visibility.

Thanks!

Richfez
SplunkTrust
SplunkTrust

@ksbuchanon,

Thank you for participating in Answers! Having users that ask questions and help answer them are what makes our community so great!

We here in Answers often deal with very complex, vague questions over data and problems that aren't well defined. These often require multiple layers of comments and attempts to find a "best" answer for the questioner's problem. We really think this is one of the things that sets this community apart from others - our inclusion of all people who are trying to help and the effort we will often put toward finding you the answer you need.

So, In order to encourage as much participation as possible we prefer to:
Accept the "most right" answer if it solved your problem,
Liberally upvote comments or other answers that were helpful.

In that same spirit, we reserve downvoting an answer for only if it's possibly harmful to a users system, and not if it just didn't quite answer your problem as well as it could have.

Many thanks, and Happy Splunking!
-Rich

ksbuchanan
Explorer

micahkemp: I tried you solution, but the resulting count is 6, but the number of records is 3.

The Windows event log uses the Account_Name field twice in the record, and the search statement is counting each one of the field occurrences. So, all the counts are doubled.

0 Karma

ksbuchanan
Explorer

host="rh-dc*" EventCode=4723
| eval UserName=coalesce(User_Name,mvindex(Account_Name, 0))
| stats count(Account_Name) by Account_Name
| sort -count(Account_Name)

This yields this result:
Account_Name count(Account_Name)
KINKI 12
ROSCO 8
ADAAN 4

The problem, is that there are only 3 actual records for the Account_Name: KINKI

why is it multiplying the results? Granted, the record has 2 fields that are called "Account_Name" which contain "KINKI"

I appreciate your help...but I am confused about how to get it to count the "just the first "occurrence" of the "Account_Name" field.

Thank you!!

0 Karma

micahkemp
Champion

count(Account_Name) BY Account_Name will count the number of values seen in the Account_Name field, not the number of events where that Account_Name was seen.

To get the event count per Account_Name, use stats count BY Account_Name‘. Notice the lack of()` in this example.

Also, you caolesced to User_Name, but counted by Account_Name. I can’t imagine this is actually what you wanted.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...