Splunk Search

Evaluating static field over time with Splunk values?

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

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

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

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

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 ]

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

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

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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!