How to extract only successful and failed logins using regex?

[atyshke1]

Hello All,
I have a file with data:

--------------server1 2018-07-----SQL2008--
Number of Success Logins:
SOFTPOINTPERFOMANCEEXPERTLICENCEUSER - SQL SERVER AUTHENTICATION -  xx.xxx.xxx.xx -                    server01.citytown01.alls.com - 13303433
          FOR0001\Login114 -    WINDOWS AUTHENTICATION -  xx.xxx.xxx.xx -                    server01.citytown01.alls.com - 258857
                  Log_chat - SQL SERVER AUTHENTICATION - xx.xxx.xxx.xxx -                    server01.citytown01.alls.com - 214180
          FOR0001\Login114 -    WINDOWS AUTHENTICATION - xx.xxx.xxx.xxx -                    server01.citytown01.alls.com - 184989
       NT AUTHORITY\SYSTEM -    WINDOWS AUTHENTICATION -  xx.xxx.xxx.xx -                    server01.citytown01.alls.com - 12684
          FOR0001\Login112 -    WINDOWS AUTHENTICATION - xx.xxx.xxx.xxx -                    server01.citytown01.alls.com - 1166
                      1SSA - SQL SERVER AUTHENTICATION -  xx.xxx.xxx.xx -                    server01.citytown01.alls.com - 841
                  Log_chat - SQL SERVER AUTHENTICATION - xx.xxx.xxx.xxx -                    server01.citytown01.alls.com - 271
          FOR0001\Login114 -    WINDOWS AUTHENTICATION - xx.xxx.xxx.xxx -                    server01.citytown01.alls.com - 46
                  SQLLSS01 - SQL SERVER AUTHENTICATION -   xx.xxx.x.xxx -                            xxxxxxx.xxx.xxxx.com - 37
SOFTPOINTPERFOMANCEEXPERTLICENCEUSER - SQL SERVER AUTHENTICATION -            ::1 -                    server01.citytown01.alls.com - 1
Number of Failed Logins:
                  Log_chat -                           - xx.xxx.xxx.xxx -                    server01.citytown01.alls.com - 73
          FOR0001\Login118 -                           - xx.xxx.xxx.xxx -                           xxxxxxx.xxx.xxxx.com - 10
                  Log_chat -                           - xx.xxx.xxx.xxx -                    server01.citytown01.alls.com - 3
SOFTPOINTPERFOMANCEEXPERTLICENCEUSER -                           -  xx.xxx.xxx.xx -                    server01.citytown01.alls.com - 1
------------------------------------------

I need to extract only Success Logins and then Failed Logins.
I tried use rex ^\s+(?\S+) | eval New=Success_Login | stats count by New
But it extracting only the first Login.

[atyshke1]

I found what I need!
Thank you nittala_surya that you gave the right way for exploration 🙂

rex max_match=0 field=_raw "(?:Number\sof\sFailed\sLogins\:)?[\r\n]\s+(?<Failed_login>\S+)\s\-(?!.*AUTHENTICATION)\D+(?<IPs>[A-z0-9]+\.[0-9]+\.[0-9]+\.[0-9]+)\s\-(?!.*AUTHENTICATION)\s+(?<DNS>[A-z0-9]+\.[A-z0-9]+\.[A-z0-9]+\.[A-z0-9]+)\s\-\s(?<Count>\w+)" | rex field=_raw "(?<Servers>\w+)\\s.+[^AUTHENTICATION]" | rename Failed_login as "Failed Login" | table "Failed Login", Servers, IPs, DNS, Count

[atyshke1]

Hi, I tried this one
your_search | rex field=_raw "(?\w+)\Login.+AUTHENTICATION" | rex field=_raw "(?\w+)\Login.+[^AUTHENTICATION]"
But it extraction only one first row with account name

[atyshke1]

It seems works! Thank you very much!
Maybe you can help me with extract amount of event. It 's last digit(s) in each row.
For example:
Log_chat - - xx.xxx.xxx.xxx - server01.citytown01.alls.com - 73

73 is an amount of event

I tried this code but it's incorrect extract:

 rex max_match=0 field=_raw "(?<Count_Login_Failed>\w+)\n+(?!.*AUTHENTICATION)"

[sudosplunk · Accepted solution]

Hello @atyshke1,

For 1st question, extracting Success and Failed logins, try below rex statement in your search:

Please note that, I assumed all Success logins will have AUTHENTICATION key word and Failed logins don't. If this is not the case, then use appropriate key words after Login.+ in rex statement.

your_search | rex field=_raw "(?<Success_logins>\w+)\\Login.+AUTHENTICATION" | rex field=_raw "(?<Failed_logins>\w+)\\Login.+[^AUTHENTICATION]"

Tested the above regex here.

For 2nd question, extracting Account name for windows event=4625, I modified your rex a little. Give it a shot:

your_search | rex field=_raw "Account\sFor\sWhich\sLogon\sFailed:\s+Security\sID:\s(?<SecurityID>.+)\s+Account\sName:\s(?<AccountName>\w+)"

Tested the above regex here.

[atyshke1]

nittala_surya below your post help me! Thank you!

Sorry, I didn't realize that the regex should be adjusted.

