How to use Regex to extract the fields in the windows application event?

[kiran331]

HI, How to extract the field user, action and src_ip from the below event?

05/31/2017 11:59:52 PM
LogName=Application
SourceName=MSSQLSERVER
EventCode=18456
EventType=0
Type=Information
ComputerName=SQl34
User=NOT_TRANSLATED
Sid=S-1-5-7
SidType=0
TaskCategory=Logon
OpCode=None
RecordNumber=123
Keywords=Audit Failure, Classic
Message=Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 12.13.14.152]

05/31/2017 11:59:44 PM
LogName=Application
SourceName=MSSQLSERVER
EventCode=18454
EventType=0
Type=Information
ComputerName=SQL33
TaskCategory=Logon
OpCode=None
RecordNumber=151021016
Keywords=Audit Success, Classic
Message=Login succeeded for user 'KIRAN'. Connection made using SQL Server authentication. [CLIENT: 13.12.14.133]

I need to extract the field values

action
succeeded
failed

user
KIRAN
NT AUTHORITY\ANONYMOUS LOGON

src_ip
13.12.14.133
12.13.14.152

[Richfez]

kiran331,

If one of the two answers below resolved your issue, could you please mark it Accepted? If they both helped, "Accept" the one that was most useful and upvote the other one!

If it did not, please post back with more information or what's not working right so we can help finish this up!

Happy Splunking,
Rich

[Richfez · Accepted solution]

If you have a field named "Message" that contains "Login blah for user blah ...." then:

Your Search Here...
| rex field=Message "^Login (?<action>\w+) for user '(?<user>[^']+).*CLIENT:\s+(?<src_ip>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})]"

If on the other hand you do NOT have a field "Message", then ...

Your Search Here...
| rex "Login (?<action>\w+) for user '(?<user>[^']+).*CLIENT:\s+(?<src_ip>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})]"

Happy Splunking!
-Rich

[niketn]

Try to test the following Regular Expression(this is based on existing data):

rex field=Message "Login (?<action>\w+)\sfor user '(?<User>[^']+)'.\s.*\[CLIENT: (?<IP>[^\]]+)\]"

If you want to come up have more accurate regular expression, you should try Interactive Field Extraction(IFX) with more data sample. Also this will allow you to persist your regular expression as field. Refer to documentation for details: https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/ExtractfieldsinteractivelywithIFX