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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...