How to determine if a compliance check passed for all hosts

[chaday00]

I have created the search below which:

•  Filters out by only hostnames that I want
• Then extracts the STIG ID from those results
• Then extracts the controls status
• Lastly, consolidating Errors, Failed, and Warnings into a group of 'failed' controls with the remaining being "Passed"

What I would like to do is identify any controls that have passed across all of the hostnames and vice versa identify the controls that have failed across all of the host names.

Example: 15 STIG ID(s) have Failed across all hosts. 200 STIG ID(s) have passed a crossed all hosts.

Failed	Passed
15	200

index="tenable" sourcetype="tenable:sc:vuln" repository="Audit Repository" 
    [ inputlookup windows10_hostnames.csv 
    | fields dnsName ]
| rex field=pluginName "(?<stigid>\w{4}\S\w{2}\S\d{6})\s+.*" 
| rex field=pluginText "\<cm\:compliance-result\>(?<status>\w+)\<\/cm\:compliance-result\>" 
| eval passFail=if(IN(status,"ERROR","FAILED","WARNING"), "Failed","Passed") 

I tried appending the below to the end of this query. While it's interesting data, I'm having a hard time figuring out the comparison and filtering to get the desired output in the table above.

| stats values(stigid) by dnsName passFail
| stats count by dnsName passFail

Any help is much appreciated.

[to4kawa]

index="tenable" sourcetype="tenable:sc:vuln" repository="Audit Repository" 
    [ inputlookup windows10_hostnames.csv 
    | fields dnsName ]
| rex field=pluginName "(?<stigid>\w{4}\S\w{2}\S\d{6})\s+.*" 
| rex field=pluginText "\<cm\:compliance-result\>(?<status>\w+)\<\/cm\:compliance-result\>" 
| stats count(eval(IN(status,"ERROR","FAILED","WARNING"))) as "Failed" count(eval(!IN(status,"ERROR","FAILED","WARNING"))) as "Passed" by stigid dnsName

[chaday00]

This is not really telling me '14 hosts passed "stigid x"'.

I'm getting the results like below, where there is a stigid listed for every host. 

stigid	dnsName	Failed	Passed
WN10-00-000005	hostname1	0	1
WN10-00-000005	hostname2	0	1

[inventsekar]

Please check the passFail calculation works find and gives your the count correctly(by query 1)..also pls check the query 2. 

Query 1 - index="tenable" sourcetype="tenable:sc:vuln" repository="Audit Repository" 
    [ inputlookup windows10_hostnames.csv 
    | fields dnsName ]
| rex field=pluginName "(?<stigid>\w{4}\S\w{2}\S\d{6})\s+.*" 
| rex field=pluginText "\<cm\:compliance-result\>(?<status>\w+)\<\/cm\:compliance-result\>" 
| eval passFail=if(IN(status,"ERROR","FAILED","WARNING"), "Failed","Passed") 
| table stigid dnsName passFail 

Query 2 - index="tenable" sourcetype="tenable:sc:vuln" repository="Audit Repository" 
    [ inputlookup windows10_hostnames.csv 
    | fields dnsName ]
| rex field=pluginName "(?<stigid>\w{4}\S\w{2}\S\d{6})\s+.*" 
| rex field=pluginText "\<cm\:compliance-result\>(?<status>\w+)\<\/cm\:compliance-result\>" 
| eval passFail=if(IN(status,"ERROR","FAILED","WARNING"), "Failed","Passed") 
| stats count(stigid) by dnsName passFail

[chaday00]

Query 1: confirmed everything works as expected

stigid	dnsName	passFail
WN10-EM-00015	hostname1	Passed
WN10-EM-00015	hostname2	Passed

Query 2:  confirmed that for each dnsName, I'm getting the status and count of STIG ids that either passed or failed

dnsName	passFail	count(stigid)
hostname1	Passed	34
hostname2	Passed	34

[inventsekar]

sooo, @chaday00 its working fine or some more modifications required, please suggest!

[chaday00]

Yes, additional modification is needed. Referencing the OP, neither of the queries you provided, resulted in what I'm trying to do. However they do provide the expected output from those queries...

Consider the following csv

stigid,dnsName,,,,,
WIN-10-0001,Test01,,,,,
WIN-10-0003,Test01,,,,,
WIN-10-0004,Test01,,,,,
WIN-10-0001,Test02,,,,,
WIN-10-0003,Test02,,,,,
WIN-10-0004,Test02,,,,,
WIN-10-0001,Test03,,,,,
WIN-10-0003,Test03,,,,,
WIN-10-0004,Test03,,,,,
WIN-10-0011,Test02,,,,,
WIN-10-0013,Test01,,,,,
WIN-10-0014,Test03,,,,,
WIN-10-0011,Test01,,,,,
WIN-10-0013,Test01,,,,,
WIN-10-0014,Test01,,,,,

This CSV assumes all the stigid and hostnames have "Passes". I know that they only 'dnsName' that "Passed" all stigid checks is 'Test01'. In the OP, I'm trying to figure out a way to return how many 'dnsName's have "Passed" all queries. 

If I use the query below, I get very close:

| inputlookup test_kv.csv | eventstats count by dnsName, stigid | stats list(stigid) as id by dnsName | stats count by id

This results in the table below:

id	count
WIN-10-0001	3
WIN-10-0003	3
WIN-10-0004	3
WIN-10-0011	2
WIN-10-0013	2
WIN-10-0014	2

This shows me that for the 6 stigid's, only 3 dnsNames have passed 3 of those stigids. However, this is not what I'm trying to do.

I know that 'Test01' passed across all 6 I'd like to build a query that reflects that, in this example, only 1 dnsName passed across all stigids. 

