Reporting

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

New Member

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

alt text

0 Karma
1 Solution

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

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

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

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

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

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

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

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

New Member

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

0 Karma