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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...