Splunk DB Connect 1: How to find Open Tickets with no closed date in one dbquery search?

[hartfoml]

I have a database that has a trend table. In the trend table there are two entries for each ticket: One for open date and one for closed date.

I want to find the open tickets that do not have an entry for closed. If I use a join or append, then I am essentially doing the search twice: one to find open tickets and one to find closed tickets, then compare and show only tickets that do not have closed date.

I have almost 2 million tickets on the table. How would I find open events that do not have a corresponding closed event with only one search?

Would it make more sense to do this during the SQL query?

| dbquery "TicketDB" "SELECT "TicketID" FROM Trend WHERE ChangeType NOT like "closed" AND ChangeType like "open" " 

this didn't work

[somesoni2]

Doing it in SQL query (as part dbquery) will require selfjoin. Since, I'm not an expert of that so, I'm not even going there.

Here is how same thing can be achieved in Splunk (please adjust the SQL on dbquery per yours)

| dbquery "TicketDB "SELECT TicketID, ChangeType FROM Trend" | stats dc(ChangeType) as StatusCount values(ChangeType) as ChangeType by TicketID | where StatusCount=1 AND ChangeType="open"