Patch Audit Log
Tracks the events emitted by the Approval engine and the events detected by the Patch Status v2 monitor. Note, the Patch Lifecycle model adds a level of interpretation to the emitted events and may therefore be a better analytic solution for your needs.
Methodology: Kimball Star Schema
Fact Type: Transactional
Grain: Patch timestamp events occurring on a device
Patch Action ANDR schema
Patch Action ANDR table joins
FACT: FACT_PATCH_ACTION_ANDR
| Dimension | Dimension key | Fact key | Cardinality |
|---|---|---|---|
|
DIM_TENANT |
TENANT_DK |
TENANT_DK |
1:N |
|
DIM_CUSTOMER |
CUSTOMER_SK |
CUSTOMER_SK |
1:N |
|
DIM_CUSTOMER_LATEST |
CUSTOMER_DK |
CUSTOMER_DK |
1:N |
|
DIM_SITE |
SITE_SK |
SITE_SK |
1:N |
|
DIM_SITE_LATEST |
SITE_DK |
SITE_DK |
1:N |
|
DIM_DEVICE_SUMMARY |
DEVICE_SK |
DEVICE_SK |
1:N |
|
DIM_DEVICE_SUMMARY_LATEST |
DEVICE_DK |
DEVICE_DK |
1:N |
|
DIM_DATE |
DATE_SK |
DATE_SK |
1:N |
|
DIM_TIME |
TIME_SK |
TIME_SK |
1:N |
|
DIM_PATCH_ANDR |
PATCH_DK |
PATCH_DK |
1:N |
|
DIM_PATCH_ACTION_STATUS_TYPE |
DIM_PATCH_ACTION_STATUS_TYPE_DK |
DIM_PATCH_ACTION_STATUS_TYPE_DK |
1:N |
Boilerplate for Patch Action ANDR
Select
-- enter columns here
From SHARED_ANALYTICS_DATA.SHARED_DATA.FACT_PATCH_ACTION_ANDR f
Join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CUSTOMER c on f.Customer_SK = c.Customer_SK
Join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CUSTOMER_LATEST cl on f.Customer_DK = cl.Customer_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_SITE s on f.Site_SK = s.Site_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_SITE_LATEST sl on f.Site_DK = sl.Site_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DEVICE_SUMMARY d on f.Device_SK = d.Device_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DEVICE_SUMMARY_LATEST dl on f.Device_DK = dl.Device_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE dt on f.Date_SK = dt.Date_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_TIME t on f.Time_SK = t.Time_SK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_PATCH_ANDR p on f.Patch_DK = p.Patch_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_PATCH_ACTION_STATUS_TYPE ps on f.DIM_PATCH_ACTION_STATUS_TYPE_DK = ps.DIM_PATCH_ACTION_STATUS_TYPE_DK
-- Where clauses here
-- Group By columns here
-- Having clauses here
-- Order by columns here