Splunk Search

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

DEAD_BEEF
Builder

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
0 Karma
1 Solution

niketn
Legend

[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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

[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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

DEAD_BEEF
Builder

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" ?

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

DEAD_BEEF
Builder

This works perfectly @niketnilay, thank you!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...