Table Alphanumeric Totals

[ARothman]

I am building a report for AV auditing. The requirements are that there be 1) a total sum of specific values in specific fields and 2) a row for each endpoint with AV and its status based on the raw data Splunk has received from the AV console. To try and help explain this, here is an example of what I am looking to accomplish:

Endpoint    | AV Policy Status | Last Comm. (Days) | Last AV Update (Days)

Computer1 |       Compliant     |              27             |              27

Computer2 |       Compliant     |              27             |              27

Computer3 |       Compliant     |              27             |              27

Computer4 |       Compliant     |               0              |               0

Computer5 |       Compliant     |               0              |               0

----------------+--------------------------+-----------------------------+----------------------------------

Totals                     5                            2                             2

Now, there are a number of more fields (columns) which are going to be displayed, but for the sake of formatting, I removed them for the example. In my example, you can see that I want to display the totals of a specific value in a field (column) that equals a desired value. In this situation, I want to display the totals of all 'Compliant' endpoints, the totals of all endpoints with a last communication in the last 0 days (ie last 24 hours) and the totals of all endpoints which have the latest AV update.

Of course, I can create two separate tables, one for all the details and one for the totals, but I would prefer this to be in one report which I can then export to one csv, if at all possible. I have messed around with addtotals, addcoltotals, etc., but I can't seem to get the results I'm looking for.

Anyone have any other ideas / pointers?

[martin_mueller · Accepted solution]

Assuming you have the table, you could do roughly this:

... | appendpipe [stats count(eval(match(AV Policy Status, "Compliant"))) as "AV Policy Status" count(eval(Last Comm. (Days) == 0)) as "Last Comm. (Days)" count(eval(Last AV Update (Days) == 0)) as "Last AV Update (Days)" | eval Endpoint="Totals"]

Now, you will get problems with those field names in the stats, so you may want to do this before giving them their user-friendly names.

[martin_mueller]

Concerning 1), you can sort your output... you just need an appropriate column to sort by. One approach would be to number your original rows using streamstats count, then to add that field to your stats in the appended pipe with a lower number, ie -1, and to sort by that column. That way you maintain the original ordering and get to put your Totals where ever you like.

Concerning 2), you can in principle do anything you like. For example, I've added a field to the stats named Endpoint with the value "Totals". I'm not sure where you exactly want to add stuff though.

[ARothman]

After making the needed adjustments for my actual report, this worked perfectly, thank you very much.

A few questions, if I may, about some minor formatting adjustments:

1) Is there a means to force this 'Totals' row to be at the top?
2) Is there a way to append the output of the stats to also include some sort of text (i.e. +" Endpoints")?