topic Re: Comparing Dates in Results- How do I get a laterdate? in Splunk Search

Comparing Dates in Results- How do I get a laterdate?

jedimuffin — Mon, 10 Oct 2022 20:53:57 GMT

Hello, I have the search built that generates the results I want. But, the goal is to also be able to track high number of online orders after someone made a retail order.

index=data sector=Retail | stats earliest(_time) as firstretailapp latest(_time) as lastretailapp by username | join username [| search index=data sector=Online | stats earliest(_time) as firstonlinesale latest(_time) as lastonlinesale by username] | convert ctime(firstretailsale) ctime(lastretailsale) ctime(firstonlinesale) ctime(lastonlinesale)

When these results populate, I can not get the firstonlinesale to be a later date than the lastretailsale. I have tried | eval difference =time1-time2, and where difference >1 and other command searches to try and match up something but am unsuccessful.

Thanks!

Re: Comparing Dates in Results- How do I get a laterdate?

yuanliu — Mon, 10 Oct 2022 22:51:31 GMT

Avoid expensive join. Try

index=data sector IN (Retail, Online) | stats earliest(_time) as first_order latest(_time) as last_order by username sector | foreach *_order [eval <<MATCHSTR>>_retail = if(sector == "Retail", <<FIELD>>, null()) | eval <<MATCHSTR>>_online = if(sector == "Online", <<FIELD>>, null())] | stats earliest(*_*) as *_* by username | where first_online > last_retail | convert ctime(first_retail) ctime(last_retail) ctime(first_online) ctime(last_online)

Hope this helps

Re: Comparing Dates in Results- How do I get a laterdate?

jedimuffin — Thu, 13 Oct 2022 14:35:04 GMT

thank you. But I get 0 results and I dont understand what the search is doing. Also, in the where clause, that is the first time first_online and last_retail are mentioned? I will keep working on it

Re: Comparing Dates in Results- How do I get a laterdate?

jedimuffin — Thu, 13 Oct 2022 14:49:39 GMT

| stats earliest(*_*) as *_* by username

this drops it to one column of just a list of just usernames

Re: Comparing Dates in Results- How do I get a laterdate?

yuanliu — Fri, 14 Oct 2022 03:54:55 GMT

I think trying to use double wildcard (earliest(*_*) as *_*) was a mistake.

index=data sector IN (Retail, Online) | stats earliest(_time) as first_order latest(_time) as last_order by username sector | eval first_retail = if(sector == "Retail", first_order, null()) | eval first_online = if(sector == "Online", first_order, null()) | eval last_retail = if(sector == "Retail", last_order, null()) | eval last_online = if(sector == "Online", last_order, null()) | stats earliest(first_retail) as first_retail earliest(first_online) as first_online latest(last_retail) as last_retail latest(last_online) as last_online by username | where first_online > last_retail | convert ctime(first_retail) ctime(last_retail) ctime(first_online) ctime(last_online)

To clarify: the foreach subsearch is an iteration over first_order, last_order in order to populate first_retail, first_online, last_retail, and last_online as expanded in the above. But the double wildcard probably wouldn't work.

Re: Comparing Dates in Results- How do I get a laterdate?

jedimuffin — Fri, 14 Oct 2022 16:56:13 GMT

thank you! this works except for the conditional comparison of the where clause. but, I can just export the results and go from there. Thanks!