topic Re: Ticket IDs older than 30+ days in Splunk Search

Ticket IDs older than 30+ days

tjryberg — Fri, 29 Jul 2016 13:37:40 GMT

Howdy Folks,
Going through the support forums, I've tried numerous ways to come up with a way to search for Open ticket IDs, older than X (30, 60, 90, etc.) days. While playing around with my query, I either get all the tickets returned, or none of them.
I'm basing the "day" mark from the time the ticket was submitted (submit_dttm).

index=[index name] source="dbmon-tail://MetricsITSR/ITSRC_METRICS" | dedup request_id | search assignee_support_group_name="USA.NSS.X2AIX" assignee="*" status_desc="In Progress" OR status_desc="Assigned" | eval submitdate=strptime(submit_dttm,"%m/%d/%y") |where now()<relative_time(submidate, "+30d") | table assignee submitdate request_id

My expectation of the above, is a table with the person's name, the date the ticket was submitted, and the ticket #

Re: Ticket IDs older than 30+ days

sundareshr — Fri, 29 Jul 2016 15:52:39 GMT

Try this

UPDATED TO FIX TYPO

index=[index name] source="dbmon-tail://MetricsITSR/ITSRC_METRICS" | dedup request_id | search assignee_support_group_name="USA.NSS.X2AIX" assignee="*" status_desc="In Progress" OR status_desc="Assigned" | eval submitdate=strptime(submit_dttm,"%m/%d/%y") |where submitdate<relative_time(now(), "-30d") | table assignee submitdate request_id

Re: Ticket IDs older than 30+ days

tjryberg — Fri, 29 Jul 2016 16:11:51 GMT

No luck. Still zero results returned.

Re: Ticket IDs older than 30+ days

sundareshr — Fri, 29 Jul 2016 17:38:01 GMT

I had misspelled submitdate, did you catch that? If not, try the updated version.

Re: Ticket IDs older than 30+ days

tjryberg — Fri, 29 Jul 2016 18:34:12 GMT

Yup, I did catch that 🙂
No Luck

Re: Ticket IDs older than 30+ days

sundareshr — Fri, 29 Jul 2016 18:45:32 GMT

And the format for submitdate is correct? If it is, the eval should return earlier than (-30d) data

... | eval submitdate=strptime(submit_dttm,"%m/%d/%y") | where submitdate<relative_time(now(), "-30d") | ...

Re: Ticket IDs older than 30+ days

tjryberg — Mon, 01 Aug 2016 17:13:20 GMT

I played with some things, over the weekend. Still no luck.

Re: Ticket IDs older than 30+ days

sundareshr — Mon, 01 Aug 2016 17:26:30 GMT

Can you share some raw events?

Re: Ticket IDs older than 30+ days

tjryberg — Thu, 18 Aug 2016 19:32:36 GMT

So I'm trying this a different way. This search gets results:

index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS"  | dedup request_id  | search assignee_support_group_name="usa.nss.x2aix*" OR assignee_support_group_name="usa.nss.x2linux*" OR assignee_support_group_name="usa.nss.x2sun" OR assignee_support_group_name="usa.nss.x2Windows" OR assignee_support_group_name="usa.dis.dsaix" OR assignee_support_group_name="usa.dis.dslinux" OR assignee_support_group_name="usa.dis.dssun" OR assignee_support_group_name="usa.dis.dswin" status_desc=Open OR status_desc=Assigned summary="*Decommission*" | chart count as Open-Decoms by assignee_support_group_name

When I add latest=-30d, nothing returns.
I've manually looked and verified that we have tickets out there, older than 30 days.
Any idea why I'm getting nothing back?

Re: Ticket IDs older than 30+ days

sundareshr — Thu, 18 Aug 2016 19:46:55 GMT

latest=-30d will apply on the _time field. Do you have data with _time earlier than 30d?

See if this works...index=_internal latest=-30d | head 1

Re: Ticket IDs older than 30+ days

tjryberg — Thu, 18 Aug 2016 19:51:36 GMT

..... summary="*Decommission*" index=_internal latest=-30d | head 1 | chart count as Open-Decoms by assignee_support_group_name

Doesn't return anything

Re: Ticket IDs older than 30+ days

sundareshr — Thu, 18 Aug 2016 19:58:14 GMT

The sample query I gave was to run by itself. Or you could try this to see if it returns any results

index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS" latest=-30d

Run this by itself to see if it returns data. If it doesn't, it means you have no events with _time earlier than 30d. In that case, you could do something like this

index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS" latest=[search index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS" | eval search=(strptime(submit_dttm,"%m/%d/%y")-(86400*30)) | fields search]

Re: Ticket IDs older than 30+ days

tjryberg — Thu, 18 Aug 2016 20:09:11 GMT

I did run index=_internal latest=-30d | head 1 by itself. I should've mentioned that.

index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS" latest=-30d Returned 300,000+ events, so yes... stuff is there 🙂

index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS" latest=[search index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS" | eval search=(strptime(submit_dttm,"%m/%d/%y")-(86400*30)) | fields search]

This returned the error:

Error in 'search' command: Unable to parse the search: Comparator '=' has an invalid term on the right hand side.

I tried playing around with it, but the error persists.

Re: Ticket IDs older than 30+ days

sundareshr — Thu, 18 Aug 2016 20:18:31 GMT

Yeah progress 🙂 does this return any data?

index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS" (arch assignee_support_group_name="usa.nss.x2aix*" OR assignee_support_group_name="usa.nss.x2linux*" OR assignee_support_group_name="usa.nss.x2sun" OR assignee_support_group_name="usa.nss.x2Windows" OR assignee_support_group_name="usa.dis.dsaix" OR assignee_support_group_name="usa.dis.dslinux" OR assignee_support_group_name="usa.dis.dssun" OR assignee_support_group_name="usa.dis.dswin") AND (status_desc=Open OR status_desc=Assigned) AND (summary="*Decommission*") | dedup request_id

If this doesn't try one criteria at a time to see which one breaks it

Re: Ticket IDs older than 30+ days

tjryberg — Thu, 18 Aug 2016 20:21:35 GMT

The above returned no results. Removed "arch" and got back 584.

Re: Ticket IDs older than 30+ days

sundareshr — Thu, 18 Aug 2016 20:27:41 GMT

arch was a typo. Shouldn't be there. Is 584 right number? If yes, you can add the final chart command

 index=gioanalytics_na source="dbmon-tail://MetricsITSR/ITSRC_METRICS" (assignee_support_group_name="usa.nss.x2aix*" OR assignee_support_group_name="usa.nss.x2linux*" OR assignee_support_group_name="usa.nss.x2sun" OR assignee_support_group_name="usa.nss.x2Windows" OR assignee_support_group_name="usa.dis.dsaix" OR assignee_support_group_name="usa.dis.dslinux" OR assignee_support_group_name="usa.dis.dssun" OR assignee_support_group_name="usa.dis.dswin") AND (status_desc=Open OR status_desc=Assigned) AND (summary="*Decommission*") 
| dedup request_id
| stats count as Open-Decoms by assignee_support_group_name