Splunk Search

How do I count the number of each of the different options for a field and present it in a table?

capilarity
Path Finder

I have an access log from a document system that includes a username and the type of action that was carried out on the document.
The documents are all uniquely named and there are thousands of them.
There are currently 5 actions a user can carry out on a file (lets call them A, B, C, D and E) and each log entry will include one these.
The user can do more than one action be document per day, and the user can do the same action multiple times per day on a single document.
All the actions are
Want I would like to display on a single line of a table is:

UserName  Total_Docs_Accessed Total_Actions "Number Of Docs With Action A", "Number Of Docs With Action B", "Number Of Docs With Action C" etc

Some times there will be a ZERO count for some of the actions

I have been able to get this to work with one line for each action for each user, but that gives up to 5 lines per user, and I have thousands of users.

I've tried join'ing, eval'ing, stat'ing and nothing seem to work where there is more than one user.

Thanks

Below is a simplified example of the log file

Time,          User, File,     Action
01/01/16 10:28 User1 File1.doc A
01/01/16 10:29 User1 File2.doc A
01/01/16 10:30 User1 File2.doc C
01/01/16 10:32 User1 File2.doc B
01/01/16 10:34 User1 File3.doc E
01/01/16 10:35 User1 File1.doc A
01/01/16 10:36 User2 File4.doc A
01/01/16 10:37 User3 File1.doc D
01/01/16 10:38 User4 File5.doc D
01/01/16 10:39 User4 File5.doc A

This should give a table thus:

Username, Total Docs,      Total Actions,    Action A, Action B, Action C, Action D, Action E
User1,    3,               6,                3,        1,        1,        0         1
User2,    1,               1,                0,        0,        0,        0,        0         
User3,    1,               1,                0,        0,        0,        1,        0           
User4,    1,               1,                1,        0,        0,        1,        0
0 Karma

sirkgm14vg
Explorer

If you want to format your tables using ASCII, use something like https://ozh.github.io/ascii-tables/

0 Karma

sirkgm14vg
Explorer

This is one way you can do it. You will use the chart command with the class over to break out the User by the Action. Then using the join command you will do a subsearch which will get the total docs and actions by the user. Append will not work as it will create another set over users in the first column.

source="splunk_answers_example_490188.csv"  | chart count(Action)  over User by Action | join [search source="splunk_answers_example_490188.csv"  | stats count(File) as TotalDocs, count(Action) as TotalActions by User]

=====

+-------+---+---+---+---+---+--------------+-----------+
| User  | A | B | C | D | E | TotalActions | TotalDocs |
+-------+---+---+---+---+---+--------------+-----------+
| User1 | 3 | 1 | 1 | 0 | 1 |            6 |         6 |
| User2 | 1 | 0 | 0 | 0 | 0 |            1 |         1 |
| User3 | 0 | 0 | 0 | 1 | 0 |            1 |         1 |
| User4 | 1 | 0 | 0 | 1 | 0 |            2 |         2 |
+-------+---+---+---+---+---+--------------+-----------+
0 Karma

capilarity
Path Finder

Thanks sirkgm14vg. Almost There!!
In the above example log file, user 1 accessed only 3 docs but carried out 6 actions overall. It needs some kind of dedup on a per username basis but I can't work out how

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...