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, an upvote 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, an upvote 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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.