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