F Accessing Data Through Third Party Tools

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.

Description of Views

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