D 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