need help to Form Splunk search query ?

[rsathish47]

Hi All,
Can you please help me to form the search query for below scenario

ticket     Created   Closed
Tic1    Jan-15     Aug-15   
Tic2    Jan-15     Feb-15  
Tic3    Feb-15     Mar-15  
Tic4    Feb-15     Apr-15
Tic5    Feb-15     Mar-15
Tic6    Feb-15     Feb-15
Tic7    Mar-15     Mar-15  
Tic8    Mar-15     Apr-15
......
.....
....
...

Out/put

Month  OpenTic
Jan-15    2
Feb-15    4
Mar-15    3
...
...
...

Distribution:

jan-15 = 2 tics opened not closed so open tic is 2 jan open tic count
Feb-15 = 4 tics opened , 1 is closed (4-1)= 3 tic is open and
        From jan 1 tic still open, so 3+1 =4 open tic is in open    
        so (feb tic3,tic4,tic5) 3+ (jan Tic1) 1 = 4 feb open tic count 
Mar-15 = 2 tics open , 1 is closed (2-1)= 1 is open and from jan 1 tic is still open , Feb 1 tic is still open 
        so (Mar Tic8 )1+(jan Tic1)1+(feb Tic4)1= 3 mar open tic count

thanks
Sathish Rangan

[woodcock]

You need the concurrency command:

http://docs.splunk.com/Documentation/Splunk/6.0.2/SearchReference/Concurrency

Probably like this (untested: may need to use %e instead of %d😞

... | eval Created_epoch=strptime(Created, "%b-%d) | eval Closed_epoch=strptime(Closed, "%b-%d) | duration = coalesce(Closed_epoch, 99999999999999999) - Created_epoch | concurrency start=Created_epoch duration=duration | stats max(concurrency) AS OpenTic BY Created | rename Created AS Month

[marina_rovira]

Hi!

If I have this, I would do something like:

| eval ifClosed=if('Closed'="*", "yes","no") | stats count(eval(ifClose,"yes")) as closed_tickets | eval result=OpenTic-closed_tickets | timechart span=1mon result

I'm not sure about which indexes you have, but you should include both parts, the information of closed ticket(index 1) and the one for open tickets (index 2), index=1 OR index=2, and then the other thing.

Let me know if it doesn't work! I will try again.

Regards,
Marina

[rsathish47]

I will try this and post you the update