Reporting

How to create a report with counts per column for one specific field?

ten_yard_fight
Path Finder

I have a search that breaks down what files were accessed, how much data was retrieved and how many total requests for one particular field. Example search query below:

index="my_index" source="access_log" directory="/my_app/" | chart count(status_code) AS Requests, sum(file_size) AS TotalData, values(path_to_file) AS FileRequested BY directory

The above query produces something like:

directory        Requests      TotalData        FileRequested
my_app           10            345677           html/index.html
                                                images/happy_face.png
                                                dynamic/more_happy.py

While this is fine if you only care about the TOTAL number for the entire directory, I would like to break it down so that I can display the number of requests and total data for each of the files requested listed by the directory. The directory should only be displayed once like the example above. Any suggestions? I've tried a couple of different ways but I cant get the directory to only display once and the rest of the rows populated with relevant data per file requested.

0 Karma
1 Solution

lguinn2
Legend

Splunk is not really designed as a "report writer." Depending on what you want to do, you may find it easier to use Splunk for all the computations and then export the results to a .csv file. Then you can take the csv file into any reporting tool to format it.

However, this should work:

index="my_index" source="access_log" directory="/my_app/" 
| stats count(status_code) AS Requests, sum(file_size) AS TotalData BY directory path_to_file
| eval sort_key=directory + " " +  path_to_file
| append [ search index="my_index" source="access_log" directory="/my_app/" 
    | stats count(status_code) AS Requests, sum(file_size) AS TotalData BY directory
    | eval sort_key=directory + "***Total" 
    | eval path_to_file = "Total for directory" ]
| sort 0 sort_key
| fields - sort_key
| rename path_to_file as FileRequested
| table directory FileRequested Requests TotalData 

Note that this will probably take at least twice as much time and resources to run.

View solution in original post

0 Karma

lguinn2
Legend

Splunk is not really designed as a "report writer." Depending on what you want to do, you may find it easier to use Splunk for all the computations and then export the results to a .csv file. Then you can take the csv file into any reporting tool to format it.

However, this should work:

index="my_index" source="access_log" directory="/my_app/" 
| stats count(status_code) AS Requests, sum(file_size) AS TotalData BY directory path_to_file
| eval sort_key=directory + " " +  path_to_file
| append [ search index="my_index" source="access_log" directory="/my_app/" 
    | stats count(status_code) AS Requests, sum(file_size) AS TotalData BY directory
    | eval sort_key=directory + "***Total" 
    | eval path_to_file = "Total for directory" ]
| sort 0 sort_key
| fields - sort_key
| rename path_to_file as FileRequested
| table directory FileRequested Requests TotalData 

Note that this will probably take at least twice as much time and resources to run.

0 Karma

ten_yard_fight
Path Finder

Thanks for your response Iguinn, I took snippets of your example above and got something working.

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...