Q: How can I find the lists on my server that don't have any recent posts?

By Jacob Haller
Senior Support Engineer, L-Soft

Administrators of LISTSERV sites with a large number of lists often ask about ways to identify inactive lists that are no longer in use so that they can be deleted. This tech tip looks at different ways to use the system- and list-based changelogs to gather information about lists that don't have any recent posts.

Finding Inactive Mailing Lists Using the System Changelog

While there is no built-in report that gives you the exact date and time of the last posting to all of the lists, it's possible to get this information with a little investigation.

This is most easily done if the LISTSERV system changelog feature has been enabled. To confirm, use the LISTSERV web interface, navigate to Server Administration > Site Configuration and search for the SYSTEM_CHANGELOG setting. If it's set to Enabled (1), then you should be good to go. If not, then you may wish to consider enabling it now to allow this sort of report in the future.

When the system changelog is enabled, LISTSERV will start recording any new posts made to mailing lists in a special system.changelog file in listserv\main\ or ~listserv/home/. You can then generate reports on this activity by going under Server Administration > Server Reports > Server Activity Report.

Before doing that, make sure that you are in Expert Mode. You can look in the upper-right corner of the web interface to see what mode you are in and click on it if you need to change the mode.

Once that's taken care of, you can go to the Server Activity Report page and create a History Report for Mail and Mail-Merge activity during the time period you're interested in, be it the past six months, the past year, or any period of time that a list would have to have no new posts in order for you to consider it inactive. For the report format, select History in CSV Format. This will result in the data being exported in a format that you can open in Microsoft Excel, or any similar program. Click on Submit, and your browser will download the file containing the data.

At this point, you can open the file in Microsoft Excel and manipulate the data as desired. What follows is how I would generate a report of this sort, but note that the steps and the location of menu items may be different depending on the version of Microsoft Excel that you're using.

1. Open the CSV file in Microsoft Excel.

2. Depending on the version of LISTSERV that you're using, the file may or may not contain column headings by default, so I would add them for convenience. The columns are, from left to right: Date/Time, Job Type, Local Recipients, Forwarded Recipients, Message Size, Name of Job/List, Job Info, Bounce Address and Subject Line. Don't worry if you don't know what most of these are.

3. Go under the Insert menu to PivotTable. This may be in a different location in different versions of Excel. Select Use a Table or Range in This Workbook, and select all of the data in the CSV file. Under Choose Where to Place the PivotTable, select New Worksheet and click OK.

4. A new window will open with a list of field names at the top. Find the Name of Job/List field and drag it to the section marked Row Labels. Then, find Name of Job/List in the list of field names again, and drag it to the Values area.

5. If you look at the sheet that the pivot table has been inserted into, you should see a list of all of the mailing lists that had activity recorded during the time period that you had designated, along with the number of posts in that time period. If you compare this to the full list of mailing lists on the server, then you can determine which mailing lists didn't have any traffic in that time period – they're the ones that are not in the pivot table.

If the system changelog is enabled but configured to be rotated yearly, monthly, etc., then note that the Server Activity Report only looks at the currently active changelog – so if the changelog rotates yearly, then you will only be able to get a report on activity in the current year. The result is that, if you follow the above procedure on February 28, and the changelog rotated on January 1, then you'll really only be looking at the previous two months of activity, even if you specified a longer period.

If you find yourself in this situation, then your options are either to get the information directly from the system.changelog files in the listserv\main\ directory – the format of the data can be found in Section 19.8 the LISTSERV Site Manager's Manual – or postpone generating the report until such a time as enough data has accumulated in the current changelog to make the report accurate.

Due to this limitation, I normally recommend having changelogs rotate yearly or not at all.

If you need to generate a list of all mailing lists on the server to compare with the ones generated by this report, there are a few ways to do it. Either navigate to List Management > List Reports and choose CSV Format (All) to download another CSV file containing all of the lists or go under Server Administration > LISTSERV Command and issue a lists or show points all command.

Finding Inactive Mailing Lists Using List-Based Changelogs

If you don't have the system changelog enabled and need to generate a report like this, then you need to look at list-based changelogs instead. If your individual mailing lists have list-based changelogs enabled, as described in Section 19.6 of the LISTSERV Site Manager's Manual, then you can get information on POST activity from those.

The easiest way to compare this data across multiple lists is to go to List Management > List Dashboard. The dashboard normally shows a table containing all of the mailing lists that you manage and information about those lists. You can adjust what information is displayed in this table by clicking on Edit Table. If you do so, you can then delete existing columns by clicking on the X at the top of them or add additional columns by selecting the desired bit of information from the Columns menu at the top of the table and clicking Add.

One of the available columns is Log: Post, which examines the current changelog for each list and calculates how many posts there have been in the desired time period and displays the information.

Because generating this report could in theory be resource intensive, depending on the size of the changelogs and the number of mailing lists, the information won't be displayed immediately when you visit the page. Instead, you'll see a column of + signs. Clicking on one of the + signs will cause the numbers to be populated.

Since this report only looks at the current changelogs, just like the Server Activity Reports, if a mailing list has a rotating changelog, you won't be able to get information from past changelogs.

The best way to examine the data is to select List Name, Changelog and Log: Post as the only three columns for the table. Then select a Changelog Period of 365 days and run the report. You can then click on the column headers to sort the table by number of posts. Lists that don't have changelogs enabled will display N/A. Otherwise, you will see the number of posts in the most current changelog within the selected time period.

If a list has 0 posts and a setting of Changelog=Yes or Changelog=Yes,Single, which means that the changelogs never rotate, then you can easily spot lists that haven't had any activity in the past year.

Subscribe to LISTSERV at Work.