You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spot.apache.org by na...@apache.org on 2018/01/24 20:26:32 UTC
[2/3] incubator-spot git commit: Added sub and master views.
Added sub and master views.
Project: http://git-wip-us.apache.org/repos/asf/incubator-spot/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-spot/commit/069e02d4
Tree: http://git-wip-us.apache.org/repos/asf/incubator-spot/tree/069e02d4
Diff: http://git-wip-us.apache.org/repos/asf/incubator-spot/diff/069e02d4
Branch: refs/heads/SPOT-181_ODM
Commit: 069e02d4ab26c9a3c50b9ccf0558eba28d76d6d3
Parents: 8e64552
Author: Vladimir <Vl...@sstech.us>
Authored: Wed Jul 12 22:02:45 2017 +0300
Committer: Vladimir <Vl...@sstech.us>
Committed: Wed Jul 12 22:02:45 2017 +0300
----------------------------------------------------------------------
spot-setup/create_email_parquet.hql | 4 +-
spot-setup/create_wgtraffic_parquet.hql | 2 +-
spot-setup/create_windows_parquet.hql | 2 +-
.../views/hive/AdministrationActivity.sql | 131 +++++++++++++++++++
.../views/hive/FileObjectAccessedOrChanged.sql | 118 +++++++++++++++++
spot-setup/views/hive/MessageEvent.sql | 77 +++++++++++
spot-setup/views/hive/NetworkConnection.sql | 86 ++++++++++++
spot-setup/views/hive/PasswordChangeOrReset.sql | 45 +++++++
.../views/hive/ProcessStartupOrShutdown.sql | 87 ++++++++++++
.../hive/SecurityObjectAccessedOrChanged.sql | 119 +++++++++++++++++
spot-setup/views/hive/UseOfPrivilegeCommand.sql | 89 +++++++++++++
.../views/hive/UserAccountAddedOrRemoved.sql | 102 +++++++++++++++
spot-setup/views/hive/UserLogin.sql | 89 +++++++++++++
13 files changed, 947 insertions(+), 4 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/create_email_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/create_email_parquet.hql b/spot-setup/create_email_parquet.hql
index ad42887..d3ade61 100644
--- a/spot-setup/create_email_parquet.hql
+++ b/spot-setup/create_email_parquet.hql
@@ -1,7 +1,7 @@
SET hiveconf:huser;
SET hiveconf:dbname;
-CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.email_login_view_partition (
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.email_view_partition (
event_time string,
event_name string,
result_status string,
@@ -27,5 +27,5 @@ PARTITIONED BY (
day string
)
STORED AS PARQUET
-LOCATION '${hiveconf:huser}/email_login_view_parquet_partition';
+LOCATION '${hiveconf:huser}/email_view_parquet_partition';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/create_wgtraffic_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/create_wgtraffic_parquet.hql b/spot-setup/create_wgtraffic_parquet.hql
index a9f9c45..964c7a5 100644
--- a/spot-setup/create_wgtraffic_parquet.hql
+++ b/spot-setup/create_wgtraffic_parquet.hql
@@ -44,7 +44,7 @@ CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.wgtraffic_view_partition
PARTITIONED BY (
year string,
month string,
- day strin
+ day string
)
STORED AS PARQUET
LOCATION '${hiveconf:huser}/wgtraffic_view_partition';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/create_windows_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/create_windows_parquet.hql b/spot-setup/create_windows_parquet.hql
index d0c2f99..143de97 100644
--- a/spot-setup/create_windows_parquet.hql
+++ b/spot-setup/create_windows_parquet.hql
@@ -34,7 +34,7 @@ CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.windows_view_partition (
privilege_list string,
workstation_name string,
status string,
- accesses string,
+ accesses string
)
PARTITIONED BY (
year string,
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/AdministrationActivity.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/AdministrationActivity.sql b/spot-setup/views/hive/AdministrationActivity.sql
new file mode 100644
index 0000000..b2644f3
--- /dev/null
+++ b/spot-setup/views/hive/AdministrationActivity.sql
@@ -0,0 +1,131 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.AdministrationActivity_windows AS
+SELECT
+ event_time AS TimeOfEvent,
+ "" AS TimeReceived,
+ "" AS DeviceReceived,
+ "" AS TimeLoaded,
+ "" AS SyslogFacility,
+ "" AS SyslogPriority,
+ "Microsoft" AS ReportingVendor,
+ "Windows" AS ReportingProduct,
+ "2008+" AS ReportingVersion,
+ event_id AS EventID,
+ event_description AS EventName,
+ "AdminAccountActivity" AS EventLogType,
+ subject_logonid AS SequenceID,
+ "" AS Severity,
+ subject_username AS SourceUserID,
+ "" AS SourceHost,
+ ip_address AS SourceIP,
+ ip_port AS SourcePort,
+ "" AS SourceMac,
+ subject_domainname AS SourceDomain,
+ "" AS ReportingHost,
+ "" AS ReportingIP,
+ "" AS ReportingPort,
+ "" AS ReportingMac,
+ target_username AS DestinationUserID,
+ "" AS DestinationHost,
+ "" AS DestinationIP,
+ "" AS DestinationPort,
+ "" AS DestinationMac,
+ target_domainname AS DestinationDomain,
+ event_description AS Action,
+ object_name AS ObjectName,
+ handle_id AS ObjectID,
+ object_type AS ObjectType,
+ "" AS ObjectCreateTime,
+ "" AS ObjectModifiedTime,
+ "" AS ObjectPath,
+ "" AS ObjectPermission,
+ "" AS ObjectHash,
+ "" AS ObjectSize,
+ "" AS OriginalObjectName,
+ "" AS OriginalObjectID,
+ "" AS OriginalObjectType,
+ "" AS OriginalObjectCreateTime,
+ "" AS OriginalObjectModifiedTime,
+ "" AS OriginalObjectPath,
+ "" AS OriginalObjectPermission,
+ "" AS OriginalObjectHash,
+ "" AS OriginalObjectSize,
+ year,
+ month,
+ day
+
+FROM ${hiveconf:dbname}.windows_view_partition
+WHERE event_id IN (
+ 4704,
+ 4705,
+ 4720,
+ 4722,
+ 4725,
+ 4726,
+ 4728,
+ 4729,
+ 4737,
+ 4738,
+ 4740,
+ 4767,
+ 4781
+);
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.AdministrationActivity
+(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ EventID,
+ EventName,
+ EventLogType,
+ SequenceID,
+ Severity,
+ SourceUserID,
+ SourceHost,
+ SourceIP,
+ SourcePort,
+ SourceMac,
+ SourceDomain,
+ ReportingHost,
+ ReportingIP,
+ ReportingPort,
+ ReportingMac,
+ DestinationUserID,
+ DestinationHost,
+ DestinationIP,
+ DestinationPort,
+ DestinationMac,
+ DestinationDomain,
+ Action,
+ ObjectName,
+ ObjectID,
+ ObjectType,
+ ObjectCreateTime,
+ ObjectModifiedTime,
+ ObjectPath,
+ ObjectPermission,
+ ObjectHash,
+ ObjectSize,
+ OriginalObjectName,
+ OriginalObjectID,
+ OriginalObjectType,
+ OriginalObjectCreateTime,
+ OriginalObjectModifiedTime,
+ OriginalObjectPath,
+ OriginalObjectPermission,
+ OriginalObjectHash,
+ OriginalObjectSize,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.AdministrationActivity_windows;
+
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/FileObjectAccessedOrChanged.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/FileObjectAccessedOrChanged.sql b/spot-setup/views/hive/FileObjectAccessedOrChanged.sql
new file mode 100644
index 0000000..1663a44
--- /dev/null
+++ b/spot-setup/views/hive/FileObjectAccessedOrChanged.sql
@@ -0,0 +1,118 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.FileObjectAccessedOrChanged_windows AS
+SELECT
+ event_time AS TimeOfEvent,
+ "" AS TimeReceived,
+ "" AS DeviceReceived,
+ "" AS TimeLoaded,
+ "" AS SyslogFacility,
+ "" AS SyslogPriority,
+ "Microsoft" AS ReportingVendor,
+ "Windows" AS ReportingProduct,
+ "2008+" AS ReportingVersion,
+ event_id AS EventID,
+ event_description AS EventName,
+ "ObjectAccess" AS EventLogType,
+ subject_logonid AS SequenceID,
+ "" AS Severity,
+ subject_username AS SourceUserID,
+ "" AS SourceHost,
+ ip_address AS SourceIP,
+ ip_port AS SourcePort,
+ "" AS SourceMac,
+ subject_domainname AS SourceDomain,
+ "" AS ReportingHost,
+ "" AS ReportingIP,
+ "" AS ReportingPort,
+ "" AS ReportingMac,
+ target_username AS DestinationUserID,
+ "" AS DestinationHost,
+ "" AS DestinationIP,
+ "" AS DestinationPort,
+ "" AS DestinationMac,
+ target_domainname AS DestinationDomain,
+ event_description AS Action,
+ object_name AS ObjectName,
+ handle_id AS ObjectID,
+ object_type AS ObjectType,
+ "" AS ObjectCreateTime,
+ "" AS ObjectModifiedTime,
+ "" AS ObjectPath,
+ accesses AS ObjectPermission,
+ "" AS ObjectHash,
+ "" AS ObjectSize,
+ "" AS OriginalObjectName,
+ "" AS OriginalObjectID,
+ "" AS OriginalObjectType,
+ "" AS OriginalObjectCreateTime,
+ "" AS OriginalObjectModifiedTime,
+ "" AS OriginalObjectPath,
+ "" AS OriginalObjectPermission,
+ "" AS OriginalObjectHash,
+ "" AS OriginalObjectSize,
+ year,
+ month,
+ day
+FROM
+ ${hiveconf:dbname}.windows_view_partition
+WHERE
+ event_id IN (4656, 4658, 4659, 4660, 4663, 4664, 4670, 4691, 4907) AND (object_type='File' OR event_id IN (4658));
+
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.FileObjectAccessedOrChanged
+(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ EventID,
+ EventName,
+ EventLogType,
+ SequenceID,
+ Severity,
+ SourceUserID,
+ SourceHost,
+ SourceIP,
+ SourcePort,
+ SourceMac,
+ SourceDomain,
+ ReportingHost,
+ ReportingIP,
+ ReportingPort,
+ ReportingMac,
+ DestinationUserID,
+ DestinationHost,
+ DestinationIP,
+ DestinationPort,
+ DestinationMac,
+ DestinationDomain,
+ Action,
+ ObjectName,
+ ObjectID,
+ ObjectType,
+ ObjectCreateTime,
+ ObjectModifiedTime,
+ ObjectPath,
+ ObjectPermission,
+ ObjectHash,
+ ObjectSize,
+ OriginalObjectName,
+ OriginalObjectID,
+ OriginalObjectType,
+ OriginalObjectCreateTime,
+ OriginalObjectModifiedTime,
+ OriginalObjectPath,
+ OriginalObjectPermission,
+ OriginalObjectHash,
+ OriginalObjectSize,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.FileObjectAccessedOrChanged_windows;
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/MessageEvent.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/MessageEvent.sql b/spot-setup/views/hive/MessageEvent.sql
new file mode 100644
index 0000000..294933f
--- /dev/null
+++ b/spot-setup/views/hive/MessageEvent.sql
@@ -0,0 +1,77 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.MessageEvent_msexchange AS
+SELECT
+ event_time AS TimeOfEvent,
+ NULL AS TimeReceived,
+ NULL AS DeviceReceive,
+ NULL AS TimeLoaded,
+ NULL AS SyslogFacility,
+ NULL AS SyslogPriority,
+ "Microsoft" AS ReportingVendor,
+ "Exchange" AS ReportingProduct,
+ "*" AS ReportingVersion,
+ source_ip AS ClientIPAddress,
+ source_host AS ClientHostname,
+ NULL AS PartnerName,
+ destination_host AS ServerHostname,
+ destination_ip AS ServerIPAddress,
+ receiver AS RecipientEmailAddresses,
+ event_name AS EventID,
+ event_name AS EventName,
+ "Message" AS EventLogType,
+ NULL AS MessageID,
+ NULL AS RecipientReportStatus,
+ email_size AS TotalBytes,
+ no_of_receivers AS NumberOfRecipients,
+ NULL AS OriginationTime,
+ NULL AS Encryption,
+ NULL AS ServiceVersion,
+ NULL AS LinkedMessageID,
+ subject AS MessageSubject,
+ sender AS SenderEmailAddress,
+ no_of_attachments AS NumberOfAttachments,
+ year,
+ month,
+ day
+FROM ${hiveconf:dbname}.email_view_partition
+WHERE event_name NOT IN ('NOTIFYMAPI');
+
+
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.MessageEvent
+(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ ClientIPAddress,
+ ClientHostname,
+ PartnerName,
+ ServerHostname,
+ ServerIPAddress,
+ RecipientEmailAddresses,
+ EventID,
+ EventName,
+ EventLogType,
+ MessageID,
+ RecipientReportStatus,
+ TotalBytes,
+ NumberOfRecipients,
+ OriginationTime,
+ Encryption,
+ ServiceVersion,
+ LinkedMessageID,
+ MessageSubject,
+ SenderEmailAddress,
+ NumberOfAttachments,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.MessageEvent_msexchange;
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/NetworkConnection.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/NetworkConnection.sql b/spot-setup/views/hive/NetworkConnection.sql
new file mode 100644
index 0000000..d0e8cc4
--- /dev/null
+++ b/spot-setup/views/hive/NetworkConnection.sql
@@ -0,0 +1,86 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.NetworkConnection_wgtraffic AS
+SELECT
+ event_time AS TimeOfEvent,
+ '' AS TimeReceived,
+ '' AS DeviceReceived,
+ '' AS TimeLoaded,
+ '' AS SyslogFacility,
+ '' AS SyslogPriority,
+ 'WatchGuard' AS ReportingVendor,
+ 'Traffic' AS ReportingProduct,
+ '' AS ReportingVersion,
+ '' AS EventID,
+ '' AS EventName,
+ 'Network' AS EventLogType,
+ sid AS SequenceID,
+ '' AS Severity,
+ regexp_extract(info_3, 'src_user=([^ ]+)', 1) AS SourceUserID,
+ '' AS SourceHost,
+ src_ip AS SourceIP,
+ src_port AS SourcePort,
+ '' AS SourceMac,
+ '' AS SourceDomain,
+ '' AS ReportingHost,
+ '' AS ReportingIP,
+ '' AS ReportingPort,
+ '' AS ReportingMac,
+ '' AS DestinationUserID,
+ '' AS DestinationHost,
+ dst_ip AS DestinationIP,
+ dst_port AS DestinationPort,
+ '' AS DestinationMac,
+ regexp_extract(info_5, 'dstname=([^ ]+)', 1) AS DestinationDomain,
+ protocol AS Protocol,
+ regexp_extract(info_6, 'arg=([^ ]+)', 1) AS URL,
+ '' AS CLFUserAgent,
+ '' AS CLFCookies,
+ IF(instr(info_1, 'rcvd_bytes') <> 0 AND instr(info_2, 'sent_bytes') <> 0, IF (cast(regexp_extract(info_1, 'rcvd_bytes=(\\d+)', 1) AS int) < cast(regexp_extract(info_2, 'sent_bytes=(\\d+)', 1) AS int), 'Upload', 'Download'), '-') AS CLFMethod,
+ year,
+ month,
+ day
+FROM ${hiveconf:dbname}.wgtraffic_view_partition;
+
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.NetworkConnection(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ EventID,
+ EventName,
+ EventLogType,
+ SequenceID,
+ Severity,
+ SourceUserID,
+ SourceHost,
+ SourceIP,
+ SourcePort,
+ SourceMac,
+ SourceDomain,
+ ReportingHost,
+ ReportingIP,
+ ReportingPort,
+ ReportingMac,
+ DestinationUserID,
+ DestinationHost,
+ DestinationIP,
+ DestinationPort,
+ DestinationMac,
+ DestinationDomain,
+ Protocol,
+ URL,
+ CLFUserAgent,
+ CLFCookies,
+ CLFMethod,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.NetworkConnection_wgtraffic;
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/PasswordChangeOrReset.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/PasswordChangeOrReset.sql b/spot-setup/views/hive/PasswordChangeOrReset.sql
new file mode 100644
index 0000000..9e18578
--- /dev/null
+++ b/spot-setup/views/hive/PasswordChangeOrReset.sql
@@ -0,0 +1,45 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.PasswordChangeOrReset_windows AS
+SELECT
+ event_time AS TimeOfEvent,
+ "Microsoft" AS ReportingVendor,
+ "Windows" AS ReportingProduct,
+ "2008+" AS ReportingVersion,
+ "" AS ReportingHost,
+ event_id AS EventID,
+ event_description AS EventName,
+ "PasswordChangeOrReset" AS EventLogType,
+ subject_username AS SourceUserID,
+ subject_domainname AS SourceDomain,
+ target_username AS DestinationUserID,
+ target_domainname AS DestinationDomain,
+ year,
+ month,
+ day
+FROM
+ ${hiveconf:dbname}.windows_view_partition
+WHERE
+ event_id IN (4723, 4724, 4794);
+
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.PasswordChangeOrReset
+(
+ TimeOfEvent,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ ReportingHost,
+ EventID,
+ EventName,
+ EventLogType,
+ SourceUserID,
+ SourceDomain,
+ DestinationUserID,
+ DestinationDomain,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.PasswordChangeOrReset_windows;
+
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/ProcessStartupOrShutdown.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/ProcessStartupOrShutdown.sql b/spot-setup/views/hive/ProcessStartupOrShutdown.sql
new file mode 100644
index 0000000..ece3e5d
--- /dev/null
+++ b/spot-setup/views/hive/ProcessStartupOrShutdown.sql
@@ -0,0 +1,87 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.ProcessStartupOrShutdown_windows AS
+SELECT
+ event_time AS TimeOfEvent,
+ "" AS TimeReceived,
+ "" AS DeviceReceived,
+ "" AS TimeLoaded,
+ "" AS SyslogFacility,
+ "" AS SyslogPriority,
+ "Microsoft" AS ReportingVendor,
+ "Windows" AS ReportingProduct,
+ "2008+" AS ReportingVersion,
+ event_id AS EventID,
+ event_description AS EventName,
+ "Process" AS EventLogType,
+ subject_logonid AS SequenceID,
+ "" AS Severity,
+ subject_username AS SourceUserID,
+ "" AS SourceHost,
+ ip_address AS SourceIP,
+ ip_port AS SourcePort,
+ "" AS SourceMac,
+ subject_domainname AS SourceDomain,
+ "" AS ReportingHost,
+ "" AS ReportingIP,
+ "" AS ReportingPort,
+ "" AS ReportingMac,
+ target_username AS DestinationUserID,
+ "" AS DestinationHost,
+ "" AS DestinationIP,
+ "" AS DestinationPort,
+ "" AS DestinationMac,
+ target_domainname AS DestinationDomain,
+ process_name AS ProcessName,
+ process_id AS ProcessID,
+ CASE
+ WHEN event_id IN (4608, 4688, 4709, 4878, 4880, 5024, 5033, 5120, 5478) THEN "Started"
+ WHEN event_id IN (4609, 4689, 4710, 4879, 4881, 5025, 5034, 5121, 5479) THEN "Shutdown"
+ END AS Action,
+ year,
+ month,
+ day
+FROM ${hiveconf:dbname}.windows_view_partition
+WHERE event_id IN (4608, 4609, 4688, 4689, 4709, 4710, 5024, 5025, 5033, 5034, 5120, 5121, 5778, 5779);
+
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.ProcessStartupOrShutdown
+(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ EventID,
+ EventName,
+ EventLogType,
+ SequenceID,
+ Severity,
+ SourceUserID,
+ SourceHost,
+ SourceIP,
+ SourcePort,
+ SourceMac,
+ SourceDomain,
+ ReportingHost,
+ ReportingIP,
+ ReportingPort,
+ ReportingMac,
+ DestinationUserID,
+ DestinationHost,
+ DestinationIP,
+ DestinationPort,
+ DestinationMac,
+ DestinationDomain,
+ ProcessName,
+ ProcessId,
+ Action,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.ProcessStartupOrShutdown_windows;
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/SecurityObjectAccessedOrChanged.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/SecurityObjectAccessedOrChanged.sql b/spot-setup/views/hive/SecurityObjectAccessedOrChanged.sql
new file mode 100644
index 0000000..1915c2c
--- /dev/null
+++ b/spot-setup/views/hive/SecurityObjectAccessedOrChanged.sql
@@ -0,0 +1,119 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.SecurityObjectAccessedOrChanged_windows AS
+SELECT
+ event_time AS TimeOfEvent,
+ "" AS TimeReceived,
+ "" AS DeviceReceived,
+ "" AS TimeLoaded,
+ "" AS SyslogFacility,
+ "" AS SyslogPriority,
+ "Microsoft" AS ReportingVendor,
+ "Windows" AS ReportingProduct,
+ "2008+" AS ReportingVersion,
+ event_id AS EventID,
+ event_description AS EventName,
+ "ObjectAccess" AS EventLogType,
+ subject_logonid AS SequenceID,
+ "" AS Severity,
+ subject_username AS SourceUserID,
+ "" AS SourceHost,
+ ip_address AS SourceIP,
+ ip_port AS SourcePort,
+ "" AS SourceMac,
+ subject_domainname AS SourceDomain,
+ "" AS ReportingHost,
+ "" AS ReportingIP,
+ "" AS ReportingPort,
+ "" AS ReportingMac,
+ target_username AS DestinationUserID,
+ "" AS DestinationHost,
+ "" AS DestinationIP,
+ "" AS DestinationPort,
+ "" AS DestinationMac,
+ target_domainname AS DestinationDomain,
+ event_description AS Action,
+ object_name AS ObjectName,
+ handle_id AS ObjectID,
+ object_type AS ObjectType,
+ "" AS ObjectCreateTime,
+ "" AS ObjectModifiedTime,
+ "" AS ObjectPath,
+ "" AS ObjectPermission,
+ "" AS ObjectHash,
+ "" AS ObjectSize,
+ "" AS OriginalObjectName,
+ "" AS OriginalObjectID,
+ "" AS OriginalObjectType,
+ "" AS OriginalObjectCreateTime,
+ "" AS OriginalObjectModifiedTime,
+ "" AS OriginalObjectPath,
+ "" AS OriginalObjectPermission,
+ "" AS OriginalObjectHash,
+ "" AS OriginalObjectSize,
+ year,
+ month,
+ day
+FROM
+ ${hiveconf:dbname}.windows_view_partition
+WHERE
+ event_id IN (4660, 4661, 4662, 4663, 4674, 4691);
+
+
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.SecurityObjectAccessedOrChanged
+(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ EventID,
+ EventName,
+ EventLogType,
+ SequenceID,
+ Severity,
+ SourceUserID,
+ SourceHost,
+ SourceIP,
+ SourcePort,
+ SourceMac,
+ SourceDomain,
+ ReportingHost,
+ ReportingIP,
+ ReportingPort,
+ ReportingMac,
+ DestinationUserID,
+ DestinationHost,
+ DestinationIP,
+ DestinationPort,
+ DestinationMac,
+ DestinationDomain,
+ Action,
+ ObjectName,
+ ObjectID,
+ ObjectType,
+ ObjectCreateTime,
+ ObjectModifiedTime,
+ ObjectPath,
+ ObjectPermission,
+ ObjectHash,
+ ObjectSize,
+ OriginalObjectName,
+ OriginalObjectID,
+ OriginalObjectType,
+ OriginalObjectCreateTime,
+ OriginalObjectModifiedTime,
+ OriginalObjectPath,
+ OriginalObjectPermission,
+ OriginalObjectHash,
+ OriginalObjectSize,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.SecurityObjectAccessedOrChanged_windows;
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/UseOfPrivilegeCommand.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/UseOfPrivilegeCommand.sql b/spot-setup/views/hive/UseOfPrivilegeCommand.sql
new file mode 100644
index 0000000..454c159
--- /dev/null
+++ b/spot-setup/views/hive/UseOfPrivilegeCommand.sql
@@ -0,0 +1,89 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.UseOfPrivilegeCommand_windows AS
+SELECT
+ event_time AS TimeOfEvent,
+ "" AS TimeReceived,
+ "" AS DeviceReceived,
+ "" AS TimeLoaded,
+ "" AS SyslogFacility,
+ "" AS SyslogPriority,
+ "Microsoft" AS ReportingVendor,
+ "Windows" AS ReportingProduct,
+ "2008+" AS ReportingVersion,
+ event_id AS EventID,
+ event_description AS EventName,
+ "Privilege" AS EventLogType,
+ "" AS SequenceID,
+ "" AS Severity,
+ subject_username AS SourceUserID,
+ "" AS SourceHost,
+ ip_address AS SourceIP,
+ ip_port AS SourcePort,
+ "" AS SourceMac,
+ subject_domainname AS SourceDomain,
+ "" AS ReportingHost,
+ "" AS ReportingIP,
+ "" AS ReportingPort,
+ "" AS ReportingMac,
+ target_username AS DestinationUserID,
+ "" AS DestinationHost,
+ "" AS DestinationIP,
+ "" AS DestinationPort,
+ "" AS DestinationMac,
+ target_domainname AS DestinationDomain,
+ privilege_list AS Command,
+ "" AS CommandType,
+ "" AS CommandParameters,
+ status AS CommandResult,
+ year,
+ month,
+ day
+FROM
+ ${hiveconf:dbname}.windows_view_partition
+WHERE
+ event_id IN (4672, 4673);
+
+
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.UseOfPrivilegeCommand
+(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ EventID,
+ EventName,
+ EventLogType,
+ SequenceID,
+ Severity,
+ SourceUserID,
+ SourceHost,
+ SourceIP,
+ SourcePort,
+ SourceMac,
+ SourceDomain,
+ ReportingHost,
+ ReportingIP,
+ ReportingPort,
+ ReportingMac,
+ DestinationUserID,
+ DestinationHost,
+ DestinationIP,
+ DestinationPort,
+ DestinationMac,
+ DestinationDomain,
+ Command,
+ CommandType,
+ CommandParameters,
+ CommandResult,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.UseOfPrivilegeCommand_windows;
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/UserAccountAddedOrRemoved.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/UserAccountAddedOrRemoved.sql b/spot-setup/views/hive/UserAccountAddedOrRemoved.sql
new file mode 100644
index 0000000..2b86b5f
--- /dev/null
+++ b/spot-setup/views/hive/UserAccountAddedOrRemoved.sql
@@ -0,0 +1,102 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.UserAccountAddedOrRemoved_windows AS
+SELECT
+ event_time AS TimeOfEvent,
+ "" AS TimeReceived,
+ "" AS DeviceReceived,
+ "" AS TimeLoaded,
+ "" AS SyslogFacility,
+ "" AS SyslogPriority,
+ "Microsoft" AS ReportingVendor,
+ "Windows" AS ReportingProduct,
+ "2008+" AS ReportingVersion,
+ event_id AS EventID,
+ event_description AS EventName,
+ "UserAccount" AS EventLogType,
+ subject_logonid AS SequenceID,
+ "" AS Severity,
+ subject_username AS SourceUserID,
+ "" AS SourceHost,
+ ip_address AS SourceIP,
+ ip_port AS SourcePort,
+ "" AS SourceMac,
+ subject_domainname AS SourceDomain,
+ "" AS ReportingHost,
+ "" AS ReportingIP,
+ "" AS ReportingPort,
+ "" AS ReportingMac,
+ target_username AS DestinationUserID,
+ "" AS DestinationHost,
+ "" AS DestinationIP,
+ "" AS DestinationPort,
+ "" AS DestinationMac,
+ target_domainname AS DestinationDomain,
+ CASE
+ WHEN event_id IN (4720) THEN "Account Created"
+ WHEN event_id IN (4726) THEN "Account Deleted"
+ ELSE "Unknown Action"
+ END AS Action,
+ newtarget_username AS UserName,
+ "" AS UserID,
+ "" AS UserType,
+ "" AS UserCreateTime,
+ "" AS UserModifiedTime,
+ "" AS UserDomain,
+ "" AS UserPermissions,
+ "" AS UserGroups,
+ year,
+ month,
+ day
+
+FROM
+ ${hiveconf:dbname}.windows_view_partition
+WHERE
+ event_id IN (4720, 4726);
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.UserAccountAddedOrRemoved
+(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ EventID,
+ EventName,
+ EventLogType,
+ SequenceID,
+ Severity,
+ SourceUserID,
+ SourceHost,
+ SourceIP,
+ SourcePort,
+ SourceMac,
+ SourceDomain,
+ ReportingHost,
+ ReportingIP,
+ ReportingPort,
+ ReportingMac,
+ DestinationUserID,
+ DestinationHost,
+ DestinationIP,
+ DestinationPort,
+ DestinationMac,
+ DestinationDomain,
+ Action,
+ UserName,
+ UserID,
+ UserType,
+ UserCreateTime,
+ UserModifiedTime,
+ UserDomain,
+ UserPermissions,
+ UserGroups,
+ year,
+ month,
+ day
+) AS
+SELECT * FROM ${hiveconf:dbname}.UserAccountAddedOrRemoved_windows;
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/069e02d4/spot-setup/views/hive/UserLogin.sql
----------------------------------------------------------------------
diff --git a/spot-setup/views/hive/UserLogin.sql b/spot-setup/views/hive/UserLogin.sql
new file mode 100644
index 0000000..cb6d64d
--- /dev/null
+++ b/spot-setup/views/hive/UserLogin.sql
@@ -0,0 +1,89 @@
+SET hiveconf:dbname;
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.UserLogin_windows AS
+SELECT
+ event_time AS TimeOfEvent,
+ "" AS TimeReceived,
+ "" AS DeviceReceived,
+ "" AS TimeLoaded,
+ "" AS SyslogFacility,
+ "" AS SyslogPriority,
+ "Microsoft" AS ReportingVendor,
+ "Windows" AS ReportingProduct,
+ "2008+" AS ReportingVersion,
+ event_id AS EventID,
+ event_description AS EventName,
+ "Login" AS EventLogType,
+ target_logonid AS SequenceID,
+ "" AS Severity,
+ subject_username AS SourceUserID,
+ workstation_name AS SourceHost,
+ ip_address AS SourceIP,
+ ip_port AS SourcePort,
+ "" AS SourceMac,
+ subject_domainname AS SourceDomain,
+ "" AS ReportingHost,
+ "" AS ReportingIP,
+ "" AS ReportingPort,
+ "" AS ReportingMac,
+ CASE
+ WHEN event_id IN (4624) THEN newtarget_username
+ ELSE target_username
+ END AS DestinationUserID,
+ "" AS DestinationHost,
+ "" AS DestinationIP,
+ "" AS DestinationPort,
+ "" AS DestinationMac,
+ target_domainname AS DestinationDomain,
+ year,
+ month,
+ day
+FROM
+ ${hiveconf:dbname}.windows_view_partition
+WHERE event_id IN (
+ 4624,
+ 4625,
+ 4634,
+ 4647,
+ 4648);
+
+
+
+CREATE OR REPLACE VIEW ${hiveconf:dbname}.UserLogin
+(
+ TimeOfEvent,
+ TimeReceived,
+ DeviceReceived,
+ TimeLoaded,
+ SyslogFacility,
+ SyslogPriority,
+ ReportingVendor,
+ ReportingProduct,
+ ReportingVersion,
+ EventID,
+ EventName,
+ EventLogType,
+ SequenceID,
+ Severity,
+ SourceUserID,
+ SourceHost,
+ SourceIP,
+ SourcePort,
+ SourceMac,
+ SourceDomain,
+ ReportingHost,
+ ReportingIP,
+ ReportingPort,
+ ReportingMac,
+ DestinationUserID,
+ DestinationHost,
+ DestinationIP,
+ DestinationPort,
+ DestinationMac,
+ DestinationDomain,
+ year,
+ month,
+ day
+) AS
+SELECT * from ${hiveconf:dbname}.UserLogin_windows;
+