How to join two time stamps with different counts?

[samwatson45]

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?

[somesoni2 · Accepted solution]

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

[samwatson45]

Solved!

Thank you very much 🙂

[adonio]

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

[samwatson45]

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

[logloganathan]

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

[samwatson45]

[samwatson45]

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