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
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/Audit#sap.com/com.sap.security.core.admin#com.sap.security.core.util.SecurityAudit#Guest#0##n/a##f88fd67152dd11e4cb5a0000002e2bba#SAPEngine_Application_Thread[impl:3]_6##0#0#Warning#1#com.sap.security.core.util.SecurityAudit#Plain###Guest | 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>
Our new report view was having few parameters specific to our needs as below
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.
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.
My directory structure looks like below :
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 snippet 2 :
We specified an output file where our actual processed report will be saved.
We used Apache POI APIs to generate our Report.xlsx file.
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 snippet 4
Once I get the files I am using below code to fetch those files line by Line.
Once I get a log record which contain LOGIN.ERROR I fetch all required information from that log record and process them.
Since timestamp in our case was in UNIX format terefore we formatted it as per our requirements.
Code snippet 5
Once all information is processed I inserted that in excel using Apache POI APIs.
and finally excel file is generated
The generated Excel file looks like below :