Do I need to create additional tables?

[jamestford]

Having trouble creating a query to summarize vulnerability data and display the affected hosts. So far I've been able to create a query that counts the number of vulnerabilities based on the plugin that the scanner used but cannot group the ip addresses by vulnerability. I'm guessing I may need to denormalize the data but cannot for the life of me figure this out, I've been banging my head against this for 8 hours now and really need some help.



Here is the the table layout

ID - randomly assigned by access
Plugin ID - plugin used to identify vulnerability
Risk - risk of vulnerability
Name - short description
Description - description of vulnerability
Host - host ip address
Protocol - protocol used
Port - port number where vulnerability was found
Plugin Output - specific vulnerability data for the host

Here is my query:

SELECT Results.[Plugin ID], Count(*) AS [Count], Results.Risk, Results.Name
FROM Results
WHERE (((Results.Risk)="Critical" Or (Results.Risk)="High"))
GROUP BY Results.[Plugin ID], Results.Risk, Results.Name
ORDER BY Results.Risk, Count(Results.[Plugin ID]) DESC;

This query does a great job of counting the number of vulnerabilities and displaying them by Criticality, however it does not associate the host ip address with the vulnerabilities, every time I try to associate the host ip address it messes up the count.

Any ideas?

[June7]

Perhaps if you posted some sample raw data and sample of desired output we could suggest a method. I don't know what you mean by 'associate the host ip address with the vulnerabilities'. There's already an association because the vulnerability and host ip are in same record.

How does this query display by Criticality? The grouping is on PluginID, Risk, Name - what is 'Criticality'?

BTW, advise no spaces in naming convention, nor reserved words as names.

[jamestford]

Thanks for the reply, here is an example of the type of query or report I'd like to build:

Plugin ID: 5881
Count: 3458
Risk: Critical
Name: VMware Security Updates for vCenter Server
Affected Hosts: 192.168.1.1, 192.168.1.3, 192.168.1.4, 192.168.1.5

I have a query that is able to quickly generate a list of plugins, counts, risk, and name fields, but cannot figure out how to create a column that aggregates the affected hosts.

Here is what some entries in the database look like:

ID: 1
Plugin ID: 5881
Risk: Critical
Name: VMware Security Updates for vCenter Server
Host: 192.168.1.1

ID: 2
Plugin ID: 5881
Risk: Critical
Name: VMware Security Updates for vCenter Server
Host: 192.168.1.2

ID: 3
Plugin ID: 5881
Risk: Critical
Name: VMware Security Updates for vCenter Server
Host: 192.168.1.3

ID:4
Plugin ID: 5881
Risk: Critical
Name: VMware Security Updates for vCenter Server
Host: 192.168.1.4

Here is my current query:

SELECT Results.[Plugin ID], Count(*) AS [Count], Results.Risk, Results.Name
FROM Results
WHERE (((Results.Risk)="Critical" Or (Results.Risk)="High"))
GROUP BY Results.[Plugin ID], Results.Risk, Results.Name
ORDER BY Results.Risk, Count(Results.[Plugin ID]) DESC;

[June7]

Was looking for a sample of the desired output. What do you want results to look like? Build a facsimile in Excel or even a table in the Advanced Post Editor.