CODEWITHSUNDEEP
splunksplunk-querysplunk-formulasplunk-calculationsplunk-dashboard
user3376592
user3376592

Reputation: 191

Query to extract data

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.

Upvotes: 2

Views: 761

Answers (1)

Mads Hansen
Mads Hansen

Reputation: 66783

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

Upvotes: 2

Related Questions