Compliance Report, Summarize & Count Multiple Violations Per Device

Anyone?

Not sure I understand completely what you're looking for, but hopefully this will get you part way there.  Just a quick stab at it.   I'm sure you could filter or group on specific violations if you wanted to. Not 100% sure that compliance works the way you're looking for it too.    If you add in PCR.RuleName into the select statement you can see individual rule names used in the count shown...

SELECT  COUNT(PCR.IsViolation) AS NumViolations, N.Caption, N.IP_Address FROM NCM_PolicyCacheResults PCR

  JOIN NCM_NodeProperties NP ON (NP.NodeID = PCR.NodeID)

  JOIN Nodes N ON (N.NodeID = NP.CoreNodeID)

WHERE (PCR.IsViolation = 1)

GROUP BY N.NodeID, PCR.IsViolation, N.Caption, N.IP_Address

ORDER BY NumViolations DESC, N.Caption

cnorborg‌

Thank you very much for the suggestion.  I will test it out shortly.

As for my goal(s)... Starting out in a single node environment, for the sake of the example...

node = "DSLAM-01"

DSLAM-01 provides service to a maximum of 48 customers, dsl interfaces 1-48.

Each of those individual interfaces requires 2 different profile types to be provisioned in order for the customers' services to work correctly. (1 "DSL Profile", and 1 "Access Profile", each with a different purpose.)

There are several different profiles for each profile type.

Again, for the sake of the example, let's say each profile type has 3 options.

"DSL Profile" type has "dp-a", "dp-b", & "dp-c" as available profiles to choose from.

"Access Profile" type has "ap-x", "ap-y", & "ap-z" as the profiles to choose from.

Originally, many years ago, we always used matching profiles. ex. "dp-b" was always paired with "ap-x".

Over the years, we have added different profiles for each type, and we no longer exclusively pair specific profiles.

Now, we can have dp-b paired with ap-y, or paired with ap-z, just as long as it is paired with one of the profiles from the opposite type.

This has created quite the mess, as this example does not account for the, almost, 20+ access profiles, 10+ dsl profiles, and all scattered out over nearly 700 nodes.

Now, I have built individual rules for each of the different profiles, as well as different policies for each of the profile types.

Back to our 1 node environment example...

If I were to run the standard policy report, it would tell me that node has 6 violations, 1 for each of the rules, as each of the 3 profiles, from each of the different profile types, are provisioned throughout the 48 interfaces.

When I click on the the little blue icon, as seen in my op, it pops up a window that shows me the "Violation Details".

Within those violation details, I am able to expand out a listing, showing me each of the occurrences for that specific profile rule. (If I could only get a total count of those lines shown here, for each of the different rule violation details... AGH, so close!)

I need the total number of occurrences that rule is triggered, per device.

Expanding our single node environment to 3 nodes, I need to see something similar to the following.

(under the assumption that each node is at full capacity, 48 interfaces X 3 nodes; gives us 144 total/combined interfaces, each provisioned with 2 different profiles, 1 from each type ...)

node = "DSLAM-01"

rule = "dp-a" violated 15 times

rule = "dp-b" violated 10 times

rule = "dp-c" violated 23 times

Total of 48 violations = full capacity

node = "DSLAM-02"

rule = "dp-a" violated 20 times

rule = "dp-b" violated 20 times

rule = "dp-c" violated 8 times

Total of 48 violations = full capacity

node = "DSLAM-03"

rule = "dp-a" violated 5 times

rule = "dp-b" violated 0 times

rule = "dp-c" violated 43 times

Total of 48 violations = full capacity

Total/Combined Violations

rule = "dp-a" violated 40 times

rule = "dp-b" violated 30 times

rule = "dp-c" violated 74 times

This would allow me to identify the nodes using outdated provisioning, which it already does, as the node(s) only shows up on the report if in violation.

However, this would also allow me to easily plan ahead for future maintenance, as well as provide a quick count the various profiles actually in use.

This project actually started several years ago. However, due to the time, and manpower, it takes to manually log into each node, one at a time, and show the config, count the occurrences, and correct the profiles when/if needed, this project has been on going for a long long time... By the time we would make any progress, marketing, and others, would change all of the packages being sold, and thus we would basically have to start over.

The policy rules do a perfect job of identifying all of the nodes we need to focus on.  It allows management to see the mess others have caused due to making changes without all parties aware.  It allows management to get a general idea of the efficiency of our current, and historical, provisioning.  However, I need to be able to provide a report that show this little bit of extra detail.  As they see it right now, there is no need to justify more time, or resources, as we only have a handful of nodes to fix, compared to the total number of these nodes in our network.  What they cannot see, and thus, do not know, is the time and resources required to correct each node.  This report would provide that exact information.  With this report, management can accurately justify the need of more time and/or resources to correct/update provisioning.  Although there are only 3 nodes in violation (back to our latest example), we will know that, if all 48 interfaces need to be provisioned with "dp-c", then we will need to allocate enough time/resources to accommodate 25 profile changes for "DSLAM-01", 40 changes for "DSLAM-02", and only 5 changes for "DSLAM-03".

