Hammer CLI CSV to report errata counts per host

Latest response

Is there are way to export all Content Hosts, adding in the installable errata counts by host for security, bug, and enhancement?

It seems like this ought to be simply built into the Satellite UI, but no such report export capability seems to exist (which is rather bizarre).

With a command like:

hammer csv content-hosts --export --file /tmp/reports.csv

I can get all kinds of content host information per host, except for the very information I need, installable errata counts by host.

Responses

not sure which version of satellite 6 you are on, but this is actually quite easy in 6.3.x

$  hammer --csv host errata list --host host.example.com
ID,Erratum ID,Type,Title,Installable
23703,RHBA-2018:0770,bugfix,"gtk3, gdm, gnome-shell, gnome-session update, new packages: wayland",true
24906,RHEA-2018:1771,enhancement,linux-firmware enhancement update,true
24705,RHSA-2018:1700,security,Important: procps-ng security update,true
24677,RHSA-2018:1629,security,Important: kernel security update,true
24584,RHEA-2018:1581,enhancement,microcode_ctl bug fix and enhancement update,true
24550,RHSA-2018:1453,security,Critical: dhcp security update,true
24506,RHEA-2018:1377,enhancement,ca-certificates enhancement update,true
24504,RHBA-2018:1401,bugfix,sos bug fix and enhancement update,true
24497,RHBA-2018:1383,bugfix,gcc bug fix update,true
24496,RHBA-2018:1375,bugfix,rsyslog bug fix update,true
24493,RHBA-2018:1388,bugfix,iptables bug fix update,true
24489,RHEA-2018:1376,enhancement,"nspr, nss-util, nss-softokn, and nss bug fix and enhancement update",true
24486,RHBA-2018:1398,bugfix,e2fsprogs bug fix update,true
24484,RHBA-2018:1389,bugfix,openldap bug fix update,true
24431,RHSA-2018:1318,security,"Important: kernel security, bug fix, and enhancement update",true
24432,RHBA-2018:1339,bugfix,tzdata enhancement update,true
24250,RHSA-2018:1200,security,Important: patch security update,true
24167,RHBA-2018:1143,bugfix,nfs-utils bug fix update,true

oh and you can further refine it

$  hammer --csv host errata list --host host.example.com --search "type=security"
ID,Erratum ID,Type,Title,Installable
24705,RHSA-2018:1700,security,Important: procps-ng security update,true
24677,RHSA-2018:1629,security,Important: kernel security update,true
24550,RHSA-2018:1453,security,Critical: dhcp security update,true
24431,RHSA-2018:1318,security,"Important: kernel security, bug fix, and enhancement update",true
24250,RHSA-2018:1200,security,Important: patch security update,true
24042,RHSA-2018:1062,security,"Important: kernel security, bug fix, and enhancement update",true
23924,RHSA-2018:0855,security,"Moderate: ntp security, bug fix, and enhancement update",true
23828,RHSA-2018:0913,security,"Low: policycoreutils security, bug fix, and enhancement update",true
23795,RHSA-2018:0980,security,"Low: openssh security, bug fix, and enhancement update",true
23784,RHSA-2018:0998,security,Moderate: openssl security and bug fix update,true
23754,RHSA-2018:0666,security,"Moderate: krb5 security, bug fix, and enhancement update",true
23684,RHSA-2018:0849,security,"Low: gcc security, bug fix, and enhancement update",true

I've seen that capability, however, this is not what I was looking for. It would be more like this kind of output:

Host,OS_Description,Security_Count,Bugfix_Count,Enhancment_Count
host1, RHEL 7.5, 5, 25, 2
host2, RHEL 7.5, 5, 22, 2
host3, RHEL 7.5, 4, 25, 2
host4, RHEL 7.5, 5, 25, 1

The purpose is quite simple, management wants a summary, not all of the details for every host. This information can be seen but not exported from the UI.

My misunderstanding. You're right that would be nice, but I don't think there's a way to do it, without scripting either the hammer output and looping over it to get your numericals or writing something in Python against the API to get it.

We opted for the latter, to generate executive metrics (a pretty graph :D) We store the numericals in a mysql db because its faster than querying Satellite. We run this job once a day that extracts all the errata info, host info, numerics, etc and dumps it into a mysql db that serves a custom reporting front-end.

I would be willing to submit a ticket against an RFE for this type of output from hammer though, at least a simple metric count of each type.

