Query to extract data

Question

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.

score 2 · Accepted Answer · answered Jan 15 '22 at 13:52

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

Query to extract data

1 Answers1