I hope this example has cleared things up better, though, I have noticed that is not one of my better skills...

Again, thank you for your suggestion, and I will test it out shortly.

-Will

cnorborg‌

I just tested your query, however, the results show the total number of the different rules in violation.  This is actually good info to have for the report as well.

While I was in there, I started looking around, and I think I found where they keep that list of occurrences.

[dbo].[NCM_PolicyCacheResults].[XmlResults]

When I look in that table, I find that nice list of violation occurrences.

The only problem is, to me, anyway, those XML format doesn't look like a nice and simple list.

<CBs><CB L="!&#xD;" LN="586"><Ps><P FM="True" PT="dsl profile BB12M" ALL="TRUE"><L FL=" dsl profile BB12M&#xD;" FLN="723" /><L FL=" dsl profile BB12M&#xD;" FLN="827" /><L FL=" dsl profile BB12M&#xD;" FLN="914" /></P></Ps></CB></CBs>

So, if this is actually the data I am looking for, then I need to know how to do the following:

Count the total number of occurrences, within that table data, for each device. Counting only the important stuff in between all that garbage...

GARBAGE:
<CBs><CB L="!&#xD;" LN="586"><Ps><P FM="True" PT="dsl profile BB12M" ALL="TRUE">

NEED TOTAL UNIQUE COUNT OF THESE:
<L FL=" dsl profile BB12M&#xD;" FLN="723" />
<L FL=" dsl profile BB12M&#xD;" FLN="827" />
<L FL=" dsl profile BB12M&#xD;" FLN="914" />

MORE GARBAGE:
</P></Ps></CB></CBs>

Then, after we get the total for each report, for each device, individually, we get the combined total of all occurrences for all devices, for each like report.

I must have looked at that table 20 times yesterday, along with numerous others, and not once, was I able to find anything useful.

I go and run your query, following your advice, and your query must have jumped out and slapped me hard enough to pay closer attention.

Hopefully, the SQL powers that be will show me a way to harvest this newly discovered data.

Thanks,

-Will

Yea, you kind of lost me again on your further explanation!  :-)   But, we don't have anything like that, so...

I figured the little bit I found would put you on the right track though.  It is interesting that they then keep the results in an XML field, never even looked at that before.  Unfortunately I have no idea how ot parse an XML field in SQL, guessing you might need to export it to something else and do further processing to get the results you want.  You never know though, you might be able to come up with some magic to do exactly what you want.    If you figure out how to do it I'd love to see an example!!

cnorborg‌

Yeah, just forget about all of my previous examples/explanation. (or the lack thereof)

Now, plain and simple, the mission is to count those lines in that table!!

*enter the sound of crickets chirping, pin drop from across the room, hair growing, etc...*

Any magic that comes about, will most likely not come from me... but, hopefully, someone will be able to fill in the void here.

When/if I get this figured out, unless someone else posts it here first, I will surely post my findings, and close this bad boy out.

Off, to another adventure in Google search land, I go...

Thanks again,

-Will

That got me thinking a bit and did a bit of research on XML in SQL databases.   A bit easier if the XML is actually stored in an XML datatype vs the nvarchar this field is stored in, but luckily a "CAST" seems to work. 

Try this, I think it actually works!!   Might need some tweaking, esp. changing <YOUR RULE NAME> to a specific rule, or removing that part of the query if you want a count of all violated rules...

SELECT  CAST(PCR.XmlResults AS XML).query('count(//Ps)'), PCR.RuleName, N.Caption, N.IP_Address FROM NCM_PolicyCacheResults PCR

  JOIN NCM_NodeProperties NP ON (NP.NodeID = PCR.NodeID)

  JOIN Nodes N ON (N.NodeID = NP.CoreNodeID)

WHERE (PCR.IsViolation = 1) AND (PCR.RuleName LIKE '%<YOUR RULE NAME>%')

GROUP BY N.NodeID, PCR.RuleName, N.Caption, N.IP_Address, PCR.IsViolation, PCR.XmlResults

ORDER BY N.Caption, PCR.RuleName

Kinda neat actually...

cnorborg‌

BY THE POWER OF GRAYSKULL... YOU DID IT!!!

I am now able to see the total count of occurrences per rule, for each device.

All that is left for me to do now, is research a little bit more to manage the format of the query results, and figure out a way to sort that count column.

Outstanding work!

Thank you very much for all of your assistance.

I will go ahead and mark this thread as "Answered", thanks to you.

-Will

cnorborg‌

I would like to thank you, once again, for your assistance with this "project".

My end result can be broken up into 3 separate parts/reports.

I have highlighted those 3 parts, shown in the image below, in case anyone should find a use for this in the future.

The RED Part:

This is the section that specifically reflects the query provided by cnorborg‌.

This query allows me to list all devices that are in violation of a specific policy rule, and then counts the number of times each of those devices violated the rule.

