I have table like this:
I want to query number of completed tickets during the date that they were created.
e.g: As You can see, there are 5 completed tickets at 2017-03-01.
So whenever the ticket "day_open_ticket" is 2017-03-01 the value of "completed_during_day" should be 5.
But when "day_open_ticket" is different, like "2017-02-24" the value should be number of tickets completed it that day, not when it was completed (as is now).
My query:
eventstats count as opened_during_day by day_open_ticket|
eventstats count(day_open_ticket) as completed_during_day by day_complete_ticket|
I will appreciate any help!
This is a quick way to get JUST the number that both opened and closed on the same day
(your base search)
| where epoch_open_date = epoch_complete_date
| stats count by day_open_ticket
Or, if you want a more complete report, you can use this method. This produces some test data
| makeresults
| eval thedata="A!!!!2017-02-24!!!!2017-03-01 B!!!!2017-03-01!!!!2017-03-01 C!!!!2017-02-24!!!!2017-03-01 D!!!!2017-02-24!!!!2017-03-01 E!!!!2017-02-24!!!!2017-03-01 F!!!!2017-02-20!!!!2017-02-28 G!!!!2017-02-10!!!!2017-02-28 H!!!!2017-02-28!!!!2017-02-28 I!!!!2017-02-27!!!!2017-02-28"
| makemv thedata
| mvexpand thedata
| makemv delim="!!!!" thedata
| eval ticket_number=mvindex(thedata,0), day_open_ticket=mvindex(thedata,1), day_complete_ticket=mvindex(thedata,2)
| table ticket_number day_open_ticket day_complete_ticket
This processes the test data into a chart of how many opened, closed, or both opened and closed on each day
| eval mydata="DayOpen=".day_open_ticket." DayClose=".day_complete_ticket.if(day_open_ticket==day_complete_ticket," CloseSame=".day_open_ticket,"")
| table mydata
| makemv mydata
| mvexpand mydata
| makemv delim="=" mydata
| eval TheDate=mvindex(mydata,1), TheAction=mvindex(mydata,0)
| chart count over TheDate by TheAction
Resulting in this chart-
TheDate CloseSame DayClose DayOpen
2017-02-10 0 0 1
2017-02-20 0 0 1
2017-02-24 0 0 4
2017-02-27 0 0 1
2017-02-28 1 4 1
2017-03-01 1 5 1
This is a quick way to get JUST the number that both opened and closed on the same day
(your base search)
| where epoch_open_date = epoch_complete_date
| stats count by day_open_ticket
Or, if you want a more complete report, you can use this method. This produces some test data
| makeresults
| eval thedata="A!!!!2017-02-24!!!!2017-03-01 B!!!!2017-03-01!!!!2017-03-01 C!!!!2017-02-24!!!!2017-03-01 D!!!!2017-02-24!!!!2017-03-01 E!!!!2017-02-24!!!!2017-03-01 F!!!!2017-02-20!!!!2017-02-28 G!!!!2017-02-10!!!!2017-02-28 H!!!!2017-02-28!!!!2017-02-28 I!!!!2017-02-27!!!!2017-02-28"
| makemv thedata
| mvexpand thedata
| makemv delim="!!!!" thedata
| eval ticket_number=mvindex(thedata,0), day_open_ticket=mvindex(thedata,1), day_complete_ticket=mvindex(thedata,2)
| table ticket_number day_open_ticket day_complete_ticket
This processes the test data into a chart of how many opened, closed, or both opened and closed on each day
| eval mydata="DayOpen=".day_open_ticket." DayClose=".day_complete_ticket.if(day_open_ticket==day_complete_ticket," CloseSame=".day_open_ticket,"")
| table mydata
| makemv mydata
| mvexpand mydata
| makemv delim="=" mydata
| eval TheDate=mvindex(mydata,1), TheAction=mvindex(mydata,0)
| chart count over TheDate by TheAction
Resulting in this chart-
TheDate CloseSame DayClose DayOpen
2017-02-10 0 0 1
2017-02-20 0 0 1
2017-02-24 0 0 4
2017-02-27 0 0 1
2017-02-28 1 4 1
2017-03-01 1 5 1
I have no idea how it works, but thanks a lot!
That's why I made you the run-anywhere code.
Put the second set of code into a splunk session and hit enter. See what the fake "test data" looks like.
Then, add the third section, one line at a time , and see exactly what each line does to the data.
I'm going to explain it for you, line by line, but you should do it for yourself, too. There's no excuse for being a learner and not understanding the basic function of each and every line of code that you put into your jobs. That doesn't mean that you necessarily will know exactly which verb to reach for the next time, and what its syntax and output result is, but after a few repetitions, you'll instinctively remember what set of verbs you are choosing between, and a little bit of googling will get you an example to modify for your own use.
In the below discussion, (Date1) or (Date2) represent some date from the test data, for instance "2017-03-01".
This code adds a field called mydata...
| eval mydata="DayOpen=".day_open_ticket." DayClose=".day_complete_ticket.if(day_open_ticket==day_complete_ticket," CloseSame=".day_open_ticket,"")
...which will look like one of the following...
"DayOpen=(Date1) DayClose=(Date1) CloseSame=(Date1)"
"DayOpen=(Date1) DayClose=(Date2)"
This gets rid of all other data...
| table mydata
This turns that from a single flat variable into a multivalue variable...
| makemv mydata
...so it looks like this...
"DayOpen=(Date1)"
"DayClose=(Date1)"
"CloseSame=(Date1)"
... or this ...
"DayOpen=(Date1)"
"DayClose=(Date2)"
This turns each of those into a separate record...
| mvexpand mydata
This breaks each of THOSE up into a multivalue field, breaking on the equal sign instead of the above default, which was a space...
| makemv delim="=" mydata
So it looks like this...
"DayOpen" this is the FIRST mv field, which is at index number 0
"(Date1)" this is the SECOND mv field, which is at index number 1
This takes those two values and puts them into variables named TheAction and TheDate...
| eval TheDate=mvindex(mydata,1), TheAction=mvindex(mydata,0)
This produces the final count, formatted how you want it...
| chart count over TheDate by TheAction
You can also flip the words in that chart command and see what happens. 😉