Skip to Content
Author's profile photo Former Member

Report in Excel for Failed Logon Attempts

Below blog can we divided in two parts. First part is our initial approach where we faced certain challenges as stated below. Second part is the final solution we gave to client which worked perfectly since then.

Recently we got a requirement to generate a report of “Failed Logon Attempts” for our client. This report was requested by client for audit purposes.

The first thing that striked our mind as a solution was to use custom report view in SAP Netweaver Administrator.

So we logged in to http://<domain name>:50000/nwa

Portal Logon Screen.jpg

We navigated to Troubleshooting -> Logs and Traces -> Log Viewer

In the default view we got all the logs.

Now our requirement was to create a custom log view that is specific to our needs i.e. to get Failed Logon Attempts.

We analysed how does typical log of failed logon attempt looks like.

We went to server directory and fetched one log record with Failed logon attempt. The same I am posting below.

#1.5#00199983C074009A0000C86100006FB10005054DFD4E42D9#1413207403938#/System/Security/[impl:3]      | LOGIN.ERROR                | null     |              | Login Method=[default], UserID=[z******3], IP Address=[**.**.**.**], Reason=[Authentication did not succeed.]#

The key in above log for us was the keyword “LOGIN.ERROR

So we created a custom view in NWA Log Viewer utility for us by selecting view->open view-><create new view>

Create new view context menu.jpg

Our new report view was having few parameters specific to our needs as below

Custom log view parameters.jpg

Main parameter was “LOGIN.ERROR” which would filter records for us on “Failed Logon attempts”. This is exactly what client wanted.

Once we run this report it correctly displayed log records for us.

We could either download them in Excel or send a snapshot to client.

Custom report Output.jpg

But things never turned out to be that simple for us as we were facing certain challenges with above report:

A major challenge that we faced was, this report sometimes returned us “No records to Display” for a particular date range. But after couple of days it used to work correctly for the very same date range. This we raised with SAP and they correctly reverted back that we need to upgrade our SP level.

Few other challenges were :

1. We did not had any further control on customization level of report. As client had to interpret the entire string of “message” column to get user id, address and reason text.

| LOGIN.ERROR                | null     |              | Login Method=[default], UserID=[zg****3], IP Address=[**.**.**.**], Reason=[Authentication did not succeed.

2. When we opted to export this report on excel again we never received output, that too might be due to our SP levels.

3. This report used to take long time to execute.

4. These logs gets archived and are transferred to another directory on server, as a result out custom report in NWA would always fetch empty log records for archived date range. Sometimes client requested old log records as well. Though there is a “archive” view in Log viewer, but again it took ages to execute and also fetched empty records for us 🙁 … of-course due to our SP level. 😡

So due to above challenges we started thinking about an alternative approach.

One thing I would like to add here is that we had access to server directory structure where all log files were physically stored.

So I came up with an idea to create a custom Java application that takes these log files as input and produces a Excel report as per our needs as output.

This now brings me to Part 2 of this article – The actual solution.

The process that we followed going forward to generate reports was :

Step 1 : Basis/Security team use to get those log files from server and mail them to me, or share on our corporate directory which I have access to.

Step 2 : I get those log files and place them in my local folder, unZip them if required.

Step 3 : I execute Java program that takes these files as input and generate a excel report for me in only 2-3 seconds !!! 😎

Step 4 : I mail that xlsx file back to Basis/Security team which they shares with client.

The Java Solution

I would only provide code snippets below, and if you have similar requirement, you can ask any Java developer to create a similar class for you as per your specific requirements.

I used Eclipse as development IDE.

Code snippet 1:

In below snippet I specified the root directory which would contain all log files.

Code Snippet 1.jpg

My directory structure looks like below :

Local file structure.jpg

Above code snippet access this directory structure.

In my case I only kept 1 level deep directory structure.

you can use recursion to have multiple nested directories.

We got list of all directories from below code

Code - get list of files.jpg

Code snippet 2 :

We specified an output file where our actual processed report will be saved.

Code Snippet - output filename.jpg

We used Apache POI APIs to generate our Report.xlsx file.

Code Apache POI.jpg

Above code snippet shows that we created a Excel workbook with a sheet “UME Report”.

This sheet will contain a table of five columns “Date, Time, UserId, IP Address, Reason”.

Code snippet 3

In below snipped I have traversed all files one by one in all directories within my root directory.

Code - Traversing the list of files all  directories.jpg

Code snippet 4

Once I get the files I am using below code to fetch those files line by Line.

Traverse file line by line.jpg

Once I get a log record which contain LOGIN.ERROR I fetch all required information from that log record and process them.

Process LOGINERROR log.jpg

Since timestamp in our case was in UNIX format terefore we formatted it as per our requirements.

Process date.jpg

process timestamp.jpg

Code snippet 5

Once all information is processed I inserted that in excel using Apache POI APIs.

insert in apache poi.jpg

and finally excel file is generated

generate excel file.jpg

The generated Excel file looks like below :

excel file.jpg

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Aníbal Alonso Rodríguez-Suárez
      Aníbal Alonso Rodríguez-Suárez

      Great job!

      I have to do something similar, but through an application in sap portal where it shows the number of failed attempts when a user enters. I have used the IUserAccount library, the getFailedLogonAttempts() method, but it always returns the value 0, even if there have been failed accesses. Do you know what happens? How can I get this information?