Splunk Search

How to truncate the number of results in my table?

DEAD_BEEF
Builder

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
0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults count=35 
| streamstats count 
| eval user = if(count <=5, "jsmith","jdoe") 
| eval resource = case(count <=6, "file1", count <=8, "file2", count <= 10, "file3", count = 11, "file4"
    , count <= 18, "file5", count <= 21, "file6", count=22, "file7", count <= 31, "file8", count=32, "file9"
    , count <= 35, "file10") 
| stats count AS Files by user resource 
| eventstats sum(Files) AS TotalFiles by user resource 
| sort - TotalFiles + resource 
| streamstats global=f count by user 
| stats sum(Files) AS "Total FileCount", list(eval(if(count<6,resource,NULL))) AS Resource
    , list(eval(if(count<6,TotalFiles,NULL))) AS "FileCount" by user

View solution in original post

to4kawa
Ultra Champion
| makeresults count=35 
| streamstats count 
| eval user = if(count <=5, "jsmith","jdoe") 
| eval resource = case(count <=6, "file1", count <=8, "file2", count <= 10, "file3", count = 11, "file4"
    , count <= 18, "file5", count <= 21, "file6", count=22, "file7", count <= 31, "file8", count=32, "file9"
    , count <= 35, "file10") 
| stats count AS Files by user resource 
| eventstats sum(Files) AS TotalFiles by user resource 
| sort - TotalFiles + resource 
| streamstats global=f count by user 
| stats sum(Files) AS "Total FileCount", list(eval(if(count<6,resource,NULL))) AS Resource
    , list(eval(if(count<6,TotalFiles,NULL))) AS "FileCount" by user

DEAD_BEEF
Builder

Exactly what I was looking for!

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...