So I took this as an opportunity to quickly learn how these new tables are set up... all I can say is I regret that. Getting down to the ACL was pretty straightforward but the way the rules are in the db is not making much sense to me. First of all nothing in SWQL will actually show you the content of the line, so nothing will show you anything like 'permit tcp any4 host 192.168.xx.xx eq https', the whole thing just refers to them by their rule numbers. What was more frustrating was that the rule numbering scheme seems not to add up. It seems that the db starts counting at 0 but the UI adds 1 so things can start at 1. Easy to offset for single values but when a rule is shadowed by multiple other rules they end up in a string and I can't add 1 to a string and get the intended results.
On the plus side I did find out that if you click on the 1-10 it allows you to pick a number of items to display. I think that selection doesn't seem to persist though, every time i go to a new ACL I was back to 10 per page.
This is the best I can do for you, it looks up the latest config of each type, and lets you know the summary info about any ACL that has issues and generates a link to that page for you to look it over. Go ahead and remove the comment marks if you want to see the madness that lies deeper but I fear it won't be too helpful.
,concat('/ui/ncm/aclRules/',tostring(n.CoreNodeID),'/',+ca.ConfigID,'?aclName='+acl.AccessListName) as [_linkfor_AccessListName]
--,(ace.RuleId+1) as [RuleId]
--,case when rd.OverlappingType = 2 then 'Partially Shadowed'
--when rd.OverlappingType = 3 then 'Fully Redundant'
--when rd.OverlappingType = 4 then 'Partially Redundant'
--end as [Overlap Type]
FROM NCM.Nodes n
join (SELECT NodeID, max(DownloadTime) as [DownloadTime], ConfigType
group by nodeid, configtype) latest on latest.nodeid=n.nodeid
join NCM.ConfigArchive ca on ca.NodeID=n.nodeid and ca.DownloadTime=latest.downloadtime
join NCM.ShadowRuleDetectionAclStatistics acl on acl.ConfigId=ca.ConfigID and (FullyShadowedCount <> 0 or PartiallyShadowedCount <> 0 or FullyRedundantCount <> 0 or PartiallyRedundantCount <> 0)
--join NCM.ShadowRuleDetectionResult sdrd on sdrd.ConfigId=acl.ConfigId and acl.AccessListName=sdrd.AccessListName
--join NCM.AceShadowRuleDetectionResult ace on ace.SrdrId=sdrd.srdrid
--join NCM.RuleDetection rd on rd.ResultId=ace.ResultId
order by n.nodecaption, ca.configtype, acl.AccessListName--, [RuleId]
Loop1 Systems: SolarWinds Training and Professional Services
Obi-Wan has taught you well!
I'm looking forward to testing this on my Net Insight ACLs and seeing what the output looks like.