[Spacewalk-list] spacewalk-report enhancement

[P.Cookson at bham.ac.uk]

Hi Michael

Thanks for your quick response, this looks encouraging. I really don't know SQL, though, so can you highlight exactly how to amend the report definition please? Also, how can you list available attribute names, like for System Group, as I need?

# cat /usr/share/spacewalk/reports/data/system-currency

synopsis:

        System currency list

description:

        Prints list of numbers of available erratas for each registered system

columns:

        system_id Server ID
        org_id  Organization ID which is server registered to
        name    Name of the server
        critical        Number of critical security updates available
        important Number of important security updates available
        moderate        Number of moderate importance security updates available
        low     Number of low importance security updates available
        bug     Number of bug fixes available
        enhancement     Number of enhancements available
        score Total system score

params:

        p_crit  java.sc_crit
        p_imp   java.sc_imp
        p_mod   java.sc_mod
        p_low   java.sc_low
        p_bug   java.sc_bug
        p_enh   java.sc_enh

sql:

        select system_id, org_id, name, critical, important, moderate, low, bug, enhancement,
                ((critical * :p_crit) + (important *  :p_imp) + (moderate * :p_mod) + (low * :p_low) + (bug * :p_bug) + (enhancement * :p_enh)) as score
        from (
        select S.id as system_id,
                S.org_id as org_id,
                S.name as name,
                sum(case when (substr(E.synopsis, 1, 1) = 'C' and E.advisory_type = 'Security Advisory') then 1 else 0 end) as critical,
                sum(case when (substr(E.synopsis, 1, 1) = 'I' and E.advisory_type = 'Security Advisory') then 1 else 0 end) as important,
                sum(case when (substr(E.synopsis, 1, 1) = 'M' and E.advisory_type = 'Security Advisory') then 1 else 0 end) as moderate,
                sum(case when (substr(E.synopsis, 1, 1) = 'L' and E.advisory_type = 'Security Advisory') then 1 else 0 end) as low,
                sum(case when E.advisory_type = 'Bug Fix Advisory' then 1 else 0 end) as bug,
                sum(case when E.advisory_type = 'Product Enhancement Advisory' then 1 else 0 end) as enhancement
        from rhnServer S left join
                (select distinct err.id, snc.server_id, err.synopsis, err.advisory_type from rhnErrata err join
                        rhnServerNeededCache SNC on err.id=SNC.errata_id) E on S.id=E.server_id
        group by S.id, S.org_id, name
        ) X
        -- where placeholder
        order by org_id, system_id

#

Regards
Phil

-----Original Message-----
From: spacewalk-list-bounces at redhat.com <spacewalk-list-bounces at redhat.com> On Behalf Of michael.mraka at redhat.com
Sent: 04 April 2019 10:28
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] spacewalk-report enhancement

P.Cookson at bham.ac.uk:
> Good morning
> 
> I really like the "spacewalk-report system-currency" report as it gives a really good idea of the patch status of each registered system. However, is there any way of getting a "System Groups" column added to this? We use System Groups to group systems by service and so this would help us produce a regular report that could be sent to each service Manager. Currently, we have to do this manually after running the report and putting the output in to Excel.
> 
> Only the "spacewalk-report inventory" report seems to have a Groups column but it lacks the patch type details of the "spacewalk-report system-currency" report.
> 
> Equally, if anyone knows of an alternative way of doing this, then that would be good too.

Hello Philip,

See definition of system-currency report in  /usr/share/spacewalk/reports/data/system-currency
and create your own modification.

> Regards
> 
> Philip Cookson

Regards,

--
Michael Mráka
System Management Engineering, Red Hat

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list