How to edit my search to create a table for failed authentications?

[aanic]

Hy,

i have problem with creating table for failed authentication. This is my search..

index=windows_ad source="wineventlog:security" earliest=-24h@h latest=now ("EventCode=675" OR ("EventCode=672" AND Type="Failure Audit")) OR (EventCode=4771 AND "Audit Failure") NOT (User_Name="*$" OR Account_Name="*$") NOT Failure_Code=0x19 | eval "User Account"=coalesce(User_Name,Account_Name) | stats count by "User Account" | where count > 100 | table "User Account" "Client IP" "Client Port" "Failure Code" count | sort - count

Field User Account and count gives me entry in table but fields Client Ip, Client port and Failure code does not.

I tried few times with eval command for those three fields but its all the same.

Can you please help me with that, here is some of fields that can be used for this table.

Thank you.

[DalJeanis]

1) the stats count by "User Account" command eliminates all fields but "User Account" and count. that's why there's nothing available from the other fields. Use eventstats command instead, and the count will be added to each individual event rather than creating a summary of all events.

2) You are inconsistent in your use of quotes around the EventCode test. If EventCode is an indexed field, get those quotes off so that splunk can use the indexes.

3) Sort defaults to only give 100 results. use sort 0 to retain all records.

4) My preference is to put parenthesis around the whole complex test regarding EventCode. I've done that below, but the only line that actually matters to your problem is changing stats to eventstats.

index=windows_ad source="wineventlog:security" 
earliest=-24h@h latest=now 
(EventCode=675 OR (EventCode=672 AND Type="Failure Audit") OR (EventCode=4771 AND "Audit Failure")) 
NOT (User_Name="*$" OR Account_Name="*$") 
NOT Failure_Code=0x19 
| eval "User Account"=coalesce(User_Name,Account_Name) 
| eventstats count by "User Account" 
| where count > 100 
| table "User Account" "Client IP" "Client Port" "Failure Code" count 
| sort 0 -count "User Account"

[gcusello · Accepted solution]

Hi aanic,
not all of the Windows event fields are enhanced with values, you can check this with a click on a field, in the left top corner there is the percentage of values.
if you want to have all the fields full, you should insert in your search Client_Ip=* Client_port=* Failure_code=* but in this way you have less events.
Bye.
Giuseppe

[aanic]

Hi Cusello, all field that i want to put in table have some values, some of fields have a multiple values.

Now im tryng with this querry but still nothing...

index=windows_ad source="wineventlog:security" earliest=-24h@h latest=now ("EventCode=675" OR ("EventCode=672" AND Type="Failure Audit")) OR (EventCode=4771 AND "Audit Failure") NOT (User_Name="*$" OR Account_Name="*$") NOT Failure_Code=0x19 | eval "User Account"=coalesce(User_Name,Account_Name) | stats count by "User Account" | where count > 100 | table "User Account" src_ip Client_Port Failure_Code count | sort - count

[gcusello]

the problem is different:
if you use a stats command after you can use only the fields of your command, in your example you have only "User Account" and count.
If you want also the other fields, you have to insert in stats command also the other fields with the values option, something like this

index=windows_ad source="wineventlog:security" earliest=-24h@h latest=now ("EventCode=675" OR ("EventCode=672" AND Type="Failure Audit")) OR (EventCode=4771 AND "Audit Failure") NOT (User_Name="*$" OR Account_Name="*$") NOT Failure_Code=0x19 | eval "User Account"=coalesce(User_Name,Account_Name) | stats values(src_ip) AS src_ip values(Client_Port) AS Client_Port values(Failure_Code) AS Failure_Code count by "User Account" | where count > 100 | table "User Account" src_ip Client_Port Failure_Code count | sort - count

In this way the table command is useful only to change the fields order, you could also not use it.
Bye.
Giuseppe

[aanic]

Now it works 🙂

Thank you very much for the support.

Augustin

[skoelpin]

You need to have commas between your fields after the table command

Like this

 index=windows_ad source="wineventlog:security" earliest=-24h@h latest=now ("EventCode=675" OR ("EventCode=672" AND Type="Failure Audit")) OR (EventCode=4771 AND "Audit Failure") NOT (User_Name="*$" OR Account_Name="*$") NOT Failure_Code=0x19 | eval "User Account"=coalesce(User_Name,Account_Name) | stats count by "User Account" | where count > 100 | table "User Account", "Client IP", "Client Port", "Failure Code", count | sort - count

[DalJeanis]

Those commas in a field list are completely optional in splunk. Output would not change.

https://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/Table

[aanic]

Still does not working. It is maybe problem with that i have couple IP address for one account in event logs or maybe i must set eval for that three fields.

[skoelpin]

Where is your ClientIP and Client Port fields? I don't see them in your Interesting fields, are they in Selected fields?

I also see Failure_Code as a field but you called it Failure Code in your table command. You can either rename it then call it in the table command or change it in your table command to Failure_Code

[aanic]

Yes, they are in selected fields. i correct name of field but still nothing.

[skoelpin]

Can you paste your search with corrected fields? Can you also verify that Client IP and Client Port are actually fields?

Fields are case sensitive

[aanic]

This is my search and now it works..

index=windows_ad source="wineventlog:security" earliest=-24h@h latest=now ("EventCode=675" OR ("EventCode=672" AND Type="Failure Audit")) OR (EventCode=4771 AND "Audit Failure") NOT (User_Name="*$" OR Account_Name="*$") NOT Failure_Code=0x19 NOT "10.243.101.50" NOT "10.243.149.70" NOT "10.243.101.16" | eval "User Account"=coalesce(User_Name,Account_Name) | stats values(src_ip) AS src_ip values(Failure_Code) AS Failure_Code count by "User Account" | where count > 100 | table "User Account" src_ip Failure_Code count | rename src_ip AS "Client IP" | rename Failure_Code AS "Failure code" | rename count AS Count | sort - Count