How to edit my current search to list all ITSM tickets where status!=Closed ?

[sivasobh]

Below Is the search I am using which will list the ITSM tickets in Our Group queue, but still some old tickets which are in our queue are not showing in this list. It's only taking latest list ... Can someone give a suggestion?

We use BMC Remedy for Ticketing

eventtype=itsm_incidents | sort-Last_Modified_Date_2| dedup Incident_ID | convert ctime(Reported_Date), ctime(Closed_Date), ctime(Last_Modified_Date_2), ctime(Assignment_Date) | where Status!="Closed" | where Assigned_Group="Web Platform Support L2 - All"|table Incident_ID, Last_Modified_Date_2, Closed_Date, Assigned_Group, Assignee, Product_Categorization_Tier_3, Status

[Navanitha]

Hey Siva,

We have a similar requirement to bring ITSM service tickets data into Splunk. Any light on how to do this or how did you do this would really help.

Thank you in advance.

[lguinn2]

I have some ideas:
1 - What is the timerange of your search? If the last entry for an incident was 90 days ago, but you are only searching the last 60 days, there will be nothing for Splunk to find.
2 - You are de-duping your data based on the Incident_ID, after reverse sorting on the Last_Modified_Date_2 (although it looks like a typo in the sort command, I will assume that it is merely a cut-and-paste error). IF the field "Last_Modified_Date_2" is always accurate and never null, then you will get what you want (the latest event in for the incident). Otherwise, it might be problematic.
3 - Finally, this has nothing to do with your problem, but your search is not as efficient as it could be. I would do this:

eventtype=itsm_incidents  Assigned_Group="Web Platform Support L2 - All"
| sort -Last_Modified_Date_2| dedup Incident_ID 
| where Status!="Closed"
| convert ctime(Reported_Date), ctime(Closed_Date), ctime(Last_Modified_Date_2), ctime(Assignment_Date) 
| table Incident_ID,Last_Modified_Date_2,Closed_Date,Assigned_Group,Assignee, Product_Categorization_Tier_3,Status

[lguinn2]

What is the timerange of your search? Would that timerange include the older tickets?

[ChrisYang]

by the way, how did you index the tickets data from BMC Remedy ITSM? thanks.