Reporting

Look-ahead queries

Branden
Builder

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!

0 Karma

kristian_kolb
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,

Kristian

0 Karma

sdaniels
Splunk Employee
Splunk Employee

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.

0 Karma

sdaniels
Splunk Employee
Splunk Employee

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

0 Karma

kristian_kolb
Ultra Champion

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

/k

0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...