Splunk Search

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

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 failuredate. 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=Currentfile
| where STATUS != "DRAFT"
| eval FAILURE
DATE=strptime(FAILUREDATE, "%m/%d/%Y %H:%M")
| eval CLOSED
DATE=strptime(CLOSEDDATE, "%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

Builder

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

sourcetype=Currentfile
| where STATUS != "DRAFT"
| eval FAILURE
DATE=strptime(FAILUREDATE, "%m/%d/%Y %H:%M")
| eval CLOSED
DATE=strptime(CLOSEDDATE, "%m/%d/%Y %H:%M")
| eval show
date=strftime(strptime(CLOSEDDATE,"%Y/%m/%d"),"%m")
| eval STATUS=mvappend("Open","Closed")
| mvexpand STATUS
| eval _time=case(STATUS="Open", FAILURE
DATE, STATUS="Closed", CLOSEDDATE, showdate=X)
| timechart span=1mon count by STATUS

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

0 Karma

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

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

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 "showdate=X", but when I enter a date the search still graphs "Alltime" rather than the specified month in "showdate=X". Actually it's rather odd no matter what value I put into "showdate=x" Splunk returns with "All_time" graphed data.

0 Karma

SplunkTrust
SplunkTrust

time is an integer. The last clause of the case sets _time to "showdate=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

New Member

Good morning Rich,

I'm still getting data graphed over "Alltime". 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 FailureDate.

Thank you for the help.

0 Karma