I have created the search below which:
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.
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
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
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 |
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
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 |
sooo, @chaday00 its working fine or some more modifications required, please suggest!
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.
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
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"
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 |
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
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.
ok then, lets ask our splunk gurus @richgalloway @ITWhisperer @gcusello
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
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!)
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
Wow thank you so much. Appreciate this and all the help from @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!