[chaday00]

It wouldn't accept my edits to the above, so I'll try here:

With the same CSV data as above. Consider the results of the following query:

| inputlookup test_kv.csv | eventstats count by dnsName, stigid | dedup dnsName, stigid | stats list(stigid) as id by dnsName

Similar to one of your outputs. I can see that only 'Test01' has passed all 6 of the stigid's. You can see this in the table below:

dnsName	id
Test01	WIN-10-0001 WIN-10-0003 WIN-10-0004 WIN-10-0011 WIN-10-0013 WIN-10-0014
Test02	WIN-10-0001 WIN-10-0003 WIN-10-0004 WIN-10-0011
Test03	WIN-10-0001 WIN-10-0003 WIN-10-0004 WIN-10-0014

In the OP--based of this example, I'm trying to figure out a query that will return count '1' for 'Passed' because only 1 'dnsName' has Passed every stigid

[inventsekar]

two stats in single search is possible and requires some tweaks here and there...pls check this:

| inputlookup test_kv.csv | eventstats count by dnsName, stigid | dedup dnsName, stigid | stats list(stigid) AS id sum(dnsName) AS dnsNameCount by dnsName  

>>It wouldn't accept my edits to the above, so I'll try here:

post editing is possible. above your reply, you can see a small drop down box and select "edit reply"

[chaday00]

I see where you're going and likely on the right track if it returns '1' for this control set. But for some reason the dnsNamCount column is blank? 

dnsName id dnsNameCount

Test01	WIN-10-0001 WIN-10-0003 WIN-10-0004 WIN-10-0013 WIN-10-0011 WIN-10-0014
Test02	WIN-10-0001 WIN-10-0003 WIN-10-0004 WIN-10-0011
Test03	WIN-10-0001 WIN-10-0003 WIN-10-0004 WIN-10-0014

[inventsekar]

the stats gives difficult troubles at times.. (EDITed)

Please check:

| inputlookup test_kv.csv | eventstats count by dnsName, stigid | dedup dnsName, stigid | stats list(stigid) AS id count(dnsName) AS dnsNameCount by dnsName  

[chaday00]

That's pretty much the output I thought it was going to produce which was the number of findings that passed per dnsName. 

Still not what I'm trying to do. I'm thinking I'll have to create a custom search in Python to make this work.

I'm thinking I might have to do a foreach() loop on the findings that 'Passed' and against a list of hostnames. Whichever hostname is identified for every stigid as 'Passed', will be reported out. I could then get a count of only the hostnames that have passed every control.

Thanks for your time and help. 

[inventsekar]

ok then, lets ask our splunk gurus @richgalloway @ITWhisperer  @gcusello 

[ITWhisperer · Accepted solution]

index="tenable" sourcetype="tenable:sc:vuln" repository="Audit Repository" 
    [ inputlookup windows10_hostnames.csv 
    | fields dnsName ]
| rex field=pluginName "(?<stigid>\w{4}\S\w{2}\S\d{6})\s+.*" 
| rex field=pluginText "\<cm\:compliance-result\>(?<status>\w+)\<\/cm\:compliance-result\>" 
| eval passFail=if(IN(status,"ERROR","FAILED","WARNING"), "Failed","Passed") 
| stats values(dnsName) as dnsName by stigid passFail
| eventstats dc(dnsName) as totaldnsname
| stats count(dnsName) as dnsnamecount values(totaldnsname) as totaldnsname by stigid passFail
| eval totalpass=if(passFail="Passed" AND dnsnamecount=totaldnsname,1,0)
| eval totalfail=if(passFail="Failed" AND dnsnamecount=totaldnsname,1,0)
| stats sum(totalfail) as Failed, sum(totalpass) as Passed

[inventsekar]

Thanks @ITWhisperer for the solution,.. and for our(beginners/intermediates) learning, could you please explain the logic about your SPL.. (will be very helpful for present and future learners!) 

[ITWhisperer]

OP Query

index="tenable" sourcetype="tenable:sc:vuln" repository="Audit Repository" 
    [ inputlookup windows10_hostnames.csv 
    | fields dnsName ]
| rex field=pluginName "(?<stigid>\w{4}\S\w{2}\S\d{6})\s+.*" 
| rex field=pluginText "\<cm\:compliance-result\>(?<status>\w+)\<\/cm\:compliance-result\>" 
| eval passFail=if(IN(status,"ERROR","FAILED","WARNING"), "Failed","Passed") 

OP wants to know how many times a stigid was failed by all dnsNames or passed by all dnsNames, so group dnsNames by stigid and passFail (instead of stigid by dnsName and passFail as in OP)

| stats values(dnsName) as dnsName by stigid passFail

We now have a list of dnsNames which passed and failed for each stigid. Now we want to know how many distinct dnsNames there are so we can tell if all failed or passed

| eventstats dc(dnsName) as totaldnsname

Now we count the dnsNames for each stigid that passed and failed

| stats count(dnsName) as dnsnamecount values(totaldnsname) as totaldnsname by stigid passFail

Evaluate if all failed or all passed

| eval totalpass=if(passFail="Passed" AND dnsnamecount=totaldnsname,1,0)
| eval totalfail=if(passFail="Failed" AND dnsnamecount=totaldnsname,1,0)

Determine total stigid where all dnsNames failed, and where all dnsNames passed Q.E.D.

| stats sum(totalfail) as Failed, sum(totalpass) as Passed

[chaday00]

Wow thank you so much. Appreciate this and all the help from @inventsekar 

[inventsekar]

Thanks @chaday00 for showing your appreciations thru the karma points ;)..

happy that we found out the solution.. it took some time but a good learning.. the search commands can give us difficult times, but, as always, good learnings!