In our environment, a single device has the potential of 48 unique violations/occurrences of a single policy rule.

SELECT  CAST(PCR.XmlResults AS XML).query('count(//L)') AS NumProvisioned, ND.NodeID, ND.Caption, ND.IP_Address, PCR.ConfigID, PCR.NodeID
  FROM SWNPMDB.dbo.NCM_PolicyCacheResults PCR
  JOIN SWNPMDB.dbo.NCM_NodeProperties NP
  ON (NP.NodeID = PCR.NodeID)
  JOIN SWNPMDB.dbo.NodesData ND
  ON (ND.NodeID = NP.CoreNodeID)
  WHERE (PCR.RuleName = 'access profile BASIC') AND (PCR.IsViolation = 1)
--GROUP BY --N.NodeID, PCR.RuleName, N.Caption, N.IP_Address, PCR.IsViolation, PCR.XmlResults
ORDER BY ND.Caption ASC

The GREEN Part:

This section simply takes all of the individual totals from the the RED part, and combines them all together.

This allows us to see a total number of violations, across all of our devices, for a single/specific policy rule.

SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile BASIC', '')))/LEN('access profile BASIC')-1))OVER() as "BASIC"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile BASIC')
GROUP BY XmlResults

The BLUE part:

After I was able to get the first 2 parts working, I was fairly annoyed at how much wasted space there was on the screen.

Basically, I could not accept the size of each of the resources on the page, when all I really needed was a 1-6 digit number as each result.

Also, I have been working on a totally separate issue, in a different Thwack post, located here, Custom SQL Query, Filter List, Based On 2 Different Custom Poller Field Values.

dhanson provided a query to assist in completing my task. I have still not been able to get the query from that post to work exactly as needed, however, that query was able to provide me a means to accomplish a new goal for this task.

Having modified the query provided by dhanson, in the post referenced above, I only know that it is able to provide me what I need.  I have no idea if it is the best way to do what I am doing, but it is working for me, so, there you go...

To get a good idea of the amount of page space saved, in the picture above, with this query, I was able to combine the entire right half of the page (2 columns with 10 resources), into the resource outlined in BLUE.

WITH a AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile LITE', '')))/LEN('access profile LITE')-1))OVER() as LITE
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile LITE')
GROUP BY XmlResults
)
,
b AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile BASIC', '')))/LEN('access profile BASIC')-1))OVER() as "BASIC"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile BASIC')
GROUP BY XmlResults
)
,
c AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile BRONZE', '')))/LEN('access profile BRONZE')-1))OVER() as BRONZE
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile BRONZE')
GROUP BY XmlResults
)
,
d AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile 1BB1.5', '')))/LEN('access profile 1BB1.5')-1))OVER() as "1BB1.5"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile 1BB1.5')
GROUP BY XmlResults
)
,
e AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile 1BB5', '')))/LEN('access profile 1BB5')-1))OVER() as "1BB5"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile 1BB5')
GROUP BY XmlResults
)
,
f AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile 1BB8', '')))/LEN('access profile 1BB8')-1))OVER() as "1BB8"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile 1BB8')
GROUP BY XmlResults
)
,
g AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile 1BB12', '')))/LEN('access profile 1BB12')-1))OVER() as "1BB12"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile 1BB12')
GROUP BY XmlResults
)
,
h AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile BB1.5', '')))/LEN('access profile BB1.5')-1))OVER() as "BB1.5"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile BB1.5')
GROUP BY XmlResults
)
,
i AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile BB5', '')))/LEN('access profile BB5')-1))OVER() as "BB5"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile BB5')
GROUP BY XmlResults
)
,
j AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile BB8', '')))/LEN('access profile BB8')-1))OVER() as "BB8"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile BB8')
GROUP BY XmlResults
)
,
k AS
(
SELECT TOP 1 SUM(((LEN(XmlResults) - LEN(REPLACE(XmlResults, 'access profile BB12', '')))/LEN('access profile BB12')-1))OVER() as "BB12"
FROM SWNPMDB.dbo.NCM_PolicyCacheResults
WHERE ([IsViolation]=1) AND ([RuleName]='access profile BB12')
GROUP BY XmlResults
)
SELECT a.*,b.*,c.*,d.*,e.*,f.*,g.*,h.*,i.*,j.*,k.*
FROM a,b,c,d,e,f,g,h,i,j,k

So, yet again, I would like to thank both cnorborg‌ & dhanson‌ for their assistance, as I doubt I would have been able to get this far without their help.

Thank you!

-Will

Hello.

I am not familiar so well with SolarWinds and NCM and I strongly need your advice. I have absolutely the same task, to count lines in the table I get and I see there is a solution. BUT, I'm a newibe and I actually have no idea how to apply this solution..(

Could you help me, please? Where should I put this code and how can I get such a nice output after it. This is my situation. I have a rule and this output, and I want to get a number of violations. So, in my example I want to get '4', like:

NodeName = 4 interfaces.

Thank you in advance.