Splunk Search

Count events based on two fields

Accak
Path Finder

I have table like this:

alt text

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!

Tags (3)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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

Accak
Path Finder

I have no idea how it works, but thanks a lot!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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. 😉

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...