Maybe I should check out the API. I thought someone said it was REST based so I could try doing something similar as what you did. Thanks for the info.

I need same kind of report monthly basis to find out how many servers require patches like Bug-fix,Security,enhancement...etc

the hammer csv command can do this. You just have to create a custom report to do this. We cover how to do custom reports with hammer csv in Subscription-manager for the former Red Hat Network User: Part 12 - Subscription Reporting Tools

Firstly, edit /etc/hammer/cli.modules.d/csv.yml to contain the following:

:csv:
  :enable_module: true
  :columns:
    :content-hosts:
      :define:
        - :name: Security Errata
          :json:
            - content_facet_attributes
            - errata_counts
            - security
        - :name: Bug Fix Errata
          :json:
            - content_facet_attributes
            - errata_counts
            - bugfix
        - :name: Enhancement Errata
          :json:
            - content_facet_attributes
            - errata_counts
            - enhancement

The above defines three new reporting fields.

Next, run hammer csv specifying these fields (plus any others you want) to get your report

hammer csv content-hosts --export \
  --columns "Name,OS,Security Errata,Bug Fix Errata,Enhancement Errata" \
  --file custom.csv

And check your report:

cat custom.csv 
Name,OS,Security Errata,Bug Fix Errata,Enhancement Errata
host-118.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4
ic1.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4
ic2.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4
ic3.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4
ic4.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4
ic5.example.com,Red Hat Enterprise Linux Server 7.5,3,16,4
ral-db01.example.com,Red Hat Enterprise Linux Server 7.5,0,0,0
ral-db02.example.com,Red Hat Enterprise Linux Server 7.5,0,0,0
rh053.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4
virt-who-rhv-hypervisor1.example.com-1,,,,
virt-who-rhv-hypervisor2.example.com-1,,,,
virt-who-rhv-hypervisor3.example.com-1,,,,
virt-who-rhv-hypervisor4.example.com-1,,,,
virt-who-rhv-hypervisor5.example.com-3,,,,

To further customize this report, you can use search parameters( --search) to limit the number of hosts in the report (just like any other hammer command). Example: add --search "name ~ ic*" to limit this report to hosts whose hostnames start with ic

If you wish to add any more fields to the report which aren't natively supported (see hammer csv content-hosts --help for the default list), you can use any valid property of the host. You can get those via the API.

As an example, maybe I want to expand this report to also include the applicable and upgradeable package count. This is useful because you may have 3rd party RPMs which do not have errata metadata.

Firstly, lets query the host via the API

curl -sk \
   -u admin:[REDACTED] https://satellite.example.com/api/hosts/ic1.example.com  | json_reformat
    "content_facet_attributes": {
        "id": 10,
        "uuid": "0ed040d9-34ae-438e-9477-5d1efb44e03b",
        "content_view_id": 2,
        "content_view_name": "RHEL7_Base",
        "lifecycle_environment_id": 2,
        "lifecycle_environment_name": "Crash",
        "content_source_id": 1,
        "content_source_name": "satellite.example.com",
        "kickstart_repository_id": 16,
        "kickstart_repository_name": "Red Hat Enterprise Linux 7 Server Kickstart x86_64 7.5",
        "errata_counts": {
            "security": 9,
            "bugfix": 17,
            "enhancement": 4,
            "total": 30
        },
        "applicable_package_count": 406,
        "upgradable_package_count": 105,
        "content_view": {
            "id": 2,
            "name": "RHEL7_Base"
        },
        "lifecycle_environment": {
            "id": 2,
            "name": "Crash"
        },
        "content_source": {
            "id": 1,
            "name": "satellite.example.com",
            "url": "https://satellite.example.com:9090"
        },
        "kickstart_repository": {
            "id": 16,
            "name": "Red Hat Enterprise Linux 7 Server Kickstart x86_64 7.5"
        },
        "content_view_version": "2.0",
        "content_view_version_id": 3,
        "content_view_default?": false,
        "lifecycle_environment_library?": false,
        "katello_agent_installed": true
    },

We can see that the applicable_package_count and upgradable_package_count attributes are part of the content_facet_attributes property.

So lets (re)edit /etc/hammer/cli.modules.d/csv.yml again and add these two fields. When done, your file should look as such:

