topic Re: How do I pull only specific status entries from a sourcetype at a given time? in Splunk Search

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

AShah_2 — Tue, 04 Apr 2017 14:18:36 GMT

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?

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

pradeepkumarg — Tue, 04 Apr 2017 15:55:33 GMT

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

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

somesoni2 — Tue, 04 Apr 2017 16:14:30 GMT

An alternative (almost same approach) will be

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

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

AShah_2 — Tue, 04 Apr 2017 16:43:35 GMT

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".

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

AShah_2 — Tue, 04 Apr 2017 16:46:37 GMT

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)

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

pradeepkumarg — Tue, 04 Apr 2017 16:55:01 GMT

Glad it helped.