{"id":13972,"date":"2020-06-15T14:57:05","date_gmt":"2020-06-15T14:57:05","guid":{"rendered":"https:\/\/merikebi.warrenmyers.com\/?p=13972"},"modified":"2020-06-15T14:57:05","modified_gmt":"2020-06-15T14:57:05","slug":"answer-by-warren-for-splunk-left-jion-is-not-giving-as-exepcted","status":"publish","type":"post","link":"https:\/\/merikebi.warrenmyers.com\/?p=13972","title":{"rendered":"Answer by warren for Splunk left jion is not giving as exepcted"},"content":{"rendered":"

If you do<\/em> want to do this with a join<\/code>, what you had, slightly changed, should<\/em> be correct:<\/p>\n

index=\"orders\" \"Online order received\" earliest=-9d latest=-8d \n| rex field=message \"paymentHashed=(?<payHash>.([a-z0-9_\\.-]+))\" \n| stats values(_time) as onlineOrderTime by payHash\n| join type=left payHash \n    [search index=\"orders\" \"Telesale order received\" earliest=-20d latest=-5m\n    | rex field=message \"paymentHashed=(?<payHash>.([a-z0-9_\\.-]+))\" \n    | rename timestamp as TeleSaleTime \n    | stats values(TeleSaleTime) by payHash ]\n| rename timestamp as onlineOrderTime \n<\/code><\/pre>\n
Note the added | stats values(...) by<\/code> in the subsearch: you need to ensure you’ve removed any duplicates from the list, which this will do. By using values()<\/code>, you’ll also ensure if there’re repeated entries for the payHash<\/code> field, they get grouped together. (Similarly, added a | stats values...<\/code> before<\/em> the subsearch to speed the whole operation.)<\/p>\n
\n
You should be able to do this without a join<\/code>, too:<\/p>\n
index=\"orders\" ((\"Online order received\" earliest=-9d latest=-8d) OR \"Telesale order received\" earliest=-20d)) \n| rex field=_raw \"(?<order_type>\\w+) order received\"\n| rex field=message \"paymentHashed=(?<payHash>.([a-z0-9_\\.-]+))\"\n| stats values(order_type) as order_type values(_time) as orderTimes by payHash\n| where mvcount(order_type)>1    \n<\/code><\/pre>\n
After you’ve ensured your times are correct, you can format them – here’s one I use frequently:<\/p>\n
| eval onlineOrderTime=strftime(onlineOrderTime,\"%c\"), TeleSaleTime=strftime(TeleSaleTime,\"%c\")\n<\/code><\/pre>\n
You may also need to do further reformatting, but these should get you close<\/p>\n
\n
fwiw – I’d wonder why you were trying to look at Online orders from only 9 days ago, but Telesale orders from 20 days ago to now: but that’s just me.<\/p>\n
from User warren – Stack Overflow https:\/\/stackoverflow.com\/questions\/62366635\/splunk-left-jion-is-not-giving-as-exepcted\/62390609#62390609
\nvia IFTTT<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
If you do want to do this with a join, what you had, slightly changed, should be correct: index=”orders” “Online order received” earliest=-9d latest=-8d | rex field=message “paymentHashed=(?<payHash>.([a-z0-9_\\.-]+))” | stats values(_time) as onlineOrderTime by payHash | join type=left payHash [search index=”orders” “Telesale order received” earliest=-20d latest=-5m | rex field=message “paymentHashed=(?<payHash>.([a-z0-9_\\.-]+))” | rename timestamp as TeleSaleTime …
Continue reading Answer by warren for Splunk left jion is not giving as exepcted<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[991],"keyring_services":[],"class_list":["post-13972","post","type-post","status-publish","format-standard","hentry","category-blih","tag-stackexchange"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/posts\/13972","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=13972"}],"version-history":[{"count":1,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/posts\/13972\/revisions"}],"predecessor-version":[{"id":13973,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/posts\/13972\/revisions\/13973"}],"wp:attachment":[{"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=13972"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=13972"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=13972"},{"taxonomy":"keyring_services","embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Fkeyring_services&post=13972"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}