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 SolvedAt
field, 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=outer
will 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?
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
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
Solved!
Thank you very much 🙂
hello there,
what is the format of the string you are capturing as time?
This format
2018-03-27 09:20:42.0
what is the output you got for your query? Could you please share the same
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).