Splunk Search

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

gjohnson
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

pmdba
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
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...