Look-ahead queries

[Branden]

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 USER01@dhcp-130-774-140-240.xyz.com

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 USER02@dhcp985-574.xyz.com.
 PSAPPSRV.2924 (6) [08/07/12 14:08:25 GetCertificate](3) Error Setting App Server context to user USER02@dhcp985-574.xyz.com: authentication failed.
 PSAPPSRV.2924 (6) [08/07/12 14:08:25 GetCertificate](1) (NET.502): USER02@dhcp985-574.xyz.com 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?

Thanks!

[kristian_kolb]

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,

Kristian

[sdaniels]

I you have a field for the user (USER02@dhcp985-574.xyz.com) 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.

[sdaniels]

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

[kristian_kolb]

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

/k