I have a table that shows me the username, the web resource they accessed, total number of times they accessed each file (FileCount) and the summation of all web resources they accessed. The problem I am seeing is that when a user accessed say 8+ resources, the results in my table grows very long for that user. In some cases, some users hit over 50 resources.
My question is 2 parts:
a) Is there a way to TRUNCATE or limit this part of the table? I've seen results show up as TRUNCATED before in a table but don't recall how that was done. I want no more than 5 rows per user with largest counts first. But I still want Total FileCount to be the full number and accurate count #.
b) sort the FileCount list?
How the table currently looks
user Total FileCount Resource FileCount
jsmith 5 file1 5
jdoe 30 file1 1
file10 3
file2 2
file3 2
file4 1
file5 7
file6 3
file7 1
file8 9
file9 1
How I WANT the table to look
user Total FileCount Resource FileCount
jsmith 5 file1 5
jdoe 30 file8 9
file5 7
file10 3
file6 3
file2 2
Current SPL
| makeresults count=35
| streamstats count
| eval user = case(count=1 OR count=2 OR count=3 OR count=4 OR count=5, "jsmith", count=6 OR count=7 OR count=8 OR count=9 OR count=10 OR count=11 OR count=12 OR count=13 OR count=14 OR count=15 OR count=16 OR count=17 OR count=18 OR count=19 OR count=20 OR count=21 OR count=22 OR count=23 OR count=24 OR count=25 OR count=26 OR count=27 OR count=28 OR count=29 OR count=30 OR count=31 OR count=32 OR count=33 OR count=34 OR count=35, "jdoe")
| eval resource = case(count=1 OR count=2 OR count=3 OR count=4 OR count=5 OR count=6, "file1", count=7 OR count=8, "file2", count=9 OR count=10, "file3", count=11, "file4", count=12 OR count=13 OR count=14 OR count=15 OR count=16 OR count=17 OR count=18, "file5", count=19 OR count=20 OR count=21, "file6", count=22, "file7", count=23 OR count=24 OR count=25 OR count=26 OR count=27 OR count=28 OR count=29 OR count=30 OR count=31, "file8", count=32, "file9", count=33 OR count=34 OR count=35, "file10")
| stats count AS Files by user resource
| eventstats sum(Files) AS TotalFiles by user resource
| stats sum(Files) AS "Total FileCount", list(resource) AS Resource, list(TotalFiles) AS "FileCount" by user
... View more