Reporting

How to create a report for product in time buckets for 0-14 , 14-30 days?

sravanthikand
New Member

I have splunk data similar to below where the product was purchased on different dates

alt text

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try something like this

Updated

your current search giving fields product and purchase_date
| eval purchased=strptime(purchase_date,"%b-%d-%Y")
| eventstats min(purchased) as min by product
| eval Period=case(purchased>=relative_time(min,"-14@d"),"0-14 days",
purchased>=relative_time(min,"-30@d"),"14-30 days",
purchased>=relative_time(min,"-60@d"),"30-60 days",
true(),"60+ days")
| eval product=product."##".strftime(min,"%b-%d-%Y")
| chart count by product Period
| rex field=product "(?<product>.+)##(?<FirstSeen>.+)"
| table prodct FirstSeen *
| rename product as "Product Name" FirstSeen as "First-Showed_up"

View solution in original post

0 Karma

sravanthikand
New Member

The 0-14 days ,
14-30 days ,
30-60 days is the bucket from the start time . It is not the calculation of current time - 14 days .
But 14 days is from minimum time to 14 days .

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this

Updated

your current search giving fields product and purchase_date
| eval purchased=strptime(purchase_date,"%b-%d-%Y")
| eventstats min(purchased) as min by product
| eval Period=case(purchased>=relative_time(min,"-14@d"),"0-14 days",
purchased>=relative_time(min,"-30@d"),"14-30 days",
purchased>=relative_time(min,"-60@d"),"30-60 days",
true(),"60+ days")
| eval product=product."##".strftime(min,"%b-%d-%Y")
| chart count by product Period
| rex field=product "(?<product>.+)##(?<FirstSeen>.+)"
| table prodct FirstSeen *
| rename product as "Product Name" FirstSeen as "First-Showed_up"
0 Karma

sravanthikand
New Member

I have date format as below

Wed Jun 13 02:00:43 2018 . Do I need to extract the rex to get the time buckets ?

0 Karma

sravanthikand
New Member

The purchase date "min" varies by the product . The min is different for each product .

stats min(purchase_date) by product gives me a table . I have a table to work the values to iterate against . Is there a loop variable to host two values of the table in data structure to compare against the events in splunk ?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

I did miss by product in my eventstats, added that now. I also updated hte query to compute the period/buckets based on first seen time i.e. min.

0 Karma

sravanthikand
New Member

Thanks

The line 11 table prodct FirstSeen * , , FirstSeen is the field ?

I am only getting 60 day bucket in my results .

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Yes, FirstSeen is the min field value that we appended to product in line 8 and extracted back in line 10.

Are you getting First-Showed_up values?

0 Karma

sravanthikand
New Member

Sorry , did not use First seen field in my query . Will try and post it .

0 Karma
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 ...