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

[ten_yard_fight]

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.

[lguinn2 · Accepted solution]

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.

[ten_yard_fight]

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