There are database views available to access the most commonly used information in third part data analysis and reporting tools. The following section offers a description of each of the views.
Note:
All date and time fields are in GMT time zone as all dates in the Configuration Change Console repository are stored in GMT.The following are the views you can use to access information in third party data analysis.
View: custview_allfileevents
Returns all file events for all devices.
Fields:
EVENT_ID: The unique identifier for the event record
DEVICE_NAME: The name of the device that the event happened on
DEVICE_ASSETTAG: The asset tag of the device that the event happened on
FILE_NAME: The name of the file the event affected. If the event is a rename, this field stores the new file name after the rename event.
EVENT_TIME: The time of the event.
FILE_SIZE: The size of the file after the event
EVENT: The event that occurred: Create, Deleted, Modified, Renamed
RENAMED_FROM_FILE_NAME: The name of the original file for renamed event type
EVENT_USER: The user that performed the event
Example Usage:
select * from custview_allfileevents where EVENT_TIME between ? and ?
View: custview_allprocessevents
Returns all process events for all devices
Fields:
ID: The unique identifier for the event
DEVICE_NAME: The name of the device that the event happened on
DEVICE_ASSETTAG: The asset tag of the device that the event happened on
PROCESS_NAME: The name of the process the event affected.
PROCESS_ID: The OS-supplied ID of the process
EVENT_TIME_GMT: The time of the event.
EVENT: The event that occurred: Started, Stopped, Short Lived
PROCESS_USER: The user who owns the process
View: custview_allincomingtickets
Returns all incoming tickets that have come from the change management server. If an incoming ticket is associated with many devices, there will be one result for each association.
Fields:
TICKET_ID: The unique identifier for the ticket from the change management server
STATE: Current state of the ticket as used by CCC: Open or Closed
SUMMARY: The ticket summary content from the ticket management application
SUPERVISOR: The assigned supervisor from the change management server
DEVICE_NAME: The name of the device the ticket is associated with. If a single ticket is associated with many devices, there will be one result from this view for each device.
DEVICE_ASSETTAG: The assigned asset tag for the device the ticket is associated with
PLANNED_START: The set planned start date and time from the change management server.
PLANNED_END: The set planned end date and time from the change management server.
CREATED_TIME_GMT: Time the ticket was created.
UPDATED_TIME_GMT: Time the ticket was last updated
TICKET_CATEGORY: The name of the CTI for the ticket
Example Usage:
Select * from custview_allincomingtickets where state = 'OPEN' Select * from custview_allincomingtickets where state = 'CLOSED' Select * from custview_allincomingtickets where planned_start between ? and ? and planned_end between ? and ? Select * from custview_allincomingtickets where ticket_category like '%:Software:%'
View: custview_appfileeventslastday
Returns count of events from files associated with applications by application in the last 24 hours.
Fields:
APPLICATION: The name of the application instance and the type of application
EVENT_COUNT: The count of file changes for that app in the last day
View: custview_appfileeventslasthour
Returns count of events from files associated with applications by application in the last 1 hour.
Fields:
APPLICATION: The name of the application instance and the type of application
EVENT_COUNT: The count of file changes for that app in the last hour
View: custview_appinteventslastday
Returns count of events from internal elements associated with applications by application in the last 24 hours.
Fields:
APPLICATION: The name of the application instance and the type of application
EVENT_COUNT: The count of internal changes for that app in the last day
View: custview_appinteventslasthour
Returns count of events from internal elements associated with applications by application in the last 1 hour.
Fields:
APPLICATION: The name of the application instance and the type of application
EVENT_COUNT: The count of internal changes for that app in the last hour
View: custview_appproceventslastday
Returns count of events from processes associated with applications by application in the last 24 hours.
Fields:
APPLICATION: The name of the application instance and the type of application
EVENT_COUNT: The count of process changes for that app in the last day
View: custview_appproceventslasthour
Returns count of events from processes associated with applications by application in the last 1 hour.
Fields:
APPLICATION: The name of the application instance and the type of application
EVENT_COUNT: The count of file changes for that app in the last hour
View: custview_autheventslastday
Returns history of all outbound tickets to a change management server in response to authorized events in the last 24 hours.
Fields:
ID: The unique identifier of the outbound ticket.
DEVICE_NAME: The name of the device that the event happened on
ENTITY_TYPE: The type of entity that had the event: File, Process, Internal
ENTITY_NAME: The name of the entity: File name, Process Name, or Internal key name
EVENT_TYPE: The event, events for files are Created, Deleted, Modified, Renamed. For Processes types are Started and Stopped. For internal changes, the available type is Modified.
EVENT_TIME: The time the event occurred
PR_ID: The unique identifier for the process event
FR_ID: The unique identifier for the file event
APPR_ID: The unique identifier for the application internal event
TICKET_ID: The ID of the ticket from the change management server that gave authorization for the change
AFFECTED_TICKET_CATEGORY: The CTI that is associated with the ticket that gave authorization for the change
View: custview_autheventslasthour
Returns history of all outbound tickets to a change management server in response to authorized events in the last hour.
Fields:
ID: The unique identifier of the outbound ticket.
DEVICE_NAME: The name of the device that the event happened on
ENTITY_TYPE: The type of entity that had the event: File, Process, Internal
ENTITY_NAME: The name of the entity: File name, Process Name, or Internal key name
EVENT_TYPE: The event, events for files are Created, Deleted, Modified, Renamed. For Processes types are Started and Stopped. For internal changes, the available type is Modified.
EVENT_TIME: The time the event occurred
PR_ID: The unique identifier for the process event
FR_ID: The unique identifier for the file event
APPR_ID: The unique identifier for the application internal event
TICKET_ID: The ID of the ticket from the change management server that gave authorization for the change
AFFECTED_TICKET_CATEGORY: The CTI that is associated with the ticket that gave authorization for the change
View: custview_fileeventsperhour
Returns count of file events per hour per device.
Fields:
DEVICE_NAME: The name of the device for which events are aggregated
DEVICE_ASSETTAG: The asset tag of the device
EVENT_COUNT: Number of file events during the hour
HOUR_GMT: The hour for this count
View: custview_notifcountlastday
Returns count of notifications for each person by priority and status in the last 24 hours.
Fields:
NOTIFICATION_COUNT: Count of notifications
RECIPIENT: The login name of the person the notifications were for
PRIORITY: The priority of the notifications ranging from 1 to 5 with 1 being highest
STATUS: Current status of the notification: Pending, Closed, Pending But Escalation Failed
ACTION_TAKEN:
View: custview_notifcountlasthour
Returns count of notifications for each person by priority and status in the last hour
Fields:
NOTIFICATION_COUNT: Count of notifications
RECIPIENT: The login name of the person the notifications were for
PRIORITY: The priority of the notifications ranging from 1 to 5 with 1 being highest
STATUS: Current status of the notification: Pending, Closed, Pending But Escalation Failed
ACTION_TAKEN:
View: custview_processeventsperhour
Returns count of process events per hour per device.
Fields:
DEVICE_NAME: The name of the device for which events are aggregated
DEVICE_ASSETTAG: The asset tag of the device
EVENT_COUNT: Number of process events during the hour
HOUR_GMT: The hour for this count
View: custview_unautheventslastday
Returns history of all outbound tickets to a change management server in response to unauthorized events in the last 24 hours.
Fields:
ID: Unique ID for the outbound ticket
Device_Name: Name of the device event occurred on
Entity_Type: Type of event (file, process, etc)
Entity_Name: Name of the entity that changed (file name, etc)
Event_Type: Type of event (started, stopped, created, deleted, etc)
Event_Time: Time the event occurred in GMT
PR_ID: ID of the process event (fk to processrunning table)
FR_ID: ID of the file event (fk to the filerunning table)
APPR_ID: ID of the app internal event (fk to the apprunning table)
Affected_Ticket_Category: Change Management categorization the event is related to.
View: custview_unautheventslasthour
Returns history of all outbound tickets to a change management server in response to unauthorized events in the last 1 hour.
Fields:
ID: Unique ID for the outbound ticket
Device_Name: Name of the device event occurred on
Entity_Type: Type of event (file, process, etc)
Entity_Name: Name of the entity that changed (file name, etc)
Event_Type: Type of event (started, stopped, created, deleted, etc)
Event_Time: Time the event occurred in GMT
PR_ID: ID of the process event (fk to processrunning table)
FR_ID: ID of the file event (fk to the filerunning table)
APPR_ID: ID of the app internal event (fk to the apprunning table)
Affected_Ticket_Category: Change Management categorization the event is related to.
View: custview_usereventsperhour
Returns count of user events per hour per device.
Fields:
Device_Name: name of device changes occurred on
Device_AssetTag: asset tag of the device
Event_Count: count of changes on the device
Hour_GMT: The hour the events occurred in GMT
View: custview_userproccountlastday
Returns count of processes run by each user in the last 24 hours.
Fields:
Device_Name: name of device changes occurred on
Device_AssetTag: asset tag of the device
Event_Count: count of changes on the device
Username: user that made the changes
View: custview_userproccountlasthour
Returns count of processes run by each user in the last 1 hour.
Fields:
Device_Name: name of device changes occurred on
Device_AssetTag: asset tag of the device
Event_Count: count of changes on the device
Username: user that made the changes
View: custview_all_app_changes
Returns count of all events by component ID and hour.
View: custview_app_chng_viz
View: custview_procappeventlog
Returns a log of all process events and component associations
Fields:
EventId: The event ID assigned by the server
Time_GMT: Time of the event in GMT
Device: Name of the device the event occurred on
Type: Fixed as "Process"
Event: Type of event, either process started or stopped
EventUser: The OS user that caused the event
Application: The name of the component this event is tied to
View: custview_userlogoffeventlog
Returns a log of all user logoff events and component associations
Fields:
EventId: The event ID assigned by the server
Time_GMT: Time of the event in GMT
Device: Name of the device the event occurred on
Type: Fixed as "Access"
Name: Fixed as '-'
Event: Fixed as 'Logoff'
EventUser: The OS user that caused the event
Application: The name of the component this event is tied to
Example Usage:
SELECT * FROM custview_userlogoffeventlog
WHERE
DEVICE in ('deviceabc','devicecde')
AND
TIME_GMT BETWEEN
TO_DATE('12/10/04 00:00:00','mm/dd/yy HH24:mi:ss')
AND
TO_DATE('12/10/04 04:00:00','mm/dd/yy HH24:mi:ss')
AND
EVENTUSER = 'username';
View: custview_userlogoneventlog
Returns a log of all user logon events and component associations
Fields:
EventId: The event ID assigned by the server
Time_GMT: Time of the event in GMT
Device: Name of the device the event occurred on
Type: Fixed as "Access"
Name: Fixed as '-'
Event: Fixed as 'Logon'
EventUser: The OS user that caused the event
Application: The name of the component this event is tied to
Example Usage:
SELECT * FROM custview_userlogoneventlog
WHERE
DEVICE in ('deviceabc','devicecde')
AND
TIME_GMT BETWEEN
TO_DATE('12/10/04 00:00:00','mm/dd/yy HH24:mi:ss')
AND
TO_DATE('12/10/04 04:00:00','mm/dd/yy HH24:mi:ss')
AND
EVENTUSER = 'username';
View: custview_policy_user_changes
Count of events by OS user or component internal user grouped by the policy that is affected and by the hour the event occurred.
Fields:
Cnt: Count of events
Policyid: The ID of the Policy (fk to custview_policy_name)
Time_hours: Date and Time by hour in GMT
Usertype: Whether the user is an OS user or Component Internal User
View: custview_policy_event
Get count of events by event priority 1 or 2, authorized and unauthorized grouped by the component, policy and control.
Fields:
Policy_id: The ID for the framework policy (fk to custview_policy_name)
appid: The ID for the component (fk to appconfig)
time_hours: Date and Time by hour in GMT
controlid: The ID for the framework control (fk to controlconfig)
controlname: the name of the framework control
count: total count of events
p1_cnt: total count of events that are Priority 1
p2_cnt: total count of events that are Priority 2
authcnt: total count of events that are Authorized
unauthcnt: total count of events that are Unauthorized
View: custview_policy_pr_changes
Count of process changes by frameowork policy by hour.
Fields:
Cnt: count of events
Policyid: The ID for the framework policy (fk to custview_policy_name)
Time_hours: Date and Time by hour in GMT
View: custview_policy_name
Returns all framework ID, Policy Ids and the framework and policy name
Fields:
Framework_id: The ID for the framework
Policy_id: The ID for the policy
Full_name: the full name of the framework and policy combination.
View: custview_appgroup_user_changes
Count of User events by hour and by application.
Fields:
Cnt: Count of events
Groupid: The ID of the application (fk)
Time_hours: Date and time by hour in GMT
Usertype: type of user, OS User or Component Internal User
View: custview_appgroup_pr_changes
Count of Process events by hour and by application.
Fields:
Cnt: Count of events
Groupid: The ID of the application (fk)
Time_hours: Date and time by hour in GMT
View: custview_appgroup_file_changes
Count of File events by hour and by application.
Fields:
Cnt: Count of events
Groupid: The ID of the application (fk)
Time_hours: Date and time by hour in GMT
View: custview_policy_file_changes
Count of file changes by framework policy by hour
Fields:
Cnt: count of events
Policyid: ID of the framework policy (fk to custview_policy_name)
Time_hours: Date and time by hour in GMT
View: custview_policy_app_changes
Count of component internal changes by framework policy by hour
Fields:
Cnt: count of events
Policyid: ID of the framework policy (fk to custview_policy_name)
Time_hours: Date and time by hour in GMT
View: custview_appgroup_app_changes
Count of Component Internal events by hour and by application.
Fields:
Cnt: Count of events
Groupid: The ID of the application (fk)
Time_hours: Date and time by hour in GMT
View: custview_person_productrole
Returns all product roles for each person configured
Fields:
ProductRole: The product role
LoginName: The person's login name
FirstName: The person's first name
LastName: the person's last name
View: custview_dev_group_assignment
Fields:
Group_id: The ID of the device group (fk to devicegroupconfig)
Group_name: The name of the device group
Device_id: The ID of the device (fk to deviceconfig)
Device_name: The name of the device
View: custview_fileappeventlog
Returns a log of all file events and component associations.
Fields:
EventId: The event ID assigned by the server
Time_GMT: Time of the event in GMT
Device: Name of the device the event occurred on
Type: Fixed as "Access"
Name: Fixed as '-'
Event: Fixed as 'Logon'
EventUser: The OS user that caused the event
Application: The name of the component this event is tied to
Example Usage:
SELECT * FROM custview_fileappeventlog
WHERE
DEVICE in ('deviceabc','devicecde')
AND
TIME_GMT BETWEEN
TO_DATE('12/10/04 00:00:00','mm/dd/yy HH24:mi:ss')
AND
TO_DATE('12/10/04 04:00:00','mm/dd/yy HH24:mi:ss')
AND
EVENTUSER = 'username'
View: custview_app_comp_assignment
Returns the assignment of component instances to applications so you can recreate the model for an application. Component instances that are not assigned to an application at all will belong to an application called "Components not assigned to an application".
Fields:
App_id: The ID of the application (fk to appgroupconfig)
App_name: The name of the application
Component_id: The ID of the component instance (fk to appconfig)
Component_name: The name of the component
View: custview_all_policy
Returns all Framework policies and Ids configured in the product. This includes predefined and custom policies.
Fields:
Policyid: The ID of the policy (fk to controlsetconfig)
Policyname: The name of the policy
View: custview_all_applicationgroup
Returns all applications and their Ids configured in the product.
Fields:
Groupid: The ID of the application (fk to appgroupconfig)
Groupname: The name of the application
View: custview_appgrp_change_summary
Reports counts of each type of event by the application they are associated with by the hour the events happened.
Fields:
Group_id: The ID of the application (fk to appgroupconfig)
Group_name: The name of the application
Cnt_file: Count of file events
Cnt_process: Count of process events
Cnt_app: Count of component internal events
Cnt_appu: Count of component internal user events
Cnt_osu: Count of OS user events
T_hour: Data and time by hours in GMT
View: custview_all_user_process
Count of all user process events by hour.
Fields:
Dev_id: The ID of the device where the event occurred (fk to deviceconfig)
Time_hours: Date and time by hours in GMT
User_name: The user name for these events
Count: Count of events
View: custview_all_unauth_changes
Count of all unauthorized changes by component instance by hour.
Fields:
Cnt: Count of changes
App_id: The ID of the component instance where events occurred (fk to appconfig)
T_hour: Date and time by hours in GMT