How to count the number of alerts with various time ranges and displays, together?

[DEAD_BEEF]

I want to create a visualization that shows the number of sales in the last 1, 2, and 7 days all within the same visualization. Then I wanted to color it such that if the value is equal to zero, it should be red. I'm stuck trying to set the time constraints in each part of the search.

index=sales | stats count(purchase) within last1 AS oneday | stats count(purchase) within last2 AS twoday | stats count(purchase) within last7 AS sevenday | table oneday twoday sevenday

I tried using the below query but it says that it's not formatted correctly.

index=sales
| eval timeDiff=now() - _time
| stats count(eval(timeDiff < 86401 AND purchase=*)) AS last1day 
| stats count(eval(timeDiff < 172801 AND purchase=*)) AS last2day
| stats count(eval(timeDiff < 604801 AND purchase=*)) AS last7day
| table last1day last2day last7day

[niketn · Accepted solution]

[UPDATED] Added dummy rows with 0 count to show up in table in case there is no data.

 index=sales purchase=*
| eval timeDiff=now()-_time 
| eval timeDiff=case(timeDiff<86401,"last 024h",timeDiff>=86401 AND timeDiff<172801,"last 048h",timeDiff>=172801 AND timeDiff<604801,"last 168h") 
| stats count as Sales by timeDiff 
| append 
    [| makeresults 
    | fields - _time
    | eval data="timeDiff=\"last 024h\",Sales=0;timeDiff=\"last 048h\",Sales=0;timeDiff=\"last 168h\",Sales=0"
    | makemv data delim=";"
    | mvexpand data
    | rename data as _raw
    | KV
    | fields - _raw]
| dedup timeDiff

---

@DEAD_BEEF, seems like you are trying to get stats for last 24 hours, last 48 hours and last 168 hours. You can try the following query if you want sales stats as separate columns (fields) as per your second query above. Notice purchase=* should be added to your base search

index=sales purchase=*
| eval timeDiff=now() - _time 
| stats count(eval(timeDiff<86401)) AS last1day count(eval(timeDiff>=86401 AND timeDiff<172801)) AS last2day count(eval(timeDiff>=172801 AND timeDiff<604801)) AS last7day

If you want the stats to be in rows you can try the following. This way Range Color needs to be applied only on one Column i.e. timeDiff:

index=sales purchase=*
| eval timeDiff=now()-_time 
| eval timeDiff=case(timeDiff<86401,"last 024h",timeDiff>=86401 AND timeDiff<172801,"last 048h",timeDiff>=172801 AND timeDiff<604801,"last 168h") 
| stats count as Sales by timeDiff

[DEAD_BEEF]

I like the second solution because as you mentioned, the range color only has to be applied to one column, however I noticed that when sales=0, (day 3-7), it simply doesn't show up in the table. Is there any way to force it to show up with a count of "0" ?

[niketn]

@DEAD_BEEF, I have updated my answer with dummy rows with 0 count to show up. Please try out and confirm!

[DEAD_BEEF]

This works perfectly @niketnilay, thank you!

[somesoni2]

Try like this

index=sales purchase=*
 | eval timeDiff=now() - _time
 | stats count(eval(timeDiff < 86401)) AS last1day count(eval(timeDiff < 172801)) AS last2day count(eval(timeDiff < 604801)) AS last7day
 | table last1day last2day last7day

You wanted all your stats to be applied to same base search results, so they should be done together.