topic Re: Search data for All Time but only graph a specified time range in Splunk Search

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

kyule — Tue, 29 Sep 2020 16:16:21 GMT

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:

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

mydog8it — Tue, 29 Sep 2020 16:19:50 GMT

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

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

kyule — Tue, 17 Oct 2017 23:58:38 GMT

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.

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

richgalloway — Wed, 18 Oct 2017 12:48:01 GMT

Try this.

... | eval _time=case(STATUS="Open", FAILURE_DATE, STATUS="Closed", CLOSED_DATE, 1==1, show_date=X) | ...

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

kyule — Tue, 29 Sep 2020 16:16:40 GMT

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.

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

richgalloway — Tue, 29 Sep 2020 16:17:20 GMT

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

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

kyule — Tue, 29 Sep 2020 16:21:53 GMT

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.