Knowledge Management

How to show the first and last time an item was sold, and if it was ever sold to a particular customer (among other customers)?

wu_weidong
Path Finder

I'm trying to write a query that shows the first and last time an item was sold, and if that item was ever sold to a particular customer (among other customers).

For example, the output would be

ItemName  FirstBought   LastBought   Sold to John?
ItemA     10-1-2019     21-4-2019    No
ItemB     3-5-2017      21-1-2020    Yes
ItemC     6-12-2018     2-10-2019    No

My query is

index=db | eval soldToJohn=if(customer="John","Yes","No") | stats earliest(_time) as firstBought latest(_time) as lastBought by itemName soldToJohn | table itemName firstBought lastBought soldToJohn

My problem is by doing the stats ... by soldToJohn, I'm now getting 2 lines for ItemB, one with soldToJohn="Yes", the other with soldToJohn="No". But if I leave out soldToJohn in the stats part, then the soldToJohn column is empty. How do I have just 1 result with soldToJohn="Yes", as long as one of the customers is John?

Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @wu_weidong,
did you tried something like this?

 index=db 
| eval soldToJohn=if(customer="John","Yes","No") 
| stats earliest(_time) as firstBought latest(_time) as lastBought values(soldToJohn) AS soldToJohn BY itemName
| table itemName firstBought lastBought soldToJohn

Ciao.
Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @wu_weidong,
did you tried something like this?

 index=db 
| eval soldToJohn=if(customer="John","Yes","No") 
| stats earliest(_time) as firstBought latest(_time) as lastBought values(soldToJohn) AS soldToJohn BY itemName
| table itemName firstBought lastBought soldToJohn

Ciao.
Giuseppe

wu_weidong
Path Finder

The lines for ItemB are combined into 1 line, but the soldToJohn column contains both "No" and "Yes". Is there a way to display "Yes" if it is present (even if there is a "No" too), and "No" if there is no "Yes"?

ItemName  FirstBought   LastBought   Sold to John?
 ItemA     10-1-2019     21-4-2019    No
 ItemB     3-5-2017      21-1-2020    No
                                      Yes                                                
 ItemC     6-12-2018     2-10-2019    No
0 Karma

wu_weidong
Path Finder

I've added a eval soldToJohn_flag = if(in(soldToJohn, "Yes"),"Yes","No") to display those lines with both "No" and "Yes" to "Yes", and those with "No" only to "No". This effectively gives me what I'm looking for.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...