backend/satellite_tools/reports.py | 124 ------------
backend/satellite_tools/reports/data/entitlements | 57 -----
backend/satellite_tools/reports/data/errata-list | 41 ----
backend/satellite_tools/reports/data/errata-systems | 38 ---
backend/satellite_tools/reports/data/inventory | 123 ------------
backend/satellite_tools/reports/data/users | 65 ------
backend/satellite_tools/reports/data/users-systems | 59 ------
backend/satellite_tools/spacewalk-report | 192 --------------------
backend/satellite_tools/spacewalk-report.sgml | 127 -------------
backend/spacewalk-backend.spec | 4
rel-eng/packages/spacewalk-reports | 1
reporting/reports.py | 124 ++++++++++++
reporting/reports/data/entitlements | 57 +++++
reporting/reports/data/errata-list | 41 ++++
reporting/reports/data/errata-systems | 38 +++
reporting/reports/data/inventory | 123 ++++++++++++
reporting/reports/data/users | 65 ++++++
reporting/reports/data/users-systems | 59 ++++++
reporting/spacewalk-report | 192 ++++++++++++++++++++
reporting/spacewalk-report.sgml | 127 +++++++++++++
reporting/spacewalk-reports.spec | 45 ++++
21 files changed, 872 insertions(+), 830 deletions(-)
New commits:
commit 8dfc84f67f9e9da72b746b6f4221ac51538d3cf6
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Mon Dec 14 13:23:58 2009 +0100
Automatic commit of package [spacewalk-reports] release [0.7.0-1].
diff --git a/rel-eng/packages/spacewalk-reports b/rel-eng/packages/spacewalk-reports
new file mode 100644
index 0000000..32927b2
--- /dev/null
+++ b/rel-eng/packages/spacewalk-reports
@@ -0,0 +1 @@
+0.7.0-1 reporting/
commit 6e3b9f87e0478591ad6827b97000dd99204f5556
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Mon Dec 14 13:22:20 2009 +0100
reporting: move reporting from spacewalk-backend to separate package
spacewalk-reports.
diff --git a/backend/satellite_tools/reports.py b/backend/satellite_tools/reports.py
deleted file mode 100644
index 868ec39..0000000
--- a/backend/satellite_tools/reports.py
+++ /dev/null
@@ -1,124 +0,0 @@
-
-import os
-import re
-
-REPORT_DEFINITIONS = "/usr/share/spacewalk/reports/data"
-
-def available_reports():
- return os.listdir(REPORT_DEFINITIONS)
-
-class report:
- def __init__(self, name):
- full_path = os.path.join(REPORT_DEFINITIONS, name)
- self.sql = None
- self.description = None
- self.synopsis = None
- self.columns = None
- self.column_indexes = None
- self.column_descriptions = None
- self.multival_column_names = {}
- self.multival_columns_reverted = {}
- self.multival_columns_stop = []
- self._load(full_path)
-
- def _load(self, full_path):
- try:
- fd = open(full_path, 'r')
- except (IOError):
- raise spacewalk_unknown_report
- tag = None
- value = ''
- re_comment = re.compile('^\s*#')
- re_tag_name = re.compile('^(\S+):\s*$')
-
- for line in fd:
- result = re_comment.match(line)
- if result != None:
- continue
-
- result = re_tag_name.match(line)
- if result != None:
- if tag != None:
- self._set(tag, value)
- tag = None
- value = ''
- tag = result.group(1)
- else:
- value += line
-
- if tag != None:
- self._set(tag, value)
-
- if self.multival_column_names != None:
- unknown_columns = []
-
- for c in self.multival_column_names:
- if c in self.column_indexes:
- c_id = self.column_indexes[c]
- v = self.multival_column_names[c]
- if v == None:
- self.multival_columns_stop.append(c_id)
- elif v in self.column_indexes:
- v_id = self.column_indexes[v]
- if v_id in self.multival_columns_reverted:
- self.multival_columns_reverted[v_id].append(c_id)
- else:
- self.multival_columns_reverted[v_id] = [ c_id ]
- else:
- unknown_columns.append(c)
- if len(unknown_columns) > 0:
- raise spacewalk_report_unknown_multival_column_exception(unknown_columns)
-
-
- def _set(self, tag, value):
- if tag == 'columns':
- self.columns = []
- self.column_indexes = {}
- self.column_descriptions = {}
- lines = filter(lambda x: x != '', re.split('\s*\n\s*', value))
- i = 0
- for l in lines:
- description = None
- try:
- ( c, description ) = re.split('\s+', l, 1)
- except:
- c = l
- self.columns.append(c)
- self.column_indexes[c] = i
- if description != None:
- self.column_descriptions[c] = description
- i = i + 1
- elif tag == 'multival_columns':
- # the multival_columns specifies either
- # a "stop" column, usually the first one,
- # or a pair of column names separated by colon,
- # where the first on is column which should be
- # joined together and the second one is column
- # whose value should be used to distinguish if
- # we still have the same entity or not.
- for l in filter(lambda x: x != '', re.split('\n', value)):
- m = re.match('^\s*(\S+?)(\s*:\s*(\S*)\s*)?$', l)
- if m == None:
- continue
- ( col, id_col ) = ( m.group(1), m.group(3) )
- if col != None:
- self.multival_column_names[col] = id_col
- elif tag == 'sql':
- self.sql = value
- elif tag == 'synopsis':
- self.synopsis = re.sub('^(\s*\n)+\s*|(\s*\n)+$', '', value)
- elif tag == 'description':
- self.description = re.sub('(?m)^\s*', ' ',
re.sub('^(\s*\n)+\s*|(\s*\n)+$', '', value))
- else:
- raise spacewalk_report_unknown_tag_exception(tag)
-
-
-class spacewalk_unknown_report(Exception):
- pass
-
-class spacewalk_report_unknown_tag_exception(Exception):
- pass
-
-class spacewalk_report_unknown_multival_column_exception(Exception):
- pass
-
diff --git a/backend/satellite_tools/reports/data/entitlements
b/backend/satellite_tools/reports/data/entitlements
deleted file mode 100755
index 515d06f..0000000
--- a/backend/satellite_tools/reports/data/entitlements
+++ /dev/null
@@ -1,57 +0,0 @@
-
-synopsis:
-
- Entitlement and channel list and usage
-
-description:
-
- For each organization, list of system entitlements and
- channels to which the organization is entitled, together
- with current usage.
-
-columns:
-
- organization_id Organization identifier
- organization Organization name
- entitlement_type 'system' or 'channel'
- entitlement System entitlement name or channel name
- used How many entitlements are currently used
- total The total number of entitlements
-
-multival_columns:
-
- organization_id
- entitlement_type
- entitlement
-
-sql:
-
- select organization_id,
- web_customer.name as organization,
- entitlement_type,
- entitlement,
- used,
- total
- from (
- select rhnprivatechannelfamily.org_id as organization_id,
- 'channel' as entitlement_type,
- rhnchannelfamily.name as entitlement,
- rhnprivatechannelfamily.current_members as used,
- rhnprivatechannelfamily.max_members as total
- from rhnchannelfamily, rhnprivatechannelfamily
- where rhnchannelfamily.id = rhnprivatechannelfamily.channel_family_id
- union all
- select rhnservergroup.org_id as organization_id,
- 'system' as entitlement_type,
- rhnservergrouptype.name as entitlement,
- rhnservergroup.current_members as used,
- rhnservergroup.max_members as total
- from rhnservergroup, rhnservergrouptype
- where rhnservergroup.group_type = rhnservergrouptype.id
- and rhnservergroup.max_members > 0
- ) entitlements, web_customer
- where entitlements.organization_id = web_customer.id
- order by entitlements.organization_id,
- case when entitlement_type = 'channel' then 1 else 0 end,
- entitlement
-
diff --git a/backend/satellite_tools/reports/data/errata-list
b/backend/satellite_tools/reports/data/errata-list
deleted file mode 100755
index 06a1ce0..0000000
--- a/backend/satellite_tools/reports/data/errata-list
+++ /dev/null
@@ -1,41 +0,0 @@
-
-synopsis:
-
- Errata out of compliance information - errata details
-
-description:
-
- List of erratas that are applicable to at least one registered
- system, together with basic info about the errata. Also see
- errata-systems report to get list of systems that are affected.
-
-columns:
-
- advisory Advisory / errata identifier
- type Advisory type (Enhancement, Bug Fix, Security)
- cve List of CVE names (Common Vulnerabilities and Exposures Identifiers) addressed by
the errata
- synopsis Synopsis of the errata
- systems_affected Number of systems to which this errata is applicable
-
-multival_columns:
-
- advisory
- cve : cve
-
-sql:
-
- select rhnErrata.advisory_name as advisory,
- rhnErrata.advisory_type as type,
- rhnCVE.name as cve,
- rhnErrata.synopsis,
- count(*) as systems_affected
- from rhnErrata, rhnServerNeededErrataCache, rhnErrataCVE, rhnCVE
- where rhnErrata.id = rhnServerNeededErrataCache.errata_id
- and rhnErrata.id = rhnErrataCVE.errata_id (+)
- and rhnErrataCVE.cve_id = rhnCVE.id (+)
- group by rhnErrata.id,
- rhnErrata.advisory_name,
- rhnErrata.advisory_type,
- rhnCVE.name,
- rhnErrata.synopsis
- order by rhnErrata.advisory_name, rhnCVE.name
diff --git a/backend/satellite_tools/reports/data/errata-systems
b/backend/satellite_tools/reports/data/errata-systems
deleted file mode 100755
index f40aa9f..0000000
--- a/backend/satellite_tools/reports/data/errata-systems
+++ /dev/null
@@ -1,38 +0,0 @@
-
-synopsis:
-
- Errata out of compliance information - erratas for systems
-
-description:
-
- List of applicable erratas and systems that are affected.
-
-columns:
-
- advisory Advisory / errata identifier
- server_id System identifier
- profile_name Profile name, as stored on server
- hostname Hostname, as reported by the system
- ip_address IP address, as reported by the system
-
-sql:
-
- select rhnErrata.advisory_name as advisory,
- rhnServerNeededErrataCache.server_id,
- rhnServer.name as profile_name,
- rhnservernetwork.hostname, trim(rhnservernetwork.ipaddr) as ip_address
- from rhnErrata, rhnServerNeededErrataCache, rhnServer,
- (
- select server_id, hostname, ipaddr
- from rhnservernetwork
- where id in (
- select min(id)
- from rhnservernetwork
- group by server_id
- )
- ) rhnservernetwork
- where rhnErrata.id = rhnServerNeededErrataCache.errata_id
- and rhnServerNeededErrataCache.server_id = rhnServer.id
- and rhnServerNeededErrataCache.server_id = rhnservernetwork.server_id (+)
- order by rhnErrata.advisory_name, rhnServerNeededErrataCache.server_id
-
diff --git a/backend/satellite_tools/reports/data/inventory
b/backend/satellite_tools/reports/data/inventory
deleted file mode 100755
index 7662898..0000000
--- a/backend/satellite_tools/reports/data/inventory
+++ /dev/null
@@ -1,123 +0,0 @@
-
-synopsis:
-
- Inventory report
-
-description:
-
- List of systems registered to the server, together with
- hardware and software information.
-
-columns:
-
- server_id System identifier
- profile_name Profile name, as stored on server
- hostname Hostname, as reported by the system
- ip_address IP address, as reported by the system
- registered_by User under which the system is registered
- registration_time Registration time
- last_checkin_time Last checkin time
- kernel_version Kernel version, as reported by the system
- packages_out_of_date Number of packages that can be upgraded
- errata_out_of_date Number of erratas that can be applied
- software_channel List of channels to which the system is registered
- configuration_channel List of configuration channels
- entitlements List of entitlements
- system_group List of system group to which the system belongs
- organization Organization name
- virtual_host System identifier of host, if system is a guest
- architecture Architecture of the system
- hardware Hardware information
-
-multival_columns:
-
- server_id
- software_channel : software_channel
- configuration_channel : configuration_channel
- entitlements : entitlements
- system_group : system_group
- virtual_host : virtual_host
-
-sql:
-
- select rhnserver.id as server_id, rhnserver.name as profile_name,
- rhnservernetwork.hostname, trim(rhnservernetwork.ipaddr) as ip_address,
- ( select login from web_contact where rhnserver.creator_id = web_contact.id ) as
registered_by,
- to_char(rhnserver.created, 'YYYY-MM-DD HH24:MI:SS') as registration_time,
- ( select to_char(checkin, 'YYYY-MM-DD HH24:MI:SS') from rhnserverinfo where
rhnserver.id = server_id ) as last_checkin_time,
- running_kernel as kernel_version,
- ( select count(distinct rhnpackage.name_id) from rhnpackage,
rhnserverneededpackagecache
- where rhnserver.id = rhnserverneededpackagecache.server_id
- and rhnserverneededpackagecache.package_id = rhnpackage.id ) as
packages_out_of_date,
- ( select count(*) from rhnServerNeededErrataCache where rhnserver.id = server_id ) as
errata_out_of_date,
- rhnchannel.name as software_channel,
- rhnconfigchannel.name as configuration_channel,
- entitlement_data.name as entitlements,
- group_data.name as system_group,
- (
- select name from web_customer where rhnserver.org_id = web_customer.id
- ) as organization,
- virtual_data.host_system_id as virtual_host,
- (
- select rhnserverarch.name from rhnserverarch where rhnserver.server_arch_id =
rhnserverarch.id
- ) as architecture,
- (
- (
- select count(*) || ' CPUs' from rhncpu where rhnserver.id = rhncpu.server_id
- ) || network_data.col
- ) as hardware
- from rhnserver,
- (
- select server_id, hostname, ipaddr
- from rhnservernetwork
- where id in (
- select min(id)
- from rhnservernetwork
- group by server_id
- )
- ) rhnservernetwork,
- rhnserverchannel, rhnchannel,
- rhnserverconfigchannel, rhnconfigchannel,
- (
- select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id,
rhnservergroup.name
- from rhnservergroupmembers, rhnservergroup
- where rhnservergroupmembers.server_group_id = rhnservergroup.id (+)
- and rhnservergroup.group_type is not null
- ) entitlement_data,
- (
- select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id,
rhnservergroup.name
- from rhnservergroupmembers, rhnservergroup
- where rhnservergroupmembers.server_group_id = rhnservergroup.id (+)
- and rhnservergroup.group_type is null
- ) group_data,
- (
- select host_system_id, virtual_system_id
- from rhnvirtualinstance
- where host_system_id is not null
- ) virtual_data,
- (
- select server_id, max(sys_connect_by_path(val, '; ')) col
- from (
- select server_id,
- name||' '||ip_addr||'/'||netmask||' '||hw_addr val,
- row_number() over (partition by server_id order by name, rowid) rn
- from rhnservernetinterface
- )
- start with rn = 1
- connect by prior rn = rn - 1
- and prior server_id = server_id
- group by server_id
- ) network_data
- where rhnserver.id = rhnservernetwork.server_id (+)
- and rhnserver.id = rhnserverchannel.server_id (+)
- and rhnserverchannel.channel_id = rhnchannel.id (+)
- and rhnserver.id = rhnserverconfigchannel.server_id (+)
- and rhnserverconfigchannel.config_channel_id = rhnconfigchannel.id (+)
- and rhnserver.id = entitlement_data.server_id (+)
- and rhnserver.id = group_data.server_id (+)
- and rhnserver.id = virtual_data.virtual_system_id (+)
- and rhnserver.id = network_data.server_id (+)
- order by server_id, rhnchannel.parent_channel nulls first, rhnchannel.id,
- rhnconfigchannel.id, entitlement_data.server_group_id, group_data.server_group_id,
- virtual_data.host_system_id
-
diff --git a/backend/satellite_tools/reports/data/users
b/backend/satellite_tools/reports/data/users
deleted file mode 100755
index 0a44b02..0000000
--- a/backend/satellite_tools/reports/data/users
+++ /dev/null
@@ -1,65 +0,0 @@
-
-synopsis:
-
- Users in the system
-
-description:
-
- List of all users for all organizations, with their details
- and roles.
-
-columns:
-
- organization_id Organization identifier
- organization Organization name
- user_id Internal user id
- username User name / login
- last_name Last name
- first_name First name(s)
- position Position of the user
- email Email address
- role Roles assigned to the user
- creation_time Date and time when the user was created
- last_login_time When the user last accessed the system
- active String enabled or disabled
-
-multival_columns:
-
- organization_id
- user_id
- role : role
-
-sql:
-
- select web_contact.org_id as organization_id,
- web_customer.name as organization,
- web_contact.id as user_id,
- login as username,
- web_user_personal_info.last_name,
- web_user_personal_info.first_names as first_name,
- web_user_personal_info.title as position,
- web_user_personal_info.email,
- rhnUserGroupType.name as role,
- to_char(web_contact.created, 'YYYY-MM-DD HH24:MI:SS') as creation_time,
- to_char(rhnUserInfo.last_logged_in, 'YYYY-MM-DD HH24:MI:SS') as
last_login_time,
- nvl( ( select distinct wccs.label
- from rhnWebContactChangeLog wccl, rhnWebContactChangeState wccs
- where wccl.change_state_id = wccs.id
- and wccl.web_contact_id = web_contact.id
- and wccl.date_completed = (
- select max(wccl_exists.date_completed)
- from rhnWebContactChangeLog wccl_exists
- where wccl.web_contact_id =
wccl_exists.web_contact_id
- )
- ),'enabled') as active
- from web_contact, web_customer, web_user_personal_info,
- rhnUserGroupMembers, rhnUserGroup, rhnUserGroupType,
- rhnUserInfo
- where web_contact.org_id = web_customer.id
- and web_contact.id = web_user_personal_info.web_user_id
- and web_contact.id = rhnUserGroupMembers.user_id (+)
- and rhnUserGroupMembers.user_group_id = rhnUserGroup.id (+)
- and rhnUserGroup.group_type = rhnUserGroupType.id (+)
- and web_contact.id = rhnUserInfo.user_id
- order by web_customer.id, web_contact.id
-
diff --git a/backend/satellite_tools/reports/data/users-systems
b/backend/satellite_tools/reports/data/users-systems
deleted file mode 100755
index 94234cd..0000000
--- a/backend/satellite_tools/reports/data/users-systems
+++ /dev/null
@@ -1,59 +0,0 @@
-
-synopsis:
-
- Systems administered by individual users
-
-description:
-
- List of systems that users can administer.
-
-columns:
-
- organization_id Organization identifier
- user_id Internal user id
- username User name / login
- server_id System identifier
- group Group through the user has access to the system
- admin_access 1 if the user has access by being org administrator
-
-multival_columns:
-
- organization_id
- user_id
- server_id
- group : group
-
-# adapted query available_to_uid
-sql:
-
- select organization_id, web_contact_adm.user_id, username, server_id, group_name as
"GROUP", admin_access
- from (
- select web_contact.org_id as organization_id,
- web_contact.id as user_id,
- login as username,
- case when web_contact_admin.user_id is not null then 1 else null end as admin_access
- from web_contact, (
- select rhnUserGroupMembers.user_id
- from rhnUserGroupMembers, rhnUserGroup, rhnUserGroupType
- where rhnUserGroupMembers.user_group_id = rhnUserGroup.id
- and rhnUserGroup.group_type = rhnUserGroupType.id
- and rhnUserGroupType.label = 'org_admin'
- ) web_contact_admin
- where web_contact.id = web_contact_admin.user_id (+)
- ) web_contact_adm, (
- select 1 as is_admin, web_contact.id as user_id, rhnServer.id as server_id, null as
group_name
- from web_contact, rhnServer
- where web_contact.org_id = rhnServer.org_id
- union all
- select 0 as is_admin,
- rhnUserServerGroupPerms.user_id, rhnServerGroupMembers.server_id, rhnServerGroup.name
as group_name
- from rhnUserServerGroupPerms, rhnServerGroupMembers, rhnServerGroup
- where rhnUserServerGroupPerms.server_group_id = rhnServerGroupMembers.server_group_id
- and rhnUserServerGroupPerms.server_group_id = rhnServerGroup.id
- ) rhn_contact_server_group
- where
- nvl(web_contact_adm.admin_access, 0) = rhn_contact_server_group.is_admin (+)
- and web_contact_adm.user_id = rhn_contact_server_group.user_id (+)
- and rhn_contact_server_group.server_id is not null
- order by organization_id, user_id, server_id
-
diff --git a/backend/satellite_tools/spacewalk-report
b/backend/satellite_tools/spacewalk-report
deleted file mode 100755
index cd1be9e..0000000
--- a/backend/satellite_tools/spacewalk-report
+++ /dev/null
@@ -1,192 +0,0 @@
-#!/usr/bin/python -u
-#
-# Copyright (c) 2008 Red Hat, Inc.
-#
-# This software is licensed to you under the GNU General Public License,
-# version 2 (GPLv2). There is NO WARRANTY for this software, express or
-# implied, including the implied warranties of MERCHANTABILITY or FITNESS
-# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 # along with this
software; if not, see
-#
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
-#
-# Red Hat trademarks are not licensed under GPLv2. No permission is
-# granted to use or replicate Red Hat trademarks that are incorporated
-# in this software or its documentation.
-#
-
-import sys
-def systemExit(code, msgs=None):
- """ exit with message and code """
- if msgs:
- if type(msgs) not in [type([]), type(())]:
- msgs = (msgs, )
- for msg in msgs:
- sys.stderr.write(str(msg)+'\n')
- sys.exit(code)
-
-try:
- import os
- import csv
- from optparse import Option, OptionParser
-except KeyboardInterrupt:
- systemExit(-1, "\nUser interrupted process.")
-
-
-_LIBPATH = "/usr/share/rhn"
-# add to the path if need be
-if _LIBPATH not in sys.path:
- sys.path.append(_LIBPATH)
-
-sys.path.append('/usr/share/spacewalk')
-import reports
-
-try:
- from server import rhnSQL
- from common import initCFG
-except KeyboardInterrupt:
- systemExit(-1, "\nUser interrupted process.")
-
-
-def processCommandline():
- """ process the commandline """
- optionsTable = [
- Option('--multival-on-rows', action='store_true',
dest='multivalonrows',
- help='if there are multiple values for certain field, repeat the rows'),
- Option('--multival-separator', action='store',
dest='multivalseparator',
- default=';',
- help='if there are multiple values for certain field, separate them with this
string' \
- ' (unless --multival-on-rows)'),
- Option('--db', action='store', type="string",
- help='alternative database string (username/password@sid);' \
- + ' default is default_db from /etc/rhn/rhn.conf'),
- Option('--info', action='store_true',
- help='print synopsis of the report in the list of report, or description of
individual reports specified'),
- Option('--list-fields', action='store_true',
- dest='listfields',
- help='list fields of the report instead of running the report'),
- Option('--list-fields-info', action='store_true',
- dest='listfieldsinfo',
- help='as --list-fields but also prints description for each column'),
- ]
-
- optionParser = OptionParser( \
- usage="usage: %s [options] [report_name]" % sys.argv[0], \
- option_list=optionsTable)
- options, args = optionParser.parse_args()
-
- sys.argv[1:] = args
-
- return options
-
-if __name__ == '__main__':
- options = processCommandline()
-
- try:
- if len(sys.argv) > 2:
- systemExit(-5, 'Only one report name expected.')
- if len(sys.argv) > 1:
- report_name = sys.argv[1]
-
- try:
- report = reports.report(report_name)
- except(reports.spacewalk_unknown_report):
- systemExit(-4, 'Unknown report [%s].' % report_name)
-
- need_exit = None
- if options.info:
- if report.synopsis != None:
- print report.synopsis
- else:
- print "No synopsis for report %s." % report_name
- if report.description != None:
- print
- print report.description
- need_exit = True
-
- if options.listfields or options.listfieldsinfo:
- if options.info:
- print
- print "Fields in the report:"
- print
-
- for c in report.columns:
- text = c
- if options.info:
- text = " %s" % c
- if options.listfieldsinfo and report.column_descriptions.has_key(c) :
- text = "%s: %s" % ( text, report.column_descriptions[c] )
- print text
- need_exit = True
-
- if need_exit:
- sys.exit(0)
-
- initCFG('server')
- rhnSQL.initDB(options.db)
-
- writer = csv.writer(sys.stdout)
-
- h = rhnSQL.prepare(report.sql)
- h.execute()
-
- db_columns = map(lambda x: x[0].lower(), h.description)
- if db_columns != report.columns:
- systemExit(-3, \
- "Columns in report spec and in the database do not match:\nexpected %s\n
got %s" % ( report.columns, db_columns ))
- writer.writerow(report.columns)
-
- row = h.fetchone()
- prevrow = None
- outrow = None
- multival_dupes = {}
- while row != None:
- if options.multivalonrows or not report.multival_column_names.keys():
- writer.writerow(row)
- row = h.fetchone()
- continue
-
- if outrow != None:
- for m in report.multival_columns_stop:
- if prevrow[m] != row[m]:
- writer.writerow(outrow)
- outrow = None
- break
-
- if outrow != None:
- for m in report.multival_columns_reverted.keys():
- if prevrow[m] != row[m]:
- if not m in multival_dupes:
- multival_dupes[m] = {}
- if not row[m] in multival_dupes[m]:
- outrow[m] = str(outrow[m]) + options.multivalseparator + str(row[m])
- multival_dupes[m][row[m]] = 1
- break
-
- if outrow == None:
- outrow = [ x for x in row ]
- multival_dupes = {}
-
- prevrow = row
- row = h.fetchone()
-
- if outrow != None:
- writer.writerow(outrow)
- else:
- for report_name in reports.available_reports():
- if options.info:
- synopsis = ''
- try:
- report = reports.report(report_name)
- synopsis = report.synopsis
- except:
- None
- print "%s: %s" % ( report_name, synopsis )
- else:
- print report_name
-
- except KeyboardInterrupt:
- systemExit(-1, "\nUser interrupted process.")
- except (rhnSQL.SQLError, rhnSQL.SQLSchemaError, rhnSQL.SQLConnectError), e:
- # really a stub for better exception handling in the future.
- sys.stderr.write("SQL error occurred, traceback follows...\n")
- raise
-
diff --git a/backend/satellite_tools/spacewalk-report.sgml
b/backend/satellite_tools/spacewalk-report.sgml
deleted file mode 100644
index d792336..0000000
--- a/backend/satellite_tools/spacewalk-report.sgml
+++ /dev/null
@@ -1,127 +0,0 @@
-<!DOCTYPE refentry PUBLIC "-//OASIS//DTD DocBook V3.1//EN">
-
-<refentry>
-
-<RefMeta>
-<RefEntryTitle>spacewalk-report</RefEntryTitle><manvolnum>8</manvolnum>
-</RefMeta>
-
-<RefNameDiv>
-<RefName><command>spacewalk-report</command></RefName>
-<RefPurpose>
-Generate reports from Spacewalk server in csv format
-</RefPurpose>
-</RefNameDiv>
-
-<RefSynopsisDiv>
-<Synopsis>
- <cmdsynopsis>
- <command>spacewalk-report</command>
-
<arg>--db=<replaceable>connect_string</replaceable></arg>
- <arg>--info</arg>
- </cmdsynopsis>
- <cmdsynopsis>
- <command>spacewalk-report</command>
- <sbr />
- <group>
- <arg>--multival-on-rows</arg>
-
<arg>--multival-separator=<replaceable>separator-string</replaceable></arg>
- </group>
- <sbr />
-
<arg>--db=<replaceable>connect-string</replaceable></arg>
- <sbr />
- <arg>--info</arg>
- <sbr />
-
<group><arg>--list-fields</arg><arg>--list-fields-info</arg></group>
- <sbr />
- <arg
choice='plain'><replaceable>report-name</replaceable></arg>
- </cmdsynopsis>
-</Synopsis>
-</RefSynopsisDiv>
-
-<RefSect1><Title>Description</Title>
-<para>
- The <command>spacewalk-report</command> command can be used to
- generate canned reports from Spacewalk server database. The report
- which will be generated is selected by specifying its name as
- a parameter to <command>spacewalk-report</command>.
-</para>
-
-<para>
- If the report name is not specified, list of available report
- names is printed, one name per line.
-</para>
-
-<RefSect1><Title>Options</Title>
-<variablelist>
- <varlistentry>
- <term>--info</term>
- <listitem>
- <para>When listing available reports, also print
- synopsis for each report.</para>
-
- <para>When used with report name specified, print
- synopsis and description for the report, instead
- of running the report.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term>--multival-on-rows</term>
- <listitem>
- <para>If certain field can have multiple values, you can
- use this option to have records for the entity repeated
- in output in the form of cartesian product. By default,
- those multiple values are presented on one row in one
- field, separated by semicolon.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
-
<term>--multival-separator=<replaceable>separator-string</replaceable></term>
- <listitem>
- <para>If multiple values are output on one row (the
- default), this option can change the string which
- separates multiple values in one field. The default is
- semicolon.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
-
<term>--db=<replaceable>connect-string</replaceable></term>
- <listitem>
- <para>Database connect string in the form of
- <replaceable>username/password@dbname</replaceable> can be
- specified to generated report from user-defined database.
- By default, connect string from default_db option from
- /etc/rhn/rhn.conf is used.</para>
- <para>This option should be considered obsoleted and may
- not be available in next versions of
- <command>spacewalk-report</command>.
- </listitem>
- </varlistentry>
- <varlistentry>
- <term>--list-fields</term>
- <listitem>
- <para>Instead of running the report, shows fields that the
- report has, one field name per line.</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term>--list-fields-info</term>
- <listitem>
- <para>Instead of running the report, shows fields that the
- report has, one field name per line, and a description of
- that field.</para>
- </listitem>
- </varlistentry>
-</variablelist>
-</RefSect1>
-
-<RefSect1><Title>Authors</Title>
-<simplelist>
- <member>Jan Pazdziora</member>
-</simplelist>
-</RefSect1>
-</RefEntry>
-
-<!--
-vim: sw=4
--->
diff --git a/backend/spacewalk-backend.spec b/backend/spacewalk-backend.spec
index d788672..639bfea 100644
--- a/backend/spacewalk-backend.spec
+++ b/backend/spacewalk-backend.spec
@@ -522,7 +522,6 @@ rm -f %{rhnconf}/rhnSecret.py*
%attr(755,root,root) %{_bindir}/rhn-ssl-dbstore
%attr(755,root,root) %{_bindir}/satellite-sync
%attr(755,root,root) %{_bindir}/spacewalk-debug
-%attr(755,root,root) %{_bindir}/spacewalk-report
%attr(755,root,root) %{_bindir}/rhn-satellite-exporter
%attr(755,root,root) %{_bindir}/update-packages
%attr(755,root,root) %{_bindir}/spacewalk-repo-sync
@@ -559,8 +558,6 @@ rm -f %{rhnconf}/rhnSecret.py*
%attr(770,root,apache) %dir %{_var}/log/rhn/reposync
%{rhnroot}/satellite_tools/repo_plugins/__init__.py*
%{rhnroot}/satellite_tools/repo_plugins/yum_src.py*
-%{_prefix}/share/spacewalk/reports.py*
-%{_prefix}/share/spacewalk/reports/data/*
%config %attr(644,root,apache) %{rhnconf}/default/rhn_server_iss.conf
%{_mandir}/man8/rhn-satellite-exporter.8*
%{_mandir}/man8/rhn-charsets.8*
@@ -571,7 +568,6 @@ rm -f %{rhnconf}/rhnSecret.py*
%{_mandir}/man8/rhn-schema-stats.8*
%{_mandir}/man8/satellite-sync.8*
%{_mandir}/man8/spacewalk-debug.8*
-%{_mandir}/man8/spacewalk-report.8*
%{_mandir}/man8/satpasswd.8*
%{_mandir}/man8/satwho.8*
diff --git a/reporting/reports.py b/reporting/reports.py
new file mode 100644
index 0000000..868ec39
--- /dev/null
+++ b/reporting/reports.py
@@ -0,0 +1,124 @@
+
+import os
+import re
+
+REPORT_DEFINITIONS = "/usr/share/spacewalk/reports/data"
+
+def available_reports():
+ return os.listdir(REPORT_DEFINITIONS)
+
+class report:
+ def __init__(self, name):
+ full_path = os.path.join(REPORT_DEFINITIONS, name)
+ self.sql = None
+ self.description = None
+ self.synopsis = None
+ self.columns = None
+ self.column_indexes = None
+ self.column_descriptions = None
+ self.multival_column_names = {}
+ self.multival_columns_reverted = {}
+ self.multival_columns_stop = []
+ self._load(full_path)
+
+ def _load(self, full_path):
+ try:
+ fd = open(full_path, 'r')
+ except (IOError):
+ raise spacewalk_unknown_report
+ tag = None
+ value = ''
+ re_comment = re.compile('^\s*#')
+ re_tag_name = re.compile('^(\S+):\s*$')
+
+ for line in fd:
+ result = re_comment.match(line)
+ if result != None:
+ continue
+
+ result = re_tag_name.match(line)
+ if result != None:
+ if tag != None:
+ self._set(tag, value)
+ tag = None
+ value = ''
+ tag = result.group(1)
+ else:
+ value += line
+
+ if tag != None:
+ self._set(tag, value)
+
+ if self.multival_column_names != None:
+ unknown_columns = []
+
+ for c in self.multival_column_names:
+ if c in self.column_indexes:
+ c_id = self.column_indexes[c]
+ v = self.multival_column_names[c]
+ if v == None:
+ self.multival_columns_stop.append(c_id)
+ elif v in self.column_indexes:
+ v_id = self.column_indexes[v]
+ if v_id in self.multival_columns_reverted:
+ self.multival_columns_reverted[v_id].append(c_id)
+ else:
+ self.multival_columns_reverted[v_id] = [ c_id ]
+ else:
+ unknown_columns.append(c)
+ if len(unknown_columns) > 0:
+ raise spacewalk_report_unknown_multival_column_exception(unknown_columns)
+
+
+ def _set(self, tag, value):
+ if tag == 'columns':
+ self.columns = []
+ self.column_indexes = {}
+ self.column_descriptions = {}
+ lines = filter(lambda x: x != '', re.split('\s*\n\s*', value))
+ i = 0
+ for l in lines:
+ description = None
+ try:
+ ( c, description ) = re.split('\s+', l, 1)
+ except:
+ c = l
+ self.columns.append(c)
+ self.column_indexes[c] = i
+ if description != None:
+ self.column_descriptions[c] = description
+ i = i + 1
+ elif tag == 'multival_columns':
+ # the multival_columns specifies either
+ # a "stop" column, usually the first one,
+ # or a pair of column names separated by colon,
+ # where the first on is column which should be
+ # joined together and the second one is column
+ # whose value should be used to distinguish if
+ # we still have the same entity or not.
+ for l in filter(lambda x: x != '', re.split('\n', value)):
+ m = re.match('^\s*(\S+?)(\s*:\s*(\S*)\s*)?$', l)
+ if m == None:
+ continue
+ ( col, id_col ) = ( m.group(1), m.group(3) )
+ if col != None:
+ self.multival_column_names[col] = id_col
+ elif tag == 'sql':
+ self.sql = value
+ elif tag == 'synopsis':
+ self.synopsis = re.sub('^(\s*\n)+\s*|(\s*\n)+$', '', value)
+ elif tag == 'description':
+ self.description = re.sub('(?m)^\s*', ' ',
re.sub('^(\s*\n)+\s*|(\s*\n)+$', '', value))
+ else:
+ raise spacewalk_report_unknown_tag_exception(tag)
+
+
+class spacewalk_unknown_report(Exception):
+ pass
+
+class spacewalk_report_unknown_tag_exception(Exception):
+ pass
+
+class spacewalk_report_unknown_multival_column_exception(Exception):
+ pass
+
diff --git a/reporting/reports/data/entitlements b/reporting/reports/data/entitlements
new file mode 100755
index 0000000..515d06f
--- /dev/null
+++ b/reporting/reports/data/entitlements
@@ -0,0 +1,57 @@
+
+synopsis:
+
+ Entitlement and channel list and usage
+
+description:
+
+ For each organization, list of system entitlements and
+ channels to which the organization is entitled, together
+ with current usage.
+
+columns:
+
+ organization_id Organization identifier
+ organization Organization name
+ entitlement_type 'system' or 'channel'
+ entitlement System entitlement name or channel name
+ used How many entitlements are currently used
+ total The total number of entitlements
+
+multival_columns:
+
+ organization_id
+ entitlement_type
+ entitlement
+
+sql:
+
+ select organization_id,
+ web_customer.name as organization,
+ entitlement_type,
+ entitlement,
+ used,
+ total
+ from (
+ select rhnprivatechannelfamily.org_id as organization_id,
+ 'channel' as entitlement_type,
+ rhnchannelfamily.name as entitlement,
+ rhnprivatechannelfamily.current_members as used,
+ rhnprivatechannelfamily.max_members as total
+ from rhnchannelfamily, rhnprivatechannelfamily
+ where rhnchannelfamily.id = rhnprivatechannelfamily.channel_family_id
+ union all
+ select rhnservergroup.org_id as organization_id,
+ 'system' as entitlement_type,
+ rhnservergrouptype.name as entitlement,
+ rhnservergroup.current_members as used,
+ rhnservergroup.max_members as total
+ from rhnservergroup, rhnservergrouptype
+ where rhnservergroup.group_type = rhnservergrouptype.id
+ and rhnservergroup.max_members > 0
+ ) entitlements, web_customer
+ where entitlements.organization_id = web_customer.id
+ order by entitlements.organization_id,
+ case when entitlement_type = 'channel' then 1 else 0 end,
+ entitlement
+
diff --git a/reporting/reports/data/errata-list b/reporting/reports/data/errata-list
new file mode 100755
index 0000000..06a1ce0
--- /dev/null
+++ b/reporting/reports/data/errata-list
@@ -0,0 +1,41 @@
+
+synopsis:
+
+ Errata out of compliance information - errata details
+
+description:
+
+ List of erratas that are applicable to at least one registered
+ system, together with basic info about the errata. Also see
+ errata-systems report to get list of systems that are affected.
+
+columns:
+
+ advisory Advisory / errata identifier
+ type Advisory type (Enhancement, Bug Fix, Security)
+ cve List of CVE names (Common Vulnerabilities and Exposures Identifiers) addressed by
the errata
+ synopsis Synopsis of the errata
+ systems_affected Number of systems to which this errata is applicable
+
+multival_columns:
+
+ advisory
+ cve : cve
+
+sql:
+
+ select rhnErrata.advisory_name as advisory,
+ rhnErrata.advisory_type as type,
+ rhnCVE.name as cve,
+ rhnErrata.synopsis,
+ count(*) as systems_affected
+ from rhnErrata, rhnServerNeededErrataCache, rhnErrataCVE, rhnCVE
+ where rhnErrata.id = rhnServerNeededErrataCache.errata_id
+ and rhnErrata.id = rhnErrataCVE.errata_id (+)
+ and rhnErrataCVE.cve_id = rhnCVE.id (+)
+ group by rhnErrata.id,
+ rhnErrata.advisory_name,
+ rhnErrata.advisory_type,
+ rhnCVE.name,
+ rhnErrata.synopsis
+ order by rhnErrata.advisory_name, rhnCVE.name
diff --git a/reporting/reports/data/errata-systems
b/reporting/reports/data/errata-systems
new file mode 100755
index 0000000..f40aa9f
--- /dev/null
+++ b/reporting/reports/data/errata-systems
@@ -0,0 +1,38 @@
+
+synopsis:
+
+ Errata out of compliance information - erratas for systems
+
+description:
+
+ List of applicable erratas and systems that are affected.
+
+columns:
+
+ advisory Advisory / errata identifier
+ server_id System identifier
+ profile_name Profile name, as stored on server
+ hostname Hostname, as reported by the system
+ ip_address IP address, as reported by the system
+
+sql:
+
+ select rhnErrata.advisory_name as advisory,
+ rhnServerNeededErrataCache.server_id,
+ rhnServer.name as profile_name,
+ rhnservernetwork.hostname, trim(rhnservernetwork.ipaddr) as ip_address
+ from rhnErrata, rhnServerNeededErrataCache, rhnServer,
+ (
+ select server_id, hostname, ipaddr
+ from rhnservernetwork
+ where id in (
+ select min(id)
+ from rhnservernetwork
+ group by server_id
+ )
+ ) rhnservernetwork
+ where rhnErrata.id = rhnServerNeededErrataCache.errata_id
+ and rhnServerNeededErrataCache.server_id = rhnServer.id
+ and rhnServerNeededErrataCache.server_id = rhnservernetwork.server_id (+)
+ order by rhnErrata.advisory_name, rhnServerNeededErrataCache.server_id
+
diff --git a/reporting/reports/data/inventory b/reporting/reports/data/inventory
new file mode 100755
index 0000000..7662898
--- /dev/null
+++ b/reporting/reports/data/inventory
@@ -0,0 +1,123 @@
+
+synopsis:
+
+ Inventory report
+
+description:
+
+ List of systems registered to the server, together with
+ hardware and software information.
+
+columns:
+
+ server_id System identifier
+ profile_name Profile name, as stored on server
+ hostname Hostname, as reported by the system
+ ip_address IP address, as reported by the system
+ registered_by User under which the system is registered
+ registration_time Registration time
+ last_checkin_time Last checkin time
+ kernel_version Kernel version, as reported by the system
+ packages_out_of_date Number of packages that can be upgraded
+ errata_out_of_date Number of erratas that can be applied
+ software_channel List of channels to which the system is registered
+ configuration_channel List of configuration channels
+ entitlements List of entitlements
+ system_group List of system group to which the system belongs
+ organization Organization name
+ virtual_host System identifier of host, if system is a guest
+ architecture Architecture of the system
+ hardware Hardware information
+
+multival_columns:
+
+ server_id
+ software_channel : software_channel
+ configuration_channel : configuration_channel
+ entitlements : entitlements
+ system_group : system_group
+ virtual_host : virtual_host
+
+sql:
+
+ select rhnserver.id as server_id, rhnserver.name as profile_name,
+ rhnservernetwork.hostname, trim(rhnservernetwork.ipaddr) as ip_address,
+ ( select login from web_contact where rhnserver.creator_id = web_contact.id ) as
registered_by,
+ to_char(rhnserver.created, 'YYYY-MM-DD HH24:MI:SS') as registration_time,
+ ( select to_char(checkin, 'YYYY-MM-DD HH24:MI:SS') from rhnserverinfo where
rhnserver.id = server_id ) as last_checkin_time,
+ running_kernel as kernel_version,
+ ( select count(distinct rhnpackage.name_id) from rhnpackage,
rhnserverneededpackagecache
+ where rhnserver.id = rhnserverneededpackagecache.server_id
+ and rhnserverneededpackagecache.package_id = rhnpackage.id ) as
packages_out_of_date,
+ ( select count(*) from rhnServerNeededErrataCache where rhnserver.id = server_id ) as
errata_out_of_date,
+ rhnchannel.name as software_channel,
+ rhnconfigchannel.name as configuration_channel,
+ entitlement_data.name as entitlements,
+ group_data.name as system_group,
+ (
+ select name from web_customer where rhnserver.org_id = web_customer.id
+ ) as organization,
+ virtual_data.host_system_id as virtual_host,
+ (
+ select rhnserverarch.name from rhnserverarch where rhnserver.server_arch_id =
rhnserverarch.id
+ ) as architecture,
+ (
+ (
+ select count(*) || ' CPUs' from rhncpu where rhnserver.id = rhncpu.server_id
+ ) || network_data.col
+ ) as hardware
+ from rhnserver,
+ (
+ select server_id, hostname, ipaddr
+ from rhnservernetwork
+ where id in (
+ select min(id)
+ from rhnservernetwork
+ group by server_id
+ )
+ ) rhnservernetwork,
+ rhnserverchannel, rhnchannel,
+ rhnserverconfigchannel, rhnconfigchannel,
+ (
+ select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id,
rhnservergroup.name
+ from rhnservergroupmembers, rhnservergroup
+ where rhnservergroupmembers.server_group_id = rhnservergroup.id (+)
+ and rhnservergroup.group_type is not null
+ ) entitlement_data,
+ (
+ select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id,
rhnservergroup.name
+ from rhnservergroupmembers, rhnservergroup
+ where rhnservergroupmembers.server_group_id = rhnservergroup.id (+)
+ and rhnservergroup.group_type is null
+ ) group_data,
+ (
+ select host_system_id, virtual_system_id
+ from rhnvirtualinstance
+ where host_system_id is not null
+ ) virtual_data,
+ (
+ select server_id, max(sys_connect_by_path(val, '; ')) col
+ from (
+ select server_id,
+ name||' '||ip_addr||'/'||netmask||' '||hw_addr val,
+ row_number() over (partition by server_id order by name, rowid) rn
+ from rhnservernetinterface
+ )
+ start with rn = 1
+ connect by prior rn = rn - 1
+ and prior server_id = server_id
+ group by server_id
+ ) network_data
+ where rhnserver.id = rhnservernetwork.server_id (+)
+ and rhnserver.id = rhnserverchannel.server_id (+)
+ and rhnserverchannel.channel_id = rhnchannel.id (+)
+ and rhnserver.id = rhnserverconfigchannel.server_id (+)
+ and rhnserverconfigchannel.config_channel_id = rhnconfigchannel.id (+)
+ and rhnserver.id = entitlement_data.server_id (+)
+ and rhnserver.id = group_data.server_id (+)
+ and rhnserver.id = virtual_data.virtual_system_id (+)
+ and rhnserver.id = network_data.server_id (+)
+ order by server_id, rhnchannel.parent_channel nulls first, rhnchannel.id,
+ rhnconfigchannel.id, entitlement_data.server_group_id, group_data.server_group_id,
+ virtual_data.host_system_id
+
diff --git a/reporting/reports/data/users b/reporting/reports/data/users
new file mode 100755
index 0000000..0a44b02
--- /dev/null
+++ b/reporting/reports/data/users
@@ -0,0 +1,65 @@
+
+synopsis:
+
+ Users in the system
+
+description:
+
+ List of all users for all organizations, with their details
+ and roles.
+
+columns:
+
+ organization_id Organization identifier
+ organization Organization name
+ user_id Internal user id
+ username User name / login
+ last_name Last name
+ first_name First name(s)
+ position Position of the user
+ email Email address
+ role Roles assigned to the user
+ creation_time Date and time when the user was created
+ last_login_time When the user last accessed the system
+ active String enabled or disabled
+
+multival_columns:
+
+ organization_id
+ user_id
+ role : role
+
+sql:
+
+ select web_contact.org_id as organization_id,
+ web_customer.name as organization,
+ web_contact.id as user_id,
+ login as username,
+ web_user_personal_info.last_name,
+ web_user_personal_info.first_names as first_name,
+ web_user_personal_info.title as position,
+ web_user_personal_info.email,
+ rhnUserGroupType.name as role,
+ to_char(web_contact.created, 'YYYY-MM-DD HH24:MI:SS') as creation_time,
+ to_char(rhnUserInfo.last_logged_in, 'YYYY-MM-DD HH24:MI:SS') as
last_login_time,
+ nvl( ( select distinct wccs.label
+ from rhnWebContactChangeLog wccl, rhnWebContactChangeState wccs
+ where wccl.change_state_id = wccs.id
+ and wccl.web_contact_id = web_contact.id
+ and wccl.date_completed = (
+ select max(wccl_exists.date_completed)
+ from rhnWebContactChangeLog wccl_exists
+ where wccl.web_contact_id =
wccl_exists.web_contact_id
+ )
+ ),'enabled') as active
+ from web_contact, web_customer, web_user_personal_info,
+ rhnUserGroupMembers, rhnUserGroup, rhnUserGroupType,
+ rhnUserInfo
+ where web_contact.org_id = web_customer.id
+ and web_contact.id = web_user_personal_info.web_user_id
+ and web_contact.id = rhnUserGroupMembers.user_id (+)
+ and rhnUserGroupMembers.user_group_id = rhnUserGroup.id (+)
+ and rhnUserGroup.group_type = rhnUserGroupType.id (+)
+ and web_contact.id = rhnUserInfo.user_id
+ order by web_customer.id, web_contact.id
+
diff --git a/reporting/reports/data/users-systems b/reporting/reports/data/users-systems
new file mode 100755
index 0000000..94234cd
--- /dev/null
+++ b/reporting/reports/data/users-systems
@@ -0,0 +1,59 @@
+
+synopsis:
+
+ Systems administered by individual users
+
+description:
+
+ List of systems that users can administer.
+
+columns:
+
+ organization_id Organization identifier
+ user_id Internal user id
+ username User name / login
+ server_id System identifier
+ group Group through the user has access to the system
+ admin_access 1 if the user has access by being org administrator
+
+multival_columns:
+
+ organization_id
+ user_id
+ server_id
+ group : group
+
+# adapted query available_to_uid
+sql:
+
+ select organization_id, web_contact_adm.user_id, username, server_id, group_name as
"GROUP", admin_access
+ from (
+ select web_contact.org_id as organization_id,
+ web_contact.id as user_id,
+ login as username,
+ case when web_contact_admin.user_id is not null then 1 else null end as admin_access
+ from web_contact, (
+ select rhnUserGroupMembers.user_id
+ from rhnUserGroupMembers, rhnUserGroup, rhnUserGroupType
+ where rhnUserGroupMembers.user_group_id = rhnUserGroup.id
+ and rhnUserGroup.group_type = rhnUserGroupType.id
+ and rhnUserGroupType.label = 'org_admin'
+ ) web_contact_admin
+ where web_contact.id = web_contact_admin.user_id (+)
+ ) web_contact_adm, (
+ select 1 as is_admin, web_contact.id as user_id, rhnServer.id as server_id, null as
group_name
+ from web_contact, rhnServer
+ where web_contact.org_id = rhnServer.org_id
+ union all
+ select 0 as is_admin,
+ rhnUserServerGroupPerms.user_id, rhnServerGroupMembers.server_id, rhnServerGroup.name
as group_name
+ from rhnUserServerGroupPerms, rhnServerGroupMembers, rhnServerGroup
+ where rhnUserServerGroupPerms.server_group_id = rhnServerGroupMembers.server_group_id
+ and rhnUserServerGroupPerms.server_group_id = rhnServerGroup.id
+ ) rhn_contact_server_group
+ where
+ nvl(web_contact_adm.admin_access, 0) = rhn_contact_server_group.is_admin (+)
+ and web_contact_adm.user_id = rhn_contact_server_group.user_id (+)
+ and rhn_contact_server_group.server_id is not null
+ order by organization_id, user_id, server_id
+
diff --git a/reporting/spacewalk-report b/reporting/spacewalk-report
new file mode 100755
index 0000000..cd1be9e
--- /dev/null
+++ b/reporting/spacewalk-report
@@ -0,0 +1,192 @@
+#!/usr/bin/python -u
+#
+# Copyright (c) 2008 Red Hat, Inc.
+#
+# This software is licensed to you under the GNU General Public License,
+# version 2 (GPLv2). There is NO WARRANTY for this software, express or
+# implied, including the implied warranties of MERCHANTABILITY or FITNESS
+# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 # along with this
software; if not, see
+#
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+#
+# Red Hat trademarks are not licensed under GPLv2. No permission is
+# granted to use or replicate Red Hat trademarks that are incorporated
+# in this software or its documentation.
+#
+
+import sys
+def systemExit(code, msgs=None):
+ """ exit with message and code """
+ if msgs:
+ if type(msgs) not in [type([]), type(())]:
+ msgs = (msgs, )
+ for msg in msgs:
+ sys.stderr.write(str(msg)+'\n')
+ sys.exit(code)
+
+try:
+ import os
+ import csv
+ from optparse import Option, OptionParser
+except KeyboardInterrupt:
+ systemExit(-1, "\nUser interrupted process.")
+
+
+_LIBPATH = "/usr/share/rhn"
+# add to the path if need be
+if _LIBPATH not in sys.path:
+ sys.path.append(_LIBPATH)
+
+sys.path.append('/usr/share/spacewalk')
+import reports
+
+try:
+ from server import rhnSQL
+ from common import initCFG
+except KeyboardInterrupt:
+ systemExit(-1, "\nUser interrupted process.")
+
+
+def processCommandline():
+ """ process the commandline """
+ optionsTable = [
+ Option('--multival-on-rows', action='store_true',
dest='multivalonrows',
+ help='if there are multiple values for certain field, repeat the rows'),
+ Option('--multival-separator', action='store',
dest='multivalseparator',
+ default=';',
+ help='if there are multiple values for certain field, separate them with this
string' \
+ ' (unless --multival-on-rows)'),
+ Option('--db', action='store', type="string",
+ help='alternative database string (username/password@sid);' \
+ + ' default is default_db from /etc/rhn/rhn.conf'),
+ Option('--info', action='store_true',
+ help='print synopsis of the report in the list of report, or description of
individual reports specified'),
+ Option('--list-fields', action='store_true',
+ dest='listfields',
+ help='list fields of the report instead of running the report'),
+ Option('--list-fields-info', action='store_true',
+ dest='listfieldsinfo',
+ help='as --list-fields but also prints description for each column'),
+ ]
+
+ optionParser = OptionParser( \
+ usage="usage: %s [options] [report_name]" % sys.argv[0], \
+ option_list=optionsTable)
+ options, args = optionParser.parse_args()
+
+ sys.argv[1:] = args
+
+ return options
+
+if __name__ == '__main__':
+ options = processCommandline()
+
+ try:
+ if len(sys.argv) > 2:
+ systemExit(-5, 'Only one report name expected.')
+ if len(sys.argv) > 1:
+ report_name = sys.argv[1]
+
+ try:
+ report = reports.report(report_name)
+ except(reports.spacewalk_unknown_report):
+ systemExit(-4, 'Unknown report [%s].' % report_name)
+
+ need_exit = None
+ if options.info:
+ if report.synopsis != None:
+ print report.synopsis
+ else:
+ print "No synopsis for report %s." % report_name
+ if report.description != None:
+ print
+ print report.description
+ need_exit = True
+
+ if options.listfields or options.listfieldsinfo:
+ if options.info:
+ print
+ print "Fields in the report:"
+ print
+
+ for c in report.columns:
+ text = c
+ if options.info:
+ text = " %s" % c
+ if options.listfieldsinfo and report.column_descriptions.has_key(c) :
+ text = "%s: %s" % ( text, report.column_descriptions[c] )
+ print text
+ need_exit = True
+
+ if need_exit:
+ sys.exit(0)
+
+ initCFG('server')
+ rhnSQL.initDB(options.db)
+
+ writer = csv.writer(sys.stdout)
+
+ h = rhnSQL.prepare(report.sql)
+ h.execute()
+
+ db_columns = map(lambda x: x[0].lower(), h.description)
+ if db_columns != report.columns:
+ systemExit(-3, \
+ "Columns in report spec and in the database do not match:\nexpected %s\n
got %s" % ( report.columns, db_columns ))
+ writer.writerow(report.columns)
+
+ row = h.fetchone()
+ prevrow = None
+ outrow = None
+ multival_dupes = {}
+ while row != None:
+ if options.multivalonrows or not report.multival_column_names.keys():
+ writer.writerow(row)
+ row = h.fetchone()
+ continue
+
+ if outrow != None:
+ for m in report.multival_columns_stop:
+ if prevrow[m] != row[m]:
+ writer.writerow(outrow)
+ outrow = None
+ break
+
+ if outrow != None:
+ for m in report.multival_columns_reverted.keys():
+ if prevrow[m] != row[m]:
+ if not m in multival_dupes:
+ multival_dupes[m] = {}
+ if not row[m] in multival_dupes[m]:
+ outrow[m] = str(outrow[m]) + options.multivalseparator + str(row[m])
+ multival_dupes[m][row[m]] = 1
+ break
+
+ if outrow == None:
+ outrow = [ x for x in row ]
+ multival_dupes = {}
+
+ prevrow = row
+ row = h.fetchone()
+
+ if outrow != None:
+ writer.writerow(outrow)
+ else:
+ for report_name in reports.available_reports():
+ if options.info:
+ synopsis = ''
+ try:
+ report = reports.report(report_name)
+ synopsis = report.synopsis
+ except:
+ None
+ print "%s: %s" % ( report_name, synopsis )
+ else:
+ print report_name
+
+ except KeyboardInterrupt:
+ systemExit(-1, "\nUser interrupted process.")
+ except (rhnSQL.SQLError, rhnSQL.SQLSchemaError, rhnSQL.SQLConnectError), e:
+ # really a stub for better exception handling in the future.
+ sys.stderr.write("SQL error occurred, traceback follows...\n")
+ raise
+
diff --git a/reporting/spacewalk-report.sgml b/reporting/spacewalk-report.sgml
new file mode 100644
index 0000000..d792336
--- /dev/null
+++ b/reporting/spacewalk-report.sgml
@@ -0,0 +1,127 @@
+<!DOCTYPE refentry PUBLIC "-//OASIS//DTD DocBook V3.1//EN">
+
+<refentry>
+
+<RefMeta>
+<RefEntryTitle>spacewalk-report</RefEntryTitle><manvolnum>8</manvolnum>
+</RefMeta>
+
+<RefNameDiv>
+<RefName><command>spacewalk-report</command></RefName>
+<RefPurpose>
+Generate reports from Spacewalk server in csv format
+</RefPurpose>
+</RefNameDiv>
+
+<RefSynopsisDiv>
+<Synopsis>
+ <cmdsynopsis>
+ <command>spacewalk-report</command>
+
<arg>--db=<replaceable>connect_string</replaceable></arg>
+ <arg>--info</arg>
+ </cmdsynopsis>
+ <cmdsynopsis>
+ <command>spacewalk-report</command>
+ <sbr />
+ <group>
+ <arg>--multival-on-rows</arg>
+
<arg>--multival-separator=<replaceable>separator-string</replaceable></arg>
+ </group>
+ <sbr />
+
<arg>--db=<replaceable>connect-string</replaceable></arg>
+ <sbr />
+ <arg>--info</arg>
+ <sbr />
+
<group><arg>--list-fields</arg><arg>--list-fields-info</arg></group>
+ <sbr />
+ <arg
choice='plain'><replaceable>report-name</replaceable></arg>
+ </cmdsynopsis>
+</Synopsis>
+</RefSynopsisDiv>
+
+<RefSect1><Title>Description</Title>
+<para>
+ The <command>spacewalk-report</command> command can be used to
+ generate canned reports from Spacewalk server database. The report
+ which will be generated is selected by specifying its name as
+ a parameter to <command>spacewalk-report</command>.
+</para>
+
+<para>
+ If the report name is not specified, list of available report
+ names is printed, one name per line.
+</para>
+
+<RefSect1><Title>Options</Title>
+<variablelist>
+ <varlistentry>
+ <term>--info</term>
+ <listitem>
+ <para>When listing available reports, also print
+ synopsis for each report.</para>
+
+ <para>When used with report name specified, print
+ synopsis and description for the report, instead
+ of running the report.</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>--multival-on-rows</term>
+ <listitem>
+ <para>If certain field can have multiple values, you can
+ use this option to have records for the entity repeated
+ in output in the form of cartesian product. By default,
+ those multiple values are presented on one row in one
+ field, separated by semicolon.</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+
<term>--multival-separator=<replaceable>separator-string</replaceable></term>
+ <listitem>
+ <para>If multiple values are output on one row (the
+ default), this option can change the string which
+ separates multiple values in one field. The default is
+ semicolon.</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+
<term>--db=<replaceable>connect-string</replaceable></term>
+ <listitem>
+ <para>Database connect string in the form of
+ <replaceable>username/password@dbname</replaceable> can be
+ specified to generated report from user-defined database.
+ By default, connect string from default_db option from
+ /etc/rhn/rhn.conf is used.</para>
+ <para>This option should be considered obsoleted and may
+ not be available in next versions of
+ <command>spacewalk-report</command>.
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>--list-fields</term>
+ <listitem>
+ <para>Instead of running the report, shows fields that the
+ report has, one field name per line.</para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term>--list-fields-info</term>
+ <listitem>
+ <para>Instead of running the report, shows fields that the
+ report has, one field name per line, and a description of
+ that field.</para>
+ </listitem>
+ </varlistentry>
+</variablelist>
+</RefSect1>
+
+<RefSect1><Title>Authors</Title>
+<simplelist>
+ <member>Jan Pazdziora</member>
+</simplelist>
+</RefSect1>
+</RefEntry>
+
+<!--
+vim: sw=4
+-->
diff --git a/reporting/spacewalk-reports.spec b/reporting/spacewalk-reports.spec
new file mode 100644
index 0000000..f3845c9
--- /dev/null
+++ b/reporting/spacewalk-reports.spec
@@ -0,0 +1,45 @@
+Name: spacewalk-reports
+Summary: Script based reporting
+Group: Applications/Internet
+License: GPLv2
+Version: 0.7.0
+Release: 1%{?dist}
+URL:
https://fedorahosted.org/spacewalk
+Source0:
https://fedorahosted.org/releases/s/p/spacewalk/%{name}-%{version}.tar.gz
+BuildRoot: %{_tmppath}/%{name}-%{version}-%{release}-root-%(%{__id_u} -n)
+BuildArch: noarch
+Requires: python
+BuildRequires: /usr/bin/docbook2man
+
+%description
+Script based reporting to retrieve data from Spacewalk server in CSV format.
+
+%prep
+%setup -q
+
+%build
+/usr/bin/docbook2man *.sgml
+
+%install
+rm -rf $RPM_BUILD_ROOT
+install -d $RPM_BUILD_ROOT/%{_bindir}
+install -d $RPM_BUILD_ROOT/%{_prefix}/share/spacewalk
+install -d $RPM_BUILD_ROOT/%{_prefix}/share/spacewalk/reports/data
+install -d $RPM_BUILD_ROOT/%{_mandir}/man8
+install spacewalk-report $RPM_BUILD_ROOT/%{_bindir}
+install reports.py $RPM_BUILD_ROOT/%{_prefix}/share/spacewalk
+install reports/data/* $RPM_BUILD_ROOT/%{_prefix}/share/spacewalk/reports/data
+install *.8 $RPM_BUILD_ROOT/%{_mandir}/man8
+
+%clean
+rm -rf $RPM_BUILD_ROOT
+
+%files
+%attr(755,root,root) %{_bindir}/spacewalk-report
+%{_prefix}/share/spacewalk/reports.py*
+%{_prefix}/share/spacewalk/reports/data/*
+%{_mandir}/man8/spacewalk-report.8*
+
+%changelog
+* Mon Dec 14 2009 Jan Pazdziora 0.7.0-1
+- moved reports from spacewalk-backend-0.8.10-1 separate package