We started using Kaseya Software Manager module and i must say it's pretty neat! I've been working to put together a report for our customers (using BrightGauge) which proved problematic as there isn't a View in ksubscribers that showed everything I needed. So i wrote one. If anyone is looking for a query show customers what they are missing for 3rd party patching, here's a query. I'm also working on Patch History too, that's proving quite tricky.
SELECT newid() as 'id',
t.DisplayName as 'Display Name',
t.Status as 'Status',
t.ApprovalStatus 'Approval Status',
t.ApprovalStatusDate 'Approval Status Date',
t.AgentGuid as 'Agent Guid',
t.failedDate as 'Failed Date',
t.FailedMessage as 'Failed Message',
t.PatchInProgress as 'Patch In Progress',
t.ProductVersionID as 'Product Version ID',
vm.machname as 'Computer',
vm.groupname as 'Company',
pv.name as 'Application Name',
pv.FullVersion as 'Full Version',
pv.Impact as 'Impact',
pv.ReleaseDate as 'Release Date',
pv.DateCreated as 'Date Created',
pv.DateModified as 'Date Modified',
pv.CveCode as 'CVE Code',
pv.Description as 'Description',
pv.Hyperlink as 'Hyperlink',
pv.Identifier as 'Identifier',
pv.VendorId as 'VendorID',
pv.IsWithdrawn as 'Is Withdrawn',
pv.LanguageDomainRef as 'Language',
pv.IsRebootRequired as 'Reboot Required',
pv.IsSuperseded as 'Is Superseded',
pv.IsOsPatch as 'Is Operating System Patch',
v.Name as 'Product Vendor',
p.Name as 'Product Name',
p.RequireUninstallFirst as 'Requires Uninstall First'
FROM [SM].[UnappliedPatch] t
JOIN vMachine vm on vm.agentGuid = t.AgentGuid
JOIN [SM].[ProductVersion] pv on pv.id = t.ProductVersionId
JOIN [SM].[Vendor] v on pv.vendorid = v.Id
JOIN [SM].[Product] p on pv.ProductId = p.Id
This looks really cool. We too are trying to do some reporting for the Software Management module, but I have no idea where/what tables we need for list the vulnerabilities. Have you guys ran into that?
That query definitely gives you what you need . Are you a Brightgauge customer? If you run that up in Brightgauge you'll be able to see a beautifully formatted table, customer by customer of all the vulnerabilities and CVE scores attached, including 3rd party software.
Using Report Parts to familiarize yourself to customize your reports is key. The value and data you are looking for in Software Management will be in 'Current Vulnerabilities' under the SM tree.
Is it possible to list the number of vulnerabilities on a line, as opposed to listing each vulnerability?
You can try to use the COUNT feature on that specific line via the reporting function when creating your template.
How did you go with "Patch History" I'm also looking for this information.