Analyzing the Listener.log

A little bit more than a month ago I spoke at OSWOUG event (and wrote this post about it). In the event, Jared Still talked about free tools for Oracle database. He basically said that there is a lot of stuff out there on the internet, and if you can’t find what you need, then write it and publish. So I couldn’t find what I need, and I wrote it, and now I publish (thanks Jared).

When I checked the databases of one of my customers a while ago, I saw that their listener.log get large pretty quickly, so I wanted to investigate it. What I wanted to do is to analyze the log to see how many connection they have per day and from what application. I checked and couldn’t find such a tool (maybe there is, but I didn’t look hard enough). In any case, I decided to write something. I originally wrote something quick and dirty in bash (which I love), but after I wanted to change it I decided to write my first python code. It took me a few hours (as I had to lean python while writing), but here it is.

A few comments and limitations:

1. As this is my first python code ever (and I’ve never been a real developer), if you know python, the code might seem like it was written by a grade 4 kid. Sorry about that
2. It works for both windows and linux (yay!)
3. It currently supports only the default listener (I use “lsnrctl status” to get the log file, without any listener name, and I have a couple of assumptions about the log name)
4. It supports a non diag dest (the log file is *.log) and diag dest (if the log is alert/log.xml I replace it to trace/listener.log)
5. If it can’t find a listener log line in the output of “lsnrctl status” you’ll get an error and the out put of “lsnrctl status”
6. The script is probably far from being perfect, but it’s nice and easy. If you have any comments or additions, I’ll be happy to apply and publish them

What the script does and output:

1. As I said, the script first find the listener log
2. Then it analyzes the log and asks if you prefer a csv output or text in a table format
3. It prints basic information (first and last dates in the log, and number of connections)
4. The main part is a table of connections where the first column is the date, followed by a column for each application found in the log where the values in the table are the amount of connections. This is either formatted as a table (space padded) or as a csv to load to excel or whatever

Things that I wanted to do but didn’t (for no good reason other than time, I might do that someday):

1. Write the output to a file instead of the screen
2. Count the connection based on source host instead of program
3. Support different aggregation (global, per week, per month…)
4. Support any listener (when the listener name is provided) and/or simply provide a listener log file

[24-Oct-17 update]
I got a few comments about output examples. In fact, the original plan was to add a couple to the post, but for some reason I didn’t. So here are the examples, followed by the script:

C:\Work\Oracle\Scripts>python lsnr.py
Analyzing log: c:\oracle\diag\tnslsnr\liron-laptop\listener\trace\listener.log... done
Please choose from the following output options:
1. csv format
2. text table format
1
Connection Analysis
===================
First date in log: 2014-07-23
Last date in log: 2017-09-12
Number of connections in the log: 131
Connection distribution per day:
================================
Date ,JDBC Thin Client ,SQL Developer ,sqlplus.exe
2014-07-23 , ,6 ,
2014-07-31 , ,5 ,
2015-02-12 , ,3 ,
2015-10-16 , , ,4
2015-11-04 , , ,54
2015-11-06 , , ,16
2015-11-09 , , ,14
2015-11-10 , , ,8
2016-06-17 , ,1 ,
2016-06-24 ,5 , ,
2017-02-17 , ,5 ,
2017-04-28 , ,4 ,
2017-09-05 , ,4 ,
2017-09-10 , ,1 ,
2017-09-12 , ,1 ,

C:\Work\Oracle\Scripts>python lsnr.py
Analyzing log: c:\oracle\diag\tnslsnr\liron-laptop\listener\trace\listener.log... done
Please choose from the following output options:
1. csv format
2. text table format
2
Connection Analysis
===================
First date in log: 2014-07-23
Last date in log: 2017-09-12
Number of connections in the log: 131
Connection distribution per day:
================================
|========================== |========================== |========================== |========================== |
| Date                      | JDBC Thin Client          | SQL Developer             | sqlplus.exe               |
|========================== |========================== |========================== |========================== |
| 2014-07-23                |                           | 6                         |                           |
| 2014-07-31                |                           | 5                         |                           |
| 2015-02-12                |                           | 3                         |                           |
| 2015-10-16                |                           |                           | 4                         |
| 2015-11-04                |                           |                           | 54                        |
| 2015-11-06                |                           |                           | 16                        |
| 2015-11-09                |                           |                           | 14                        |
| 2015-11-10                |                           |                           | 8                         |
| 2016-06-17                |                           | 1                         |                           |
| 2016-06-24                | 5                         |                           |                           |
| 2017-02-17                |                           | 5                         |                           |
| 2017-04-28                |                           | 4                         |                           |
| 2017-09-05                |                           | 4                         |                           |
| 2017-09-10                |                           | 1                         |                           |
| 2017-09-12                |                           | 1                         |                           |
|========================== |========================== |========================== |========================== |

The code is on GitHub, you can find it here.