Splunk Search

Search data for All Time but only graph a specified time range

kyule
New Member

Hello,

I am charting IT help desk tickets and I need to make a chart showing how many tickets are opened and closed every month. The timestamp for _time is the ticket failure_date. To accurately reflect how many tickets are closed per month I need to search "All_Time" so if a ticket were opened in say December 2016 and then closed in March 2017 it'll be captured in the graph.

Now I can get all the data to graph but I would like to only graph select months if possible. Below is the current search I am using:

sourcetype=Current_file
| where STATUS != "DRAFT"
| eval FAILURE_DATE=strptime(FAILURE_DATE, "%m/%d/%Y %H:%M")
| eval CLOSED_DATE=strptime(CLOSED_DATE, "%m/%d/%Y %H:%M")
| eval STATUS=mvappend("Open","Closed")
| mvexpand STATUS
| eval _time=case(STATUS="Open", FAILURE_DATE, STATUS="Closed", CLOSED_DATE)
| timechart span=1mon count by STATUS

0 Karma

mydog8it
Builder

I think this will work for you, but you will probably want to change something to make the timechart more interesting...

sourcetype=Current_file
| where STATUS != "DRAFT"
| eval FAILURE_DATE=strptime(FAILURE_DATE, "%m/%d/%Y %H:%M")
| eval CLOSED_DATE=strptime(CLOSED_DATE, "%m/%d/%Y %H:%M")
| eval show_date=strftime(strptime(CLOSED_DATE,"%Y/%m/%d"),"%m")
| eval STATUS=mvappend("Open","Closed")
| mvexpand STATUS
| eval _time=case(STATUS="Open", FAILURE_DATE, STATUS="Closed", CLOSED_DATE, show_date=X)
| timechart span=1mon count by STATUS

Replace the "X" in "show_date=X" with the month you wish to display

0 Karma

kyule
New Member

Thank you for the reply Mydog8it, but I am getting the following error when using that:
Error in 'eval' command: The arguments to the 'case' function are invalid.

To clarify when I entered month I used decimals, and then spelled out the month.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try this.

... | eval _time=case(STATUS="Open", FAILURE_DATE, STATUS="Closed", CLOSED_DATE, 1==1, show_date=X) | ...
---
If this reply helps you, Karma would be appreciated.
0 Karma

kyule
New Member

Thank you Rich,

Thank you very much for the suggestion, it does get rid of the error I was having with just using "show_date=X", but when I enter a date the search still graphs "All_time" rather than the specified month in "show_date=X". Actually it's rather odd no matter what value I put into "show_date=x" Splunk returns with "All_time" graphed data.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

_time is an integer. The last clause of the case sets _time to "show_date=October", which is not an integer. Try ... | eval _time=case(STATUS="Open", FAILURE_DATE, STATUS="Closed", CLOSED_DATE, 1==1, show_date) | ....

---
If this reply helps you, Karma would be appreciated.
0 Karma

kyule
New Member

Good morning Rich,

I'm still getting data graphed over "All_time". I think I may try and separate the search into an open and a close and then try to join them or appendcols...and re-index the .csv file to use indexed time as the _time rather than Failure_Date.

Thank you for the help.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...