Splunk Search

Evaluating static field over time with Splunk values?

akocak
Contributor

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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

View solution in original post

akocak
Contributor

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

0 Karma

cmerriman
Super Champion

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 ]

akocak
Contributor

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

0 Karma

cmerriman
Super Champion

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)
0 Karma

akocak
Contributor

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)

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!