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

samwatson45
Path Finder

Solved!

Thank you very much 🙂

0 Karma

adonio
Ultra Champion

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
Get Updates on the Splunk Community!

Earn a $35 Gift Card for Answering our Splunk Admins & App Developer Survey

Survey for Splunk Admins and App Developers is open now! | Earn a $35 gift card!      Hello there,  Splunk ...

Continuing Innovation & New Integrations Unlock Full Stack Observability For Your ...

You’ve probably heard the latest about AppDynamics joining the Splunk Observability portfolio, deepening our ...

Monitoring Amazon Elastic Kubernetes Service (EKS)

As we’ve seen, integrating Kubernetes environments with Splunk Observability Cloud is a quick and easy way to ...