:csv:
  :enable_module: true
  :columns:
    :content-hosts:
      :define:
        - :name: Security Errata
          :json:
            - content_facet_attributes
            - errata_counts
            - security
        - :name: Bug Fix Errata
          :json:
            - content_facet_attributes
            - errata_counts
            - bugfix
        - :name: Enhancement Errata
          :json:
            - content_facet_attributes
            - errata_counts
            - enhancement
        - :name: Applicable Packages
          :json:
            - content_facet_attributes
            - applicable_package_count
        - :name: Upgradable Packages
          :json:
            - content_facet_attributes
            - upgradable_package_count

Run hammer csv again, specifying our custom fields:

hammer csv content-hosts --export \
   --columns "Name,OS,Security Errata,Bug Fix Errata,Enhancement Errata,Applicable Packages,Upgradable Packages" \
--file custom2.csv

And lastly, let's check our work:

cat custom2.csv 
Name,OS,Security Errata,Bug Fix Errata,Enhancement Errata,Applicable Packages,Upgradable Packages
host-118.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4,406,105
ic1.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4,406,105
ic2.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4,406,105
ic3.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4,406,105
ic4.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4,406,105
ic5.example.com,Red Hat Enterprise Linux Server 7.5,3,16,4,383,82
ral-db01.example.com,Red Hat Enterprise Linux Server 7.5,0,0,0,0,0
ral-db02.example.com,Red Hat Enterprise Linux Server 7.5,0,0,0,0,0
rh053.example.com,Red Hat Enterprise Linux Server 7.5,9,17,4,406,105
virt-who-rhv-hypervisor1.example.com-1,,,,,,
virt-who-rhv-hypervisor2.example.com-1,,,,,,
virt-who-rhv-hypervisor3.example.com-1,,,,,,
virt-who-rhv-hypervisor4.example.com-1,,,,,,
virt-who-rhv-hypervisor5.example.com-3,,,,,,

Hi Rich,

Not sure which hammer version you're using. But content-host no longer exists in hammer 0.13.1.1. So the above commands won't work. (or Sat 6.4)

The above commands do indeed work on Satellite 6.4. But hammer's CLI modules, options, and subcommands are positional, which makes this sometimes confusing. Let me explain.

In the example above, I use hammer csv content-hosts, which says

"Hammer, use the csv subcommand which itself has a subcommand to export content-hosts"

This is different from using hammer --csv content-hosts which says

"Hammer, use the content-hosts subcommand and format its output in CSV format"

They are not the same, (and yes, thats a little confusing) and as you mentioned, the latter does not exist since Satellite 6.3.

The --csv option is shorthand for --output csv.

Rich,

How can I exclude some application packages (java, docker,...) related errate ?

Post processing of the CSV.

This is a very handy post, perfect for customizing what you're reporting on. I'm running Satellite 6.5.2 and was trying to figure out which fields are available to the reporting engine....couldn't figure out where erratum.errata_id, etc, etc came from, and I can't even see it in the API of a content host. But this is far easier. If there's any way I can see more information around the reporting engine that would be handy.

Rather than try exporting the information using hammer, I decided to use the RestAPI and just grab all of information that way in a PowerShell script. I was then able to parse out the errata types and all of the host information I needed to report on.

Hello Brian,

Can I have a copy of your PowerShell script?

Regards,

Jan Gerrit Kootstra

The Powershell script is using integrations with Active Directory, I create computer account for all Red Hat server and store information like the owner (managedBy). Where you see look ups to AD, you could replace them with your own list or other means of storing the information. Note that the SatAdmin credential and all other credentials I store using Export-Clixml. The stored credentials are usable only by the account that stored them, however the Invoke-RestMethod uses Basic authorization, and required using the internal Satellite Admin account, which is annoying. Basically we're grabbing all of the host information from Satellite, and then processing it for what we need to report on and outputting an HTML page and sending an email with a web link to the owners that have missing errata.

Function Unblock-SelfSignedCert()
{
  Write-Verbose -Message 'Allowing self-signed certificates'
  if ([System.Net.ServicePointManager]::CertificatePolicy -notlike 'TrustAllCertsPolicy')
  {
    Add-Type -TypeDefinition @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@
    [System.Net.ServicePointManager]::CertificatePolicy = New-Object -TypeName TrustAllCertsPolicy
  }
}
Unblock-SelfSignedCert

$xxxxxCred = Import-Clixml -Path "C:\PowerShell\PATCH_ADMIN.xml"

