Splunk Search

How to calculate no of opened ticket in past in splunk

himanshu_b_shek
New Member

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 🙂

Tags (1)
0 Karma

himanshu_b_shek
New Member

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 🙂

0 Karma

cvssravan
Path Finder

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

0 Karma

himanshu_b_shek
New Member

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

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...