topic Re: Evaluating static field over time with Splunk values? in Splunk Search

Evaluating static field over time with Splunk values?

akocak — Thu, 31 Aug 2017 21:55:42 GMT

Hi Splunkers, I have some data set with Ticket start and end times, I have created

index=x sourcetype=y
| eval opentickets=if(start>relative_time(now(),"@y"),"Opened","") 
| eval closetickets = if(end>relative_time(now(),"@y"),"Closed","") 
| bin _time span=1mon 
| eventstats count(eval(openticketstate="Opened")) as Opened count(eval(closeticketstat="Closed")) as Closed by _time 
| eval diff = Opened-Closed
| timechart values(Closed) as Closed values(Opened) as Opened

Which gives me a nice table of:
_time,Closed,Opened
2017-01,108,1
2017-02,27,7
2017-03, 86,64
2017-04,38,33

Question is I have a static number from last year and I need another column TotalOpenTickets that updates this number along with the timechart. So every month, it needs to get previous months TotalOpenTickets count, add Opened count substitute Closed count. My goal is to get the result set of ( let's say static TotalOpenTickets is 200) similar to:
_time, Closed, Opened, TotalOpenTickets
2017-01 ,108 ,1 ,93
2017-02 ,27 ,7 ,73
2017-03 ,86 ,66 ,53
2017-04 ,38 ,58 ,73

I hope I explained well. Thanks for reading.

Re: Evaluating static field over time with Splunk values?

cmerriman — Thu, 31 Aug 2017 23:53:40 GMT

Is this in another source or is it a field in this source? Is it a lookup? You could do a join _time [dataset|timechart values(TotalOpenTickets) as TotalOpenTickets ]

Re: Evaluating static field over time with Splunk values?

akocak — Fri, 01 Sep 2017 14:09:35 GMT

it is a number that I need to hard-code, not from other data sets. I need to add it like
| eval mnumber= 200,
like it needs to get into dataset by January. and keep updated with the data set as
mnumber = mnumber + opened - closed

Re: Evaluating static field over time with Splunk values?

cmerriman — Fri, 01 Sep 2017 14:29:07 GMT

Oh I see. Try this:

|eval TotalOpenCases=if(_time=1483228800,200-Closed+Opened,null())
|streamstats window=1 current=f  values(TotalOpenCases) as LMopencases
|eval TotalOpenCases=if(isnull(TotalOpenCases),LMopencases-Closed+Opened,TotalOpenCases)

Re: Evaluating static field over time with Splunk values?

akocak — Fri, 01 Sep 2017 16:31:01 GMT

it is not adding anything as below:
|eventstats count(eval(openticketstate="Opened")) as Opened count(eval(closeticketstat="Closed")) as Closed by _time

|eval TotalOpenCases=if(_time=1483228800,200-Closed+Opened,null())
|streamstats window=1 current=f values(TotalOpenCases) as LMopencases
|eval TotalOpenCases=if(isnull(TotalOpenCases),LMopencases-Closed+Opened,TotalOpenCases)

| timechart values(Closed) as Closed values(Opened) as Opened values(TotalOpenCases)

Re: Evaluating static field over time with Splunk values?

somesoni2 — Fri, 01 Sep 2017 16:52:15 GMT

Give this a try

index=x sourcetype=y
 | eval opentickets=if(start>relative_time(now(),"@y"),"Opened","") 
 | eval closetickets = if(end>relative_time(now(),"@y"),"Closed","") 
 | timechart span=1mon count by openticketstate
 | eval diff = Opened-Closed
 | accum diff
 | appendcols [search query to get count from last year, this will get added to row1| table TotalOpenTickets ]
 | eval TotalOpenTickets =TotalOpenTickets + diff | fields - diff

Re: Evaluating static field over time with Splunk values?

akocak — Thu, 07 Sep 2017 14:57:35 GMT

it didn't update the fields with this way, however, it showed me how to accumulate diff and leaded me to solution. Thanks @somesoni2 , here is the answer SPL for reference.
....
| timechart span=1mon count(eval(openedtickets="Opened")) as Opened count(eval(closedtickets="Closed")) as Closed
| eval diff = Opened-Closed | accum diff
| eval TicketFromLastYear=200
| eval TicketFromLastYear = TicketFromLastYear + diff | fields - diff