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!

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