Look-ahead queries


I need to write a report that will display all successful logins from a Peoplesoft application. It sounds simple, but there's a complication...

Here's what a successful login may look like:

 PSAPPSRV.8998 (91) [08/07/12 11:02:43 GetCertificate](3) Sign on PeopleCode authentication succeeded for user

Here's what a failed login may look like:

 PSAPPSRV.2924 (6) [08/07/12 14:08:25 GetCertificate](3) Sign on PeopleCode authentication succeeded for user
 PSAPPSRV.2924 (6) [08/07/12 14:08:25 GetCertificate](3) Error Setting App Server context to user authentication failed.
 PSAPPSRV.2924 (6) [08/07/12 14:08:25 GetCertificate](1) (NET.502): is an Invalid User ID, or  you typed the wrong password.  User ID and Password are required and case-sensitive.  Make sure you're typing in the correct upper and lower case.  

Notice that even a failed login shows "Sign on PeopleCode authentication succeeded for user ...." just like a successful login. (This is because the user's LDAP credentials succeed, but may not have permission to sign into the app.)

So in order to accurately report on successful logins, I not only need to search for the "Sign on PeopleCode authentication succeeded for user @", but I need to "look-ahead" to another event and see if there's a failure associated with it. The good news is that the PSAPPSRV PID should match up to the transaction. So for the field PSAPPSRV.XYZ, "XYZ" will be the same for the entire login transaction (if that helps).

Is there a way to do this? Does what I'm asking make sense?


0 Karma

Ultra Champion

Yes, this can be achieved with the transaction command, which groups events into transaction, based on - in your case - the id-number that comes after the PSAPPSRV.

Something like this would probably work.

sourcetype=your_log_file_type authentication 
| rex "PSAPPSRV.(?<PS_ID>\d+) 
| transaction max_events=2 max_span=1m PS_ID 
| search eventcount=1

The first row specifies your sourcetype and the fact that you want to find events with the string 'authentication' (NB: this will only return two events for failed logins, but that is OK.)

The second lets you extract the ID number into a field called PS_ID

The third creates transactions based on the ID, with restrictions on how many events make up a transaction, and how far apart they can be in time (in case ID's are reused, and also speeds up performance)

Finally find the transactions that only contain one event (the 'true' authentication succeeded)

For more info, please see the documentation for transaction

Hope this helps,


0 Karma

Splunk Employee
Splunk Employee

I you have a field for the user ( and can pipe that in the transaction command:

... | transaction User maxspan=5s eventcount=1

You could use the linecount or eventcount of the transaction to check and make sure it equals 1. Sounds like the easiest way to do it if you have a unique ID to use.

0 Karma

Splunk Employee
Splunk Employee

yes typo, should be search for linecount/eventcount = 1

0 Karma

Ultra Champion

Hmm, this will make strange transactions indeed. Each event will be a separate transaction.


0 Karma
Get Updates on the Splunk Community!

Splunk Security Content for Threat Detection & Response, Q1 Roundup

Join Principal Threat Researcher, Michael Haag, as he walks through:An introduction to the Splunk Threat ...

Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! &#x1f308; In the United States, as well as many countries around the ...

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...