Splunk Search

Table Alphanumeric Totals

ARothman
Path Finder

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?

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

ARothman
Path Finder

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")?

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