Custom report by Source and Severity

Reporting
1

(imported topic written by SystemAdmin)

Hi,

Can anyone tell me how to generate a report by source and its severity.

For example, the output has to be like

Microsoft -100

critical -10

important-75

moderate-10

low-5

Thanks

2

(imported comment written by Lee Wei)

Here is an example using Session Relevance.

I did not put in additional filters to keep it simpler.

(it, multiplicity of it) of unique values of ( (

if (exists source of it) then (source of it) 

else (
"None")) & 
" - " & (

if (exists source severity of it) then (source severity of it) 

else (
"None")) ) of bes fixlets whose (display name of site of it = 
"Patches for Windows (English)")

Example output:

Microsoft - <Unspecified>, 992 Microsoft - Critical, 2549 Microsoft - Important, 2792 Microsoft - Low, 120 Microsoft - Moderate, 711 Microsoft - N/A, 176 None - None, 1 Summit Software - Critical, 1

Lee Wei

3

(imported comment written by SystemAdmin)

Thanks Lee

If I am correct, the below script will be used for to generate web report.

But I want to create a custom report from the SQL DB.

Thanks

4

(imported comment written by Lee Wei)

I have not accessed the data via SQL for a long time. There are nuances that make it tricky sometimes.

Here is one example statement.

Enterprise Security is the synonym for “Patches for Windows (English)”.

select   dbo.fn_ExtractField(
'Source', 0, P.Fields) as 
'Source', dbo.fn_ExtractField(
'Source Severity', 0, P.Fields) as 
'Source Severity' from SITENAMEMAP S, VERSIONS V, EXTERNAL_OBJECT_DEFS P where S.Sitename = P.Sitename AND P.IsFixlet = 1 AND P.Sitename = V.Sitename AND P.ID = V.ID AND V.IsFixlet = 1 AND P.Version = V.LatestVersion AND S.IsDeleted = 0 AND NOT P.ParentID = 1 AND dbo.fn_CheckIsFixlet( P.Fields ) = 1 AND S.UndecoratedSitename = 
'Enterprise Security'
5

(imported comment written by SystemAdmin)

Hi Lee,

Thanks for the reply.

This query gave me all the Fixlets and Tasks that has been taken care of already. What is the Database field I have to look for to get only open Tasks.

Thanks

6

(imported comment written by Lee Wei)

The SQL statement list all the Fixlets.

What do you mean by open tasks?

I suggest using the SOAP API and going against Web Reports.

Lee Wei

7

(imported comment written by SystemAdmin)

OpenFixlet- Applicable Computer count > 0

Thanks

8

What is “Summit Software” in your results?