Cant build a table with command "stats count"

[Miloš]

Hello!

we would like to extend our alarm for our users' monthly failed logon. I have created the following script. There is a problem with the table. The table is not showing me the "Workstation" and "Source_Network_Address", the Affected and the Count are working fine.

I did some troubleshooting and found out that the command line with "stats count as" is the reason, as it works without that and shows everything except Count then of course.

Does anyone have an idea how I can create a table and a counter?

index=*.....
(Account_Name="*" OR Group_Name="*")
EventCode="4625"
NOT EventCode IN ("4735", "4737", "4755")
NOT Account_Name="*$*"
Name

| eval time=_time
| eval Operator=mvindex(Account_Name, 0)
| eval Affected=mvindex(Account_Name, 1)
| eval Group=mvindex(Account_Name, 2)
| eval Workstation=mvindex(Workstation_Name, 0)
| eval Group=if(isnull(Group),Group_Name,Group)
| eval Workstation=if(isnull(Workstation),"",Workstation)
| eval Workstation=nullif(Workstation,"")
| eval Affected=if(isnull(Affected),Account_Name,Affected)
| eval ExpirationTime=if(isnull(Expiration_time),"",Expiration_time)
| rex field=Message "(?<Message>[^\n]+)"

| stats count as Count by Affected
| table Affected, Workstation, Source_Network_Address, Count
| sort -Count

[Miloš]

Thank you for the fast answer. 

Now i can see all columns, but the Events in the Statistics are separate and not grouped. Now i see for example 50 times one user and then the another one 20 times, and so on.

[richgalloway · Accepted solution]

That's not optimal.  😉

Try this alternative using stats.

| stats count as Count, values(Workstation) as Workstation, values(Source_Network_Address) as Source_Network_Address by Affected
| table Affected, Workstation, Source_Network_Address, Count
| sort - Count

This may yield multiple workstation or source address values for each Affected value.

[Miloš]

Hey, 

thank you for the help. Now we have a solution 🙂

Best regards

[richgalloway]

The stats command is a transforming one, meaning it changes the results so only the referenced fields exist.  In this case, only the Count and Affected fields are available to subsequent commands.  Perhaps the best fix is to use the eventstats command, which is not transforming.

| eventstats count as Count by Affected
| table Affected, Workstation, Source_Network_Address, Count
| sort - Count