Splunk Search
Highlighted

How to search for "DateClosed" entries in a relational database ticket system?

New Member

I have a home grown ticket system (relational database). It includes a "DateClosed" field that gets updated (obviously) when the ticket is closed. I am trying to identify how many closed tickets per day and run it as a timechart - how do you query for database entries with an entry in the "DateClosed" per day?

I have been thinking of trying to get the number of tickets where DateClosed is not null per day - not per wday..., but can't seem to get the query right. In other words, how many tickets are closed each day...

One table entry holds all created tickets (and includes the DateClosed field). A separate table holds all activity on the ticket - so the query will only have to deal with TableA (the ticket entity) not TableB (The ticket activity).

Thanks

Tags (3)
0 Karma
Highlighted

Re: How to search for "DateClosed" entries in a relational database ticket system?

Builder

The actual search should probably look something like this for tickets closed in the last week (my syntax is for Oracle):

| dbquery "dbname" "select to_char(DateClosed,'YYYY-MM-DD HH24:MI:SS') DateClosed, TicketID from TableA where DateClosed>trunc(sysdate-7) group by DateClosed" | timechart span=1d sum(TicketID) by DateClosed

This will allow you to timechart the number of tickets closed by any span size you want.

0 Karma