Free Staff-side Parser Log

[Matthew]

For one reason or another, emails fail to get parsed into tickets.

As an overworked sysadmin, I don't often get around to trawling the Admin CP Parser Log, searching for 'lost' emails. I'd rather have the helpdesk staff do it themselves, and send me a ticket when they find a log entry that needs closer examination or re-processing.

That's where this Staff-side Parser Log comes in. It's basically a reloaded version of the Admin CP parser log, but with extra date- and status-filtering features that come in handy when searching through a massive parser log.



When a staff member finds an entry that requires my attention, s/he can click the Log ID and his/her email client will generate a new message with an appropriate subject line.

Installation in 4 Steps

I use a well-hacked v3.20.x SupportSuite. This modification is given 'as-is'.

1. Open staff_parserlog.php and modify line 48 (the $supportAddress) to reflect the address you want your staff to be able to send emails to regarding parser log entries. Save file.
2. Drop staff_parserlog.php into /modules/tickets/
3. Merge the relevant parts of the included /modules/tickets/tickets.php into your own /modules/tickets/tickets.php. I DO NOT ADVISE OVERWRITING YOUR OWN FILE. The two sections are clearly marked by big comment blocks.
4. To get a new menu item under the Tickets tab, merge the following into /locale/en-us/en-us.php:

Down around line 400, locate the section beginning with:

PHP Code:

    $_SWIFT["stafflinks"] = array ( 


Add the following to the end of item 2 in the array:

PHP Code:

                7 => array ('Parser Log', 'index.php?_m=tickets&_a=parserlog', true, false), 


You will end up with something like:

PHP Code:

        2 => array (
                0 => array ('Manage Tickets', 'index.php?_m=tickets&_a=manage', false, true),
                1 => array ('Search', '#', true, true, array("ticketsearch", "MENU_TICKETSEARCH")),
                2 => array ('New Ticket', '#', true, true, array("newticket", "MENU_TICKETNEW")),
                3 => array ('Predefined Replies', '#', true, true, array("ticketpred", "MENU_TICKETPRED")),
                4 => array ('Alerts', '#', true, true, array("ticketalerts", "MENU_TICKETALERTS")),
                6 => array ('Filters', '#', true, true, array("ticketfilters", "MENU_TICKETFILTERS")),
                5 => array ('Reports', '#', true, true, array("reportt", "MENU_T_REPORTS")),
                7 => array ('Parser Log', 'index.php?_m=tickets&_a=parserlog', true, false),
               ), 


Known Bug: Staff Department Permissions

I take the premise that staff members should only be able to view successfully parsed emails when the destination department is in the staff members list of assigned departments. (After all, there could be something sensitive in there.) On the other hand, *everybody* should be able to search for failed messages.

I've designed my database queries to do just that, but up to now I haven't got it quite right--nor can I figure out what I've done wrong. Maybe you can help?

In staff_parserlog.php, go to lines 204 and 237 and uncomment each. (Line 237 is an echo statement that will allow you to view the SQL query.) Uncommenting line 204 *should* enable the department filtering described above. When I run the resulting query in phpMyAdmin, a nice result set is generated. But in the php all I get is an error telling me I've got an 'empty' sql statement.

[Matthew]

Version 1.10 adds the following features:

• various hyperlinks to log items, to compose email messages to the sender (to request a resend, for example)
• sets the default time span to be 1 day only
• displays the department name in which a ticket was created
• provides an alert to flag emails that appear to be forwarded from other mail systems, and which are getting rejected (this is kind of a dirty hack and might only be relevant to our system, but it won't do yours any harm)

Source files uploaded to original post.

Previously-mentioned department filters still not working. Help appreciated!

[ZystemsK]

$queryInsert .= " WHERE ((parserlogs.logtype LIKE '%success%'".$staffDeptCondition.") OR parserlogs.logtype LIKE '%failure%')";

Shouldn't there be a space ' ' after the '%success%'? I don't have any records, so I can't test this. That might be why the AND statement isn't getting added properly. I do not know since I don't have any logs to test this with.

[Matthew]

Originally Posted by ZystemsK

$queryInsert .= " WHERE ((parserlogs.logtype LIKE '%success%'".$staffDeptCondition.") OR parserlogs.logtype LIKE '%failure%')";

Shouldn't there be a space ' ' after the '%success%'? I don't have any records, so I can't test this. That might be why the AND statement isn't getting added properly. I do not know since I don't have any logs to test this with.

$staffDeptCondition already includes the space.

Thanks for having a look!

[ZystemsK]

I sent some test emails to a ticket to populate my parser log and all I see on the parserlog view (index.php?_m=tickets&_a=parserlog) and all I see is a flat blue line:

Not sure what else to do. I think the SQL statement might be a little screwy.

The mail parser logs all incoming e-mail to help debug any problems. Click any subject line to view the message contents. Failed messages can often be re-processed by an Administrator. Click the Log ID to open a support ticket
Filter Log Entries

[ZystemsK]

I commented out the date stuff:

PHP Code:


/*if (empty($_REQUEST["datefrom"]) && empty($_REQUEST["dateto"])){
        $_prevday = DATENOW; // Same day
        //$_prevday = DATENOW-2629744; // One month
        $_gdatefrom = generateCalendarTimeline($_prevday);
        $_gdateto =generateCalendarTimeline(DATENOW);
} else {
        $_gdatefrom = $_REQUEST["datefrom"];
        $_gdateto = $_REQUEST["dateto"];

}*/ 


Now it shows me an invalid SQL query and some date boxes as well as the start of the log table. Here's the invalid SQL query:

Invalid SQL: SELECT COUNT(*) AS totalitems FROM `swparserlogs` AS parserlogs LEFT JOIN `swemailqueues` AS queues ON parserlogs.emailqueueid = queues.emailqueueid LEFT JOIN `swdepartments` AS D ON D.departmentid = queues.departmentid WHERE ((parserlogs.logtype LIKE '%success%' AND queues.departmentid IN ("3","5")) OR parserlogs.logtype LIKE '%failure%') AND parserlogs.dateline >= AND parserlogs.dateline <= ; (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND parserlogs.dateline <=' at line 2)

Edit: Guess it's because I commented out the date stuff. Won't render with it, I think that generateCalendarTimeline was removed from the latest version, but I don't remember.

[Matthew]

Hmmm.... I'm not sure what to say. Other than the departmental permissions issue I haven't been able to resolve, everything is working fine. Remember that my Kayako version is a well-hacked v3.20, so it's possible things have changed in the newer releases (such as the calendar issue you mentioned).

I've tested the sql queries, though, using Navicat, and it all seems to work as expected. But this still baffles me:

In staff_parserlog.php, go to lines 204 and 237 and uncomment each. (Line 237 is an echo statement that will allow you to view the SQL query.) Uncommenting line 204 *should* enable the department filtering described above. When I run the resulting query on the DB server using Navicat, a nice result set is generated. But via the php all I get is an error telling me I've got an 'empty' sql statement.

Maybe we need to wait for someone else to give this mod a test drive, to see what problems result from the different Kayako versions.

[John Haugeland]

It's probably time to move to 3.50, Matthew. 3.20 has many serious problems.

As far as your screenshot, both of the examples there are being rejected because the emails don't have a target address. 3.20 didn't account for several fields which 3.50 does (it shouldn't have had to, but Outlook sends certain kinds of RFC non-compliant email which we later learned about.)

You should be able to catch those with a catch-all rule in 3.20; if you move to 3.50, it will no longer be an issue.

[Matthew]

Originally Posted by John Haugeland

It's probably time to move to 3.50, Matthew. 3.20 has many serious problems.

As far as your screenshot, both of the examples there are being rejected because the emails don't have a target address. 3.20 didn't account for several fields which 3.50 does (it shouldn't have had to, but Outlook sends certain kinds of RFC non-compliant email which we later learned about.)

You should be able to catch those with a catch-all rule in 3.20; if you move to 3.50, it will no longer be an issue.

It seems like every version of Kayako has one 'serious problem' or another, depending on who we ask at the time. One person's serious problem can be another's minor inconvenience. That aside, I've hacked so much more functionality into 3.20.02 that we're well beyond a simple upgrade. Yet, we seem to be doing mostly ok with what we've got, and the ability to change the code is something I can appreciate. At some point we may extend our expired support subscription and see what can be done about merging some of the new changes. I know you hate to hear that, John, but the lecture won't be worth it.

I did try to collect those emails with a catch-all, but have yet to be successful. I tried for example /\@/ but it caught nothing. Suggestions would be most welcome, though this is off-topic for the thread.

[John Haugeland]

It seems like every version of Kayako has one serious problem or another, if we look hard enough.

There haven't actually been serious problems in SupportSuite for quite some time. Comments like that aside, there actually are serious problems with running a build that's more than a year of bugfixes and security updates out of date, especially given that its legitimate behavior causes defects in the new IE8 engine.

Regular expressions that match everything aren't the same as the tool in the support desk called a "catch-all rule". Also, that rule is not correct - it would only match an email address which was the single character "@".

[Matthew]

Originally Posted by John Haugeland

Also, that rule is not correct - it would only match an email address which was the single character "@".

Yes well... that's what the parser reports as the final destination email. So I am at a loss, and your professional insight into what might be happening--as opposed to merely pointing out my errors--would greatly assist.

[John Haugeland]

Please open a support ticket. The forums are not a support vector.

[Matthew]

Fixed a bug in the Quick Search and expanded the range to include the To and From email addresses (previously the QS only looked in the subject line).

See original post for source files.