#####Subject field for email
$subject = "RedHat Errata Report"
#####Users that will receive an email with the script results:
$Send_To = ""
#####These variables should not need modification
$smtp_server = "relay-xxxxx.us.xxxxx.com"
$smtp = New-Object net.Mail.SmtpClient($smtp_server)
$header = "This is an automated message, please do not reply.`n`n"
$body = "Red Hat Errata Report Notification`n`nYou are receiving this notification because you are the owner of one or more Red Hat VMs. The Errata status wepage has been updated at: `n`nhttp://errata.xxxxx.com`n`nIt is not necessary to take action unless you have a server listed where the OldestSecurity column shows a date over 90 days ago. Security Errata over 90 days old puts the server out of compliance with IT Security policy."


$email_struct = New-Object net.Mail.MailMessage
$email_struct.From = "powershell@xxxxx.com"
$email_struct.cc.Add("dl-xxxxxservices@xxxxx.com")
$email_struct.Subject = $subject
$email_struct.Body = $header + $body


$ServerURL = "https://sat6.xxxxx.com"
$KatelloURI = $ServerURL + "/katello/api/v2/"
$ForemanURI = $ServerURL + "/api/v2/"
$SatCred = Import-Clixml -Path "C:\Powershell\SatAdmin.xml"
$OrgName = "xxxxx"
$ErrataBasePath = "D:\wwwroot\Satellite_Errata\"
$HTMLReport = $ErrataBasePath + "ErrataReport.html"

$searchBase = "OU=RedHat,OU=Servers,DC=xxxxx,DC=com"
$redHat_ADServers = Get-ADComputer -Filter '*' -Properties '*' -Credential $xxxxxCred -SearchBase $searchBase

$method = 'GET'
$uri = $ForemanURI + "hosts?per_page=400"
$allSatHosts = (Invoke-RestMethod -Uri $uri -Method $method -Headers @{Authorization = "Basic $SatCred"}).results

$errataReport = @()
$managers = @()
foreach ($satHost in $allSatHosts)
{
    $redHatServer = Get-ADComputer $sathost.name.split('.')[0] -Properties *
    try
    {
        $redHatServerManagedBy = Get-ADUser $redHatServer.ManagedBy -Properties displayName,mail
    }
    catch
    {
        $redHatServer
    }


    $uri = $ForemanURI + "hosts/$($satHost.id)/errata?per_page=4000"

    try
    {
        $redHatServerErrata = Invoke-RestMethod -Uri $uri -Method $method -Headers @{Authorization = "Basic $SatCred"}
    }
    catch
    {
        Write-Host "Failed URI:"


        $uri
    }
    $findManager = $managers | Where-Object {$_ -eq $redHatServerManagedBy.DisplayName}
    if (!$findManager)
    {
        $managers += $redHatServerManagedBy.DisplayName
    }
    if ($redHatServerErrata.total -gt 0)
    {
        foreach ($errata in $redHatServerErrata.results)
        {
            $row = "" | Select-Object Host,ManagedBy,ErrataID,ErrataType,ErrataSeverity,ErrataReboot,ErrataIssuedDate,OS
            $row.Host = $redHatServer.Name
            $row.ManagedBy = $redHatServerManagedBy.DisplayName
            $row.ErrataID = $errata.errata_id
            $row.ErrataType = $errata.type
            $row.ErrataSeverity = $errata.severity
            $row.ErrataReboot = $errata.reboot_suggested
            $row.ErrataIssuedDate = $errata.issued
            $row.OS = $redHatServer.OperatingSystem
            $errataReport += $row
        }
    }
}
$date = (Get-Date).ToString('MM-dd-yyyy')
$errataReport | Export-Csv -Path ($ErrataBasePath + "RedHat-Servers-Errata-Report-Detail-$($date).csv") -NoTypeInformation
$allHostSummary = @()
foreach ($manager in $managers)
{
    $reportHosts = $errataReport | Where-Object {$_.ManagedBy -eq $manager}
    if ($reportHosts)
    {
        $hostSummary = @()
        foreach ($reportHost in $reportHosts)
        {
            $findHostInSummary = $null
            if ($hostSummary)
            {
                $findHostInSummary = $hostSummary | Where-Object {$_.Host -eq $reportHost.Host}
            }

            if (!$findHostInSummary)
            {
                $row = "" | Select-Object Host,ManagedBy,Security,Bugfix,Enhancement,Reboot,OS,OldestSecurity
                #$row.Index = $hostSummaryIndex
                $row.Host = $reportHost.Host
                $row.ManagedBy = $reportHost.ManagedBy
                $t = $reportHost.ManagedBy
                $owner = Get-ADUser -Filter {displayName -eq $t} -Properties mail
                if ($owner)
                {
                    $found = $email_struct.to | Where-Object {$_ -eq $owner.mail}
                    if (!$found)
                    {
                        $email_struct.to.add($owner.mail)
                    }
                }
                $row.Security = 0
                $row.Bugfix = 0
                $row.Enhancement = 0
                $row.Reboot = $false
                $row.OS = $ReportHost.OS
                $row.OldestSecurity = $date
                $hostSummary += $row
                #$hostSummaryIndex ++
            }

            $index = ([Collections.Generic.List[Object]]$hostSummary).FindIndex( {$args[0].Host -eq $reportHost.Host} )

            switch ($reportHost.ErrataType)
            {
                Security {
                    $hostSummary[$index].Security += 1
                    if ( ($reportHost.ErrataIssuedDate | Get-Date) -lt ($hostSummary[$index].OldestSecurity | Get-Date) )
                    {
                        $hostSummary[$index].OldestSecurity = ($reportHost.ErrataIssuedDate | Get-Date).ToString('MM-dd-yyyy')
                    }

                }
                Bugfix {
                    $hostSummary[$index].Bugfix += 1
                }
                Enhancement {
                    $hostSummary[$index].Enhancement += 1
                }
            }
            if (!$reportHost.Reboot -and $hostSummary[$index].Reboot -ne $true)
            {
                $hostSummary[$index].Reboot = $true
            }
        }
        $allHostSummary += $hostSummary
        $hostSummary | Export-Csv -Path ($ErrataBasePath + "\RedHat-Errata-Summary-$($manager)-$($date).csv") -NoTypeInformation
    }
}
$allHostSummary | Export-Csv -Path ($ErrataBasePath + "RedHat-Errata-Summary-All-$($date).csv") -NoTypeInformation

