https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331991#M98742 <P><IMG src="https://scontent-lht6-1.xx.fbcdn.net/v/t1.0-9/30594700_10210916480979033_5980068702081515520_o.png?_nc_cat=0&amp;oh=b88e3da4d495e61a85d12a7d4b5d7d07&amp;oe=5B6E44F3" alt="alt text" /></P> Wed, 11 Apr 2018 15:47:21 GMT samwatson45 2018-04-11T15:47:21Z https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331988#M98739 <P>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. <BR /> I am interested in having a count of two different date types. </P> <P>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 <CODE>CreatedAt</CODE>. For tickets that have been solved, there is a <CODE>SolvedAt</CODE>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.</P> <P>Is there a simple way to do this? The closest I have is </P> <PRE><CODE>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 </CODE></PRE> <P>Where <CODE>join type=outer</CODE>will allow me to join on blank solved by dates to months where tickets have been created, but not the other way around. </P> <P>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? </P> Wed, 11 Apr 2018 14:07:12 GMT https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331988#M98739 samwatson45 2018-04-11T14:07:12Z https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331989#M98740 <P>what is the output you got for your query? Could you please share the same </P> Wed, 11 Apr 2018 15:37:03 GMT https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331989#M98740 logloganathan 2018-04-11T15:37:03Z https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331990#M98741 <P>hello there, <BR /> what is the format of the string you are capturing as time? </P> Wed, 11 Apr 2018 15:43:46 GMT https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331990#M98741 adonio 2018-04-11T15:43:46Z https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331991#M98742 <P><IMG src="https://scontent-lht6-1.xx.fbcdn.net/v/t1.0-9/30594700_10210916480979033_5980068702081515520_o.png?_nc_cat=0&amp;oh=b88e3da4d495e61a85d12a7d4b5d7d07&amp;oe=5B6E44F3" alt="alt text" /></P> Wed, 11 Apr 2018 15:47:21 GMT https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331991#M98742 samwatson45 2018-04-11T15:47:21Z https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331992#M98743 <P>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).</P> Wed, 11 Apr 2018 15:48:19 GMT https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331992#M98743 samwatson45 2018-04-11T15:48:19Z https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331993#M98744 <P>This format<BR /> 2018-03-27 09:20:42.0</P> Wed, 11 Apr 2018 15:48:47 GMT https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331993#M98744 samwatson45 2018-04-11T15:48:47Z https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331994#M98745 <P>There is no full outer join in SPlunk. Considering joins are anyways expensive, try something like this:</P> <PRE><CODE>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 </CODE></PRE> Wed, 11 Apr 2018 15:48:56 GMT https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331994#M98745 somesoni2 2018-04-11T15:48:56Z https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331995#M98746 <P>Solved! </P> <P>Thank you very much <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span> </P> Wed, 11 Apr 2018 15:54:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-join-two-time-stamps-with-different-counts/m-p/331995#M98746 samwatson45 2018-04-11T15:54:29Z