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?
coalesce you can use
mvindex to grab just the first occurrence of
| eval UserName=coalesce(User_Name,mvindex(Account_Name, 0))
| eval UserName=coalesce(UserName,mvindex(AccountName, 0))
| stats count(AccountName) by AccountName
| sort -count(Account_Name)
This yields this result:
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.
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
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.
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.
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).
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!
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.
i found a solution i am doing a distinct count on the record number, and that returns the correct number of records
| stats dc(RecordNumber) by Account_Name
| sort -count
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.