Splunk Search

How to join two time stamps with different counts?

samwatson45
Path Finder

I have a single dataset which contains a couple of variables which are time (date) based. The format for all of them is the same.
I am interested in having a count of two different date types.

So, a bit of context, each event is a ticket which has been created in a support ticketing system. Each ticket has an associated date of creation, titled CreatedAt. For tickets that have been solved, there is a SolvedAtfield, otherwise it is blank. I am interested in looking over a period of months and viewing how many were created and how many were completed in a given month, where the created and solved months are usually different.

Is there a simple way to do this? The closest I have is

source="Tickets" 
|  eval CreatedAt=substr(CreatedAt,1,7 ) 
| rename CreatedAt as time 
|stats count as Open  by time  
| join type=outer time 
[search   source="Tickets"  |  eval SolvedAt=substr(SolvedAt,1, 7)   | rename SolvedAt as time | stats count as Solved by time ]  
| table time Open Solved

Where join type=outerwill allow me to join on blank solved by dates to months where tickets have been created, but not the other way around.

Is there any way to do a full join where both sides can contain null values? Am I going about this the wrong way? Is there an easier solution?

0 Karma
1 Solution

somesoni2
Revered Legend

There is no full outer join in SPlunk. Considering joins are anyways expensive, try something like this:

source="Tickets" 
 |  eval CreatedAt=substr(CreatedAt,1,7 ) 
 | rename CreatedAt as time 
 |stats count as Open  by time  
 | append
 [search   source="Tickets"  |  eval SolvedAt=substr(SolvedAt,1, 7)   | rename SolvedAt as time | stats count as Solved by time ]  
| stats values(*) as * by time

View solution in original post

somesoni2
Revered Legend

There is no full outer join in SPlunk. Considering joins are anyways expensive, try something like this:

source="Tickets" 
 |  eval CreatedAt=substr(CreatedAt,1,7 ) 
 | rename CreatedAt as time 
 |stats count as Open  by time  
 | append
 [search   source="Tickets"  |  eval SolvedAt=substr(SolvedAt,1, 7)   | rename SolvedAt as time | stats count as Solved by time ]  
| stats values(*) as * by time

View solution in original post

samwatson45
Path Finder

Solved!

Thank you very much 🙂

0 Karma

adonio
SplunkTrust
SplunkTrust

hello there,
what is the format of the string you are capturing as time?

0 Karma

samwatson45
Path Finder

This format
2018-03-27 09:20:42.0

0 Karma

logloganathan
Motivator

what is the output you got for your query? Could you please share the same

0 Karma

samwatson45
Path Finder

alt text

0 Karma

samwatson45
Path Finder

So the 31 Open is correct however there should be 26 solved tickets ( As in there is one month in which a ticket was solved, but there was no ticket opened for this month, so nothing for it to join to).

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.