How can we build a report to show Cisco ASA problem ACLs based on Net Insight information?

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.

SELECT  n.NodeCaption

, n.AgentIP

, n.Vendor

, n.MachineType

,ca.ConfigType

,acl.AccessListName

,acl.FullyShadowedCount

,acl.PartiallyShadowedCount

,acl.FullyRedundantCount

,acl.PartiallyRedundantCount

,concat('/ui/ncm/aclRules/',tostring(n.CoreNodeID),'/',+ca.ConfigID,'?aclName='+acl.AccessListName) as [_linkfor_AccessListName]

--,(ace.RuleId+1) as [RuleId]

--,rd.OverlappingRules

--,rd.OverlappingType

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

FROM NCM.ConfigArchive

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]

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

Impressive--most impressive!

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.