Here is a snippet of the logs:
127.0.0.1 - - [01/Dec/2020:00:00:11 -0500] "GET / url:"api/orderLaptop for customer id 123"
127.0.0.1 - - [01/Nov/2020:00:00:24 -0500] "GET / url:"api/orderLaptop for customer id 124"
127.0.0.1 - - [05/Nov/2020:00:00:11 -0500] "GET / url:"api/orderLaptop for customer id 333"
127.0.0.1 - - [01/Nov/2020:00:00:24 -0500] "GET / url:"api/orderCamera for customer id 124"
127.0.0.1 - - [05/Nov/2020:00:00:11 -0500] "GET / url:"api/orderCamera for customer id 333"
127.0.0.1 - - [10/Aug/2020:00:00:24 -0500] "GET / url:"api/orderLaptop for customer id 444"
127.0.0.1 - - [13/Aug/2020:00:00:24 -0500] "GET / url:"api/orderCamera for customer id 434"
Is it possible to generate a report in Splunk that shows how many products such as Laptops and Cameras have been purchased by customers every month.
My expected output should look like below:
| Item | Month purchased | Total customers purchased |
|---|---|---|
| Laptop | August | 1 |
| Camera | August | 1 |
| Laptop | November | 2 |
| Camera | November | 2 |
| Laptop | December | 1 |
| Camera | December | 0 |
Thank you very much.
Assuming that you already have the correct configs to extract the event time from the log event, you can format the month from the extracted _time with strftime().
Then, I would extract the other fields with rex and regex capture groups.
And then use stats to group by the Item, month_purchased, and customer.
Finally, remove the customer field with fields.
| eval month_purchased=strftime(_time, "%B")
| rex field=_raw "api/order(?<Item>\w+) for customer id (?<customer>\d+)"
| stats count as "Total customers purchased" by item month_purchased as "Month purchased" customer
| fields -customer