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

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

sravanthikand — Tue, 03 Jul 2018 15:32:16 GMT

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

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

somesoni2 — Tue, 03 Jul 2018 16:19:43 GMT

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"

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

sravanthikand — Tue, 03 Jul 2018 17:43:11 GMT

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 .

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

sravanthikand — Tue, 03 Jul 2018 18:02:05 GMT

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 ?

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

somesoni2 — Tue, 03 Jul 2018 18:11:52 GMT

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.

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

sravanthikand — Tue, 03 Jul 2018 20:45:06 GMT

Thanks

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

I am only getting 60 day bucket in my results .

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

somesoni2 — Tue, 03 Jul 2018 20:59:48 GMT

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?

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

sravanthikand — Wed, 04 Jul 2018 17:24:19 GMT

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 ?

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

sravanthikand — Wed, 04 Jul 2018 17:58:40 GMT

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