Splunk Search

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

Path Finder

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.

alt text

Thank you.

0 Karma
1 Solution

Legend

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

View solution in original post

SplunkTrust
SplunkTrust

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"

Legend

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

View solution in original post

Path Finder

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
0 Karma

Legend

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

0 Karma

Path Finder

Now it works 🙂

Thank you very much for the support.

Augustin

0 Karma

SplunkTrust
SplunkTrust

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

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!