$outOfComplianceCount = 0
foreach ($reportHost in $allHostSummary)
{
    $span = New-TimeSpan -Start ($reportHost.OldestSecurity | Get-Date)
    if ($span.Days -gt 90)
    {
        $outOfComplianceCount ++
    }

}

$allSatHostsCount = $allSatHosts.Count
$allHostSummaryCount = $allHostSummary.Count
$serversUpToDateCount = $allSatHostsCount - $allHostSummaryCount
$percentageUpToDate = [math]::Round(($serversUpToDateCount / $allSatHostsCount) * 100)
$percentageNeedingErrata = [math]::Round(($allHostSummaryCount / $allSatHostsCount) * 100)
$percentageOutOfCompliance = [math]::Round(($outOfComplianceCount / $allSatHostsCount) * 100)

$resultSet = $allHostSummary | ConvertTo-Html -CssUri "table.css" -Title 'Errata Report' -Body "<h1>Red Hat Servers Needing Errata</h1>`n<h5>Updated: on $(Get-Date)</h5>`n<h5>Total Host Count: $($allSatHostsCount) `t Count Needing Errata: $($allHostSummaryCount) `t Percentage Needing Errata: $($percentageNeedingErrata)</h5>`n<h4>Count Out Of Compliance: $($outOfComplianceCount) `t Percentage Out Of Compliance: $($percentageOutOfCompliance)</h4>"
Remove-Item -Path $HTMLReport 
Add-Content $HTMLReport $resultSet

$smtp.Send($email_struct)

Is it possible to get a report with a list of the errata id's against each host. Without having to loop through each host and list errata. either using hammer or the API. Thanks.

The reporting engine shipped in Satellite 6.5 has a precanned report (the applicable_errata report) that does exactly this.

Hi Rich,

The reporting tool is great, but when I run it, the information is out-of-date.

We have Satellite 6.5. I ran the "Applicable errata" report with a list of approximately 150 hosts. The report shows lots of Erratum/Packages that we have already updated.

In contrast, the "Content Hosts" view (with those same 150 hosts) accurately reflects which updates are still outstanding.

In short, I need to figure out to to make the "Applicable errata" report match the "Content Hosts" view.

I have created a ticket for this issue.

Thanks for the inputs Rich, appreciate your tips.

Thanks for the response, I could have guessed it would be in ver 6.5. currently on 6.4, but have a change freeze.... So will update when I can.