Ticket IDs older than 30+ days

[tjryberg]

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 #

[sundareshr]

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

[tjryberg]

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?

[sundareshr]

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

[tjryberg]

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

Doesn't return anything

[sundareshr]

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] 

[tjryberg]

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.

[sundareshr]

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

[tjryberg]

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

[sundareshr]

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

[tjryberg]

No luck. Still zero results returned.

[sundareshr]

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

[tjryberg]

Yup, I did catch that 🙂
No Luck

[sundareshr]

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

[tjryberg]

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

[sundareshr]

Can you share some raw events?