Interstell, Inc.

Security, Application Security, and Software Research

Recent Activity Query

From time to time, I want to see a snapshot of Project Open activity. Specifically, I wanted to see ticket creation and forum posting activity. So, I created a report with this query:

SELECT 'Forum' AS thetype, a.posting_date AS datecreated, a.subject, a.message, b.first_names, b.last_name
FROM im_forum_topics a, persons b
WHERE b.person_id = a.owner_id
AND DATE_PART('day', NOW() - a.posting_date) < 30
SELECT 'Ticket' AS thetype, a.ticket_creation_date AS datecreated, c.project_name AS subject, a.ticket_description AS message, b.first_names, b.last_name
FROM im_tickets a, persons b, im_projects c
WHERE b.person_id = a.ticket_customer_contacT_id
AND c.project_id = a.ticket_id
AND DATE_PART('day', NOW() - a.ticket_creation_date) < 30
ORDER BY datecreated DESC

No doubt I’ll tweak this as I find issues. I’ll probably use the techniques listed in this article to make the individual items “clickable.” But for now, I thought this might be help.

My goal is to post useful queries to both illustrate just how good a job the developers did in designing the Project Open schema and to help folks like us get the most use out of Project Open.

Categories: Project Open 3.5.x