Give this a shot:

Failed logins:

rex max_match=0 field=_raw "(?:Number\sof\sFailed\sLogins:)?[\r\n]\s+(?\S+)\s-(?!.*AUTHENTICATION)" | table Failed_login

Success logins:

rex max_match=0 field=_raw "(?:Number\sof\sSuccess\sLogins:)?[\r\n]\s+(?\S+)\s-.*AUTHENTICATION" | table Success_login

[atyshke1]

I tried this one for extract failed logon exact:

rex "Number of Failed Logins:\s+(?P<Failed_Login>\S+)+[^AUTHENTICATION]" | table Failed_Login

But it's extract only first failed logon in log. But I have more then 2 or 3 rows with Failed logons. Help please.

[sudosplunk]

Define max_match=0.

Try this:
rex max_match=0 field=_raw "Number of Failed Logins:\s+(?P\S+)+[^AUTHENTICATION]"
| table Failed_Login"

[atyshke1]

The same 😞
Didn't help
I tried

   rex max_match=0 field=_raw "Number of Failed Logins:\s+(?P<Failed_Login>\S+)+[^AUTHENTICATION]" | table Failed_Login

[sudosplunk]

Sorry, I didn't realize that the regex should be adjusted.

Give this a shot:

Failed logins:

rex max_match=0 field=_raw "(?:Number\sof\sFailed\sLogins\:)?[\r\n]\s+(?<Failed_login>\S+)\s\-(?!.*AUTHENTICATION)" | table Failed_login

Success logins:

rex max_match=0 field=_raw "(?:Number\sof\sSuccess\sLogins\:)?[\r\n]\s+(?<Success_login>\S+)\s\-.*AUTHENTICATION" | table Success_login

[atyshke1]

It seems works! Thank you very much!
Maybe help me with extract amount of event. It 's last digit(s) in each row.
For example:
Log_chat - - xx.xxx.xxx.xxx - server01.citytown01.alls.com - 73

73 is an amount of event

I tried this code but it's incorrect extract:

rex max_match=0 field=_raw "(?<Count_Login_Failed>\w+)\n+(?!.*AUTHENTICATION)"

[soumyasaha25]

can you post some sample events that you are getting, please mask any confidential data also the fields that you want to extract from the sample events.
also, you can consider installing Splunk App for Windows Infrastructure, that has inbuild parsing rules to extract these information.

[atyshke1]

Yeah, I have installed Windows TA
And that App have whitelist.
I am talking about event code 4625. In message below it's pasted.
But in splunk I want create a table with Account Name only.
I am using now that rex:
source="WinEventLog:Sec*" index="wineventlog" host=xxx02055 EventCode=4625 | rex "Account For Which Logon Failed:\s+Security\sID:\s+\S(.......)\s+Account Name:\s+(?\S+)" | eval CDSID=UserName | rename host as Host EventCode as "Event Code" | stats count by CDSID
and it extract:
CDSID count
AGALYSHE 1
EKOSTYUK 2
RKHAMIDU 2
Skuzmina 1
sroschin

But I don't like this S(.......) because in the eventcode maybe more symbols then I pointed in (). How can I set anymore symbols in () where can be before s+Account Name ?

[atyshke1]

ANd I have one thing with event 4625. How can I extract only Account Name with non "_"

LogName=Security
SourceName=Microsoft Windows security auditing.
EventCode=4625
EventType=0
Type=Information
ComputerName=xxx02055.chelny.fo.com
TaskCategory=Logon
OpCode=Info
RecordNumber=38692156
Keywords=Audit Failure
Message=An account failed to log on.

Subject:
Security ID: NULL SID
Account Name: -
Account Domain: -
Logon ID: 0x0

Logon Type: 3

Account For Which Logon Failed:
Security ID: NULL SID
Account Name: RKHAMIDU
Account Domain: FOR00001

Failure Information:
Failure Reason: Unknown user name or bad password.
Status: 0xC000006D
Sub Status: 0xC000006A

Process Information:
Caller Process ID: 0x0
Caller Process Name: -

Network Information:
Workstation Name: xxx02055
Source Network Address: -
Source Port: -

Detailed Authentication Information:
Logon Process: NtLmSsp
Authentication Package: NTLM
Transited Services: -
Package Name (NTLM only): -
Key Length: 0

This event is generated when a logon request fails. It is generated on the computer where access was attempted.

The Subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.

The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network).

The Process Information fields indicate which account and process on the system requested the logon.

The Network Information fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.

The authentication information fields provide detailed information about this specific logon request.
- Transited services indicate which intermediate services have participated in this logon request.
- Package name indicates which sub-protocol was used among the NTLM protocols.
- Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

As log belowI need extract only the second Account Name with Login: RKHAMIDU
I tried for below log this rex (?ms)Account Name:\s+\S but it delte first symbol of login 😞
And I see in a spunk login as KHAMIDU instead RKHAMIDU

[Shan]

@atyshke1
Can you please provide. what is the value need to be extracted from the above sample data ..

[atyshke1]

I need extract all Logins:
FOR0001\Login114
Log_chat
NT AUTHORITY\SYSTEM
FOR0001\Login112
1SSA
SQLLSS01
FOR0001\Login118