How to Find the Difference between Opened Date of Tickets and Closed Date of Tickets of any Incident Using SPLUNK
Lets says we are getting the tickets details which are logged in the SPLUNK. We want to find the difference between opened date and closed date of the tickets for each of the incidents.
We can easily find the difference of dates by the following query.
QUERY
index=”nissan” sourcetype=”csv”
| table Opened,Closed,Number,Severity
| eval EpochOpened=strptime(Opened,”%m/%d/%Y %H:%M”), EpochClosed=strptime(Closed,”%m/%d/%Y %H:%M”)
| eval Diff=EpochClosed-EpochOpened
| fields – Epoch*
| eval Diff_D=round((Diff/(60*60*24)),2)
| fields – Diff
| where Diff_D > 5
| sort – Diff_D
Result:
Explanation:
Here in the above query “nissan” is the index name and “csv” is the sourcetype.“Opened”,”Closed”,”Number” and “Severity” are the existing field names in this index.
By the “table” command we have taken the above 4 fields where it is showing the details of the tickets.Then we have used “strptime” function with the “eval” command to convert the date format fields “Opened” and “Closed” into epochtime . After that we have taken the difference between the two epochtime fields in “Diff” field.In the “Diff_D” field we have converted the “Diff” field values into days and taken upto 2 decimal point by the “round” function.By the “where” command we have taken those data whose difference of Opened date and Closed date is above 5 days.By the “sort” command we have sorted the “Diff_D” field in descending order.
Hope this has helped you in achieving the below requirement without fail :
How to Find the Difference between Opened Date of Tickets and Closed Date of Tickets of any Incident Using SPLUNK
Happy Splunking !!
