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

[ksbuchanan]

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?

[ksbuchanan · Accepted solution]

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]

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

[ksbuchanan]

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

[micahkemp]

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.

[micahkemp]

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]

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).

[ppablo]

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]

@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]

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.

[ksbuchanan]

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!!

[micahkemp]

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.