4.6

VMware, Inc. 27
Chapter 2 Integrating with the Event Database
Sample Database Queries and Views
Displayallerroreventsfromtheevent_historicaltable.
CREATE VIEW error_events AS
(
SELECT ev.EventID, ev.Time, ev.Module, ev.EventType, ev.ModuleAndEventText
FROM dbo.VE_event_historical AS ev
WHERE ev.Severity = ‘ERROR’
);
Displayallwarningeventsfromtheevent_historicaltable.
CREATE VIEW warning_events AS
(
SELECT ev.EventID, ev.Time, ev.Module, ev.EventType, ev.ModuleAndEventText
FROM dbo.VE_event_historical AS ev
WHERE ev.Severity = ‘WARNING’
);
ListallrecenteventsthatareassociatedwiththeuserfredinthedomainMYDOM.
CREATE VIEW user_fred_events AS
(
SELECT ev.EventID, ev.Time, ev.Module, ev.EventType, ev.Severity, ev.Acknowledged
FROM dbo.VE_event_historical AS ev,
dbo.VE_event_data_historical AS ed
WHERE ev.EventID = ed.EventID AND ed.Name = 'UserDisplayName' AND ed.StrValue =
‘MYDOM\fred’
);
Listallrecenteventswheretheagentonamachineshutdown.
CREATE VIEW agent_shutdown_events AS
(
SELECT ev.EventID, ev.Time, ed.StrValue
FROM dbo.VE_event_historical AS ev,
dbo.VE_event_data_historical AS ed
WHERE ev.EventID = ed.EventID AND ev.EventType = ‘AGENT_SHUTDOWN’ AND
ed.Name = ‘MachineName’
);
Listallrecenteventswhereadesktopfailedtolaunchbecausethedesktoppoolwasempty.
CREATE VIEW desktop_launch_failure_events AS
(
SELECT ev.EventID, ev.Time, ed1.StrValue, ed2.StrValue
FROM dbo.VE_event_historical AS ev,
dbo.VE_event_data_historical AS ed1,
dbo.VE_event_data_historical AS ed2
WHERE ev.EventID = ed1.EventID AND ev.EventID = ed2.EventID AND
ev.EventType = ‘BROKER_POOL_EMPTY’ AND
ed1.Name = ‘UserDisplayName’ AND ed2.Name = ‘DesktopId’
);
Listallrecenteventswhereanadministratorremovedadesktoppool.
CREATE VIEW desktop_pool_removed_events AS
(
SELECT ev.EventID, ev.Time, ed1.StrValue, ed2.StrValue
FROM dbo.VE_event_historical AS ev,
dbo.VE_event_data_historical AS ed1,
dbo.VE_event_data_historical AS ed2
WHERE ev.EventID = ed1.EventID AND ev.EventID = ed2.EventID AND
ev.EventType = ‘ADMIN_DESKTOP_REMOVED’ AND
ed1.Name = ‘UserDisplayName’ AND ed2.Name = ‘DesktopId’
);
NOTEReplacethedbo.VE_prefixintheexampleswiththeappropriateprefixforyoureventdatabaseserver.