How do I pull only specific status entries from a sourcetype at a given time?

[AShah_2]

I have one source-type with column names srno for a ticket.

Scenario: Ticket status gets updated per it's life cycle/flow (i.e. first open, Assigned, workInProgress, Fixed, closed). For a same ticket, splunk would have a multiple entries in the source-type, as and when it gets updated.

Question: How do I find only tickets that are open at a given time?

Example srno = 1, first it gets opened so status is open....while it moves through it's flow, the status gets changed and it is now in "closed" status
Similarly srno = 2 is in Assigned status
srno = 3 and 4 are in open status.

Now, I want a query that only gives me srno 3 and 4 at this point as all others were opened before but now in different status so they should not appear in my result. ( In RDBMS world it is easy as there is only one record at a given time and we can have a where clause srno=open, however, splunk source type would have all the entries whenever a ticket gets updated)

My current approach:
At this point , I first run a query for status!=open (without dedup srno) and download to excel and run a query for status=open (without dedup srno) and download to excel. Grab results of both downloads and use vlookup in excel to get my desired result.

does anyone think of any better solution in one query?

[pradeepkumarg · Accepted solution]

index=your_index | table srno status | dedup srno | search status="open"

dedup will give you the latest status of the tickets on which you filter for status that is open

[somesoni2]

An alternative (almost same approach) will be

index=yourindex sourcetype=yoursourcetype | stats latest(status) as status by srno | where status="open"

[AShah_2]

this is how I made it worked.
index=yourindex sourcetype=yoursourcetype | stats latest(status) as sts by srno | search sts!=Closed (I wanted all but closed)

[pradeepkumarg]

Glad it helped.

[AShah_2]

Hi somesoni2, it helped me fixing my query. Thank you. Everything is right except I got my result by search vs. where you had "where".