I am trying to build post patching report using queries that should include at least below mentioned columns, I have already tried built-in report in Reports >> Security > Vulnerabilities "remediated vulnerabilities" and also Patching History by device that I got from the LANDesk community.
The issue with these reports is selecting 50 devices out of 1000 to run report against, I mean keep scrolling and selecting few devices out of many is not flexible, So I looked at "Patching History by device", there we have the option to select device groups instead of checking indivisual devices, now that's really good. However I want the report in Excel format, so I export it and after exporting it, it requires lot of excel related formatting like deleting empty rows, etc. and it does not have below mentioned 7 columns that I require at a minimum in my report.
1) Device Name
2) VUL ID
3) Patch Name
4) Patch Action - Success or Failed
5) Message (Repair done / failed etc.)
6) Severity (Critical, High, Medium etc.)
7) Action Date (What date patch installed or failed)
So I started exploring LANDesk queries, and built this query,
It is working fine and giving me correct number of records, however it does not have "Severity" and "VUL ID" that I want to include.
So I modified query this way:
I added Severity and ID from "Detected Patch and Compliance Definitions", this query is running, however giving me wrong number of patches installed, and many rows are getting duplicated with patch name.
it looks like: Windows6.1something.exe Critical
Is it because "Patch History" and "Detected Patch and Compliance Definitions" may be separate tables in SQL DB and they can not build any connection (joins) between each other, so records are getting duplicated. I am not sure.
Is it possible to fix it without using any SQL query, if not, can anyone write an example SQL query to guide me (as I am new to SQL code that is the reason I am preferring queries).