topic Re: How do I count the number of each of the different options for a field and present it in a table? in Splunk Search

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

capilarity — Fri, 20 Jan 2017 10:45:17 GMT

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

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

sirkgm14vg — Fri, 20 Jan 2017 17:39:28 GMT

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 |
+-------+---+---+---+---+---+--------------+-----------+

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

sirkgm14vg — Fri, 20 Jan 2017 17:42:51 GMT

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

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

capilarity — Mon, 23 Jan 2017 16:40:49 GMT

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