How to calculate no of opened ticket in past in splunk

[himanshu_b_shek]

Hi ,

i want to calculate total no . of opened incidents by a user over a time interval in dynamic environment in splunk (assuming the time input via time picker and we have snow data in splunk )

for example i want to calculate total no. of opened incident by users from 15 feb 19 to 20 feb 19 .(obviously some ticket will be in closed , resolved, in progress , new ....etc states)

we have dv_opened_at , dv_closed_at , sys_updated_on, dv_number fields in splunk as below -

dv_number Team_Name dv_state dv_opened_at sys_updated_on
INC0346726 Desktop Computing Updated by Customer 1/21/2019 7:34 2/22/2019 18:45
INC0349402 IAM In Progress 1/23/2019 19:28 2/22/2019 16:57
INC0363170 Desktop Computing On Hold 2/7/2019 20:19 2/22/2019 19:10
INC0368256 Desktop Computing On Hold 2/13/2019 19:53 2/22/2019 18:58
INC0370984 On Hold 2/16/2019 18:46 2/22/2019 18:17
INC0375322 Updated by Customer 2/20/2019 16:13 2/22/2019 17:58
INC0375327 Endpoint Security Updated by Customer 2/20/2019 16:18 2/22/2019 18:48
INC0375361 Desktop Computing In Progress 2/20/2019 17:22 2/22/2019 16:58
INC0376457 In Progress 2/21/2019 11:12 2/22/2019 18:48
INC0376813 Desktop Computing In Progress 2/21/2019 22:33 2/22/2019 18:26
INC0377715 IAM New 2/22/2019 17:24 2/22/2019 17:27
INC0377755 Messaging New 2/22/2019 18:56 2/22/2019 19:14

this log is pulled by splunk in last 4 hours(22 feb), here we can see have we have OLDER incidents also we are getting all those incidents because those got updated in this time interval .

How we can exclude all those incidents ??
Thanks in advance 🙂

[himanshu_b_shek]

Hi Chris,

I developed below query -

euc_team_sm
| dedup dv_number
| table dv_number Team_Name dv_state dv_opened_at dv_closed_at sys_updated_on
| eval dv_opened_at_epoc = strptime(dv_opened_at, "%Y-%m-%d %H:%M:%S")| search (dv_opened_at_epoc >= "$timerange.earliest$" AND dv_opened_at_epoc <= "$timerange.latest$") | stats count by Team_Name

This query gives correct results with some limitations -

Please provide the time input in below time format via "Time Range " picker -
-Date Range (Between)
-Date & Time Range (Between)
When we pass time range in above format splunk pass time in epoc format to search so accordingly i am getting as expected results.

euc_team_sm
| dedup dv_number
| table dv_number Team_Name dv_state dv_opened_at dv_closed_at sys_updated_on
| eval dv_opened_at_epoc = strptime(dv_opened_at, "%Y-%m-%d %H:%M:%S")| search (dv_opened_at_epoc >= "1550815200" AND dv_opened_at_epoc <= "1550988000") | stats count by Team_Name

BUT when we pass time other than above mentioed format then search give WRONG result becuase in this case splunk not passing time in epoc format , out query based on epoc time format .
Sample result -(It will be wrong result)
euc_team_sm
| dedup dv_number
| table dv_number Team_Name dv_state dv_opened_at dv_closed_at sys_updated_on
| eval dv_opened_at_epoc = strptime(dv_opened_at, "%Y-%m-%d %H:%M:%S")| search (dv_opened_at_epoc >= "-24h@h" AND dv_opened_at_epoc <= "now") | stats count by Team_Name

Here if we can generalize the query that can take any time value first convert it in epoc then we can achieve our desired result .

Thank you 🙂

[cvssravan]

Hi Himanshu,

You need to add a filter based on your dv_opened_at field to filter out the events out of your search results.

I have created a small sample for you.

| makeresults
| eval dv_opened_at="1/14/2019 7:34"
| append
[ | makeresults
| eval dv_opened_at="1/15/2019 7:34"]
| eval unix_time=strptime(dv_opened_at, "%m/%d/%Y %H:%M")
| eval search_start_time=strptime("1/15/2019", "%m/%d/%Y")
| where unix_time>search_start_time

You may similarly add search end time as well in the criteria.

Let me know if it answers your question

[himanshu_b_shek]

Hi Chris,

I tried above solution given by you , it is not working as expected.

Here i want to match the results with given time interval to dv_opened_at time stamp in logs .
As there are incident keeps updating its state ....thats why we have dv_updated_ on fields also.

Example: my search timing is last 24 hours, means i want to calculate total no. Of incidents opened by users irrespective of its current state of ticket. Just i want no. Of incidents raised by users.
Time at incidents opened are dv_ opened _ at.

Now i am discussion ing problem case-

Results of my search

INC1- opened at 22feb at 2:12 it is acceptable

But INC2 opened at 12 NOV 2018 which is not required as per our requirements but this incident got modified on 22 feb at 10:06 thats why we are getting these too.
We have multiple incidents which is not required how we can exclude those kind of incidents.
Thanks

[chrisyounger]

The trick with service-now data is to make sure you are only looking at the current version of the record, by its sys_id. This means you should use a search like the below to | stats it.

index=snow | stats latest(dv_opened_at) as dv_opened_at by sys_id | then_do_your_time_comparisons...