<ObjectTemplate ID="DataConsumer.Microsoft.EnterpriseManagement.ServiceManager.Connector.SCCMv5.Power" TypeID="LFX!System.LinkingFramework.DataConnector.Projection">
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/DataConnectorName$">DataProvider_Microsoft_EnterpriseManagement_ServiceManager_Connector_SCCMv5_Power_Consumer</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/IsProvider$">False</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/SolutionName$">SCCM-v5-Power</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/EntryPoint$">Microsoft.EnterpriseManagement.ServiceManager.Linking.Consumer.OperationalStore.OperationalStoreConsumer</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/EntryAssembly$">Microsoft.EnterpriseManagement.ServiceManager.Linking.Consumer.OperationalStore, Version="7.0.5000.0", Culture=neutral, PublicKeyToken="31bf3856ad364e35"</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/InstallSQLScripts$"><Script>
if not object_id('[LFXSTG].[v_DeviceHasPowerV5Activity]') is null
drop view [LFXSTG].[v_DeviceHasPowerV5Activity]
exec ('
CREATE VIEW [LFXSTG].[v_DeviceHasPowerV5Activity] AS
SELECT DISTINCT PM.Lfx_RowId AS Lfx_RowId,
PM.Lfx_SourceId,
PM.Lfx_Timestamp,
PM.Lfx_Status as Lfx_Status,
NEWID() as ''ActivityID'',
PM.Date0 as ''Date'',
PM.hr0_10 as ''Hour0'',
PM.hr1_20 as ''Hour1'',
PM.hr2_30 as ''Hour2'',
PM.hr3_40 as ''Hour3'',
PM.hr4_50 as ''Hour4'',
PM.hr5_60 as ''Hour5'',
PM.hr6_70 as ''Hour6'',
PM.hr7_80 as ''Hour7'',
PM.hr8_90 as ''Hour8'',
PM.hr9_100 as ''Hour9'',
PM.hr10_110 as ''Hour10'',
PM.hr11_120 as ''Hour11'',
PM.hr12_130 as ''Hour12'',
PM.hr13_140 as ''Hour13'',
PM.hr14_150 as ''Hour14'',
PM.hr15_160 as ''Hour15'',
PM.hr16_170 as ''Hour16'',
PM.hr17_180 as ''Hour17'',
PM.hr18_190 as ''Hour18'',
PM.hr19_200 as ''Hour19'',
PM.hr20_210 as ''Hour20'',
PM.hr21_220 as ''Hour21'',
PM.hr22_230 as ''Hour22'',
PM.hr23_00 as ''Hour23'',
COALESCE(CS.Name0, S.Netbios_Name0) + ''.'' + COALESCE(CS.Domain0, S.Resource_Domain_OR_Workgr0) AS ''PrincipalName'',
''System.Power.ActivityRecordEnum.'' + PM.TypeOfEvent0 AS ''EventType''
FROM [LFXSTG].POWERv5_vex_GS_POWER_MANAGEMENT_ACT_DAY PM
INNER JOIN [LFXSTG].POWERv5_vex_R_System S
ON PM.ResourceID=S.ResourceID AND PM.Lfx_SourceId = S.Lfx_SourceId
INNER JOIN [LFXSTG].POWERv5_vex_GS_COMPUTER_SYSTEM CS
ON S.ResourceID=CS.ResourceID
AND S.Lfx_SourceId = CS.Lfx_SourceId
')
</Script><Script>
if not object_id('[LFXSTG].[v_DeviceHasPowerV5Capability]') is null
drop view [LFXSTG].[v_DeviceHasPowerV5Capability]
exec ('
CREATE VIEW [LFXSTG].[v_DeviceHasPowerV5Capability] AS
SELECT DISTINCT PC.Lfx_RowId AS Lfx_RowId,
PC.Lfx_SourceId,
PC.Lfx_Timestamp,
PC.Lfx_Status as Lfx_Status,
NEWID() as ''CapabilityID'',
PC.ApmPresent0 as ''ApmPresent'',
PC.BatteriesAreShortTerm0 as ''BatteriesAreShortTerm'',
PC.FullWake0 as ''FullWake'',
PC.LidPresent0 as ''LidPresent'',
PC.PreferredPMProfile0 as ''PreferredPMProfile'',
PC.ProcessorThrottle0 as ''ProcessorThrottle'',
PC.SystemBatteriesPresent0 as ''SystemBatteriesPresent'',
PC.SystemS10 as ''SystemS1'',
PC.SystemS20 as ''SystemS2'',
PC.SystemS30 as ''SystemS3'',
PC.SystemS40 as ''SystemS4'',
PC.SystemS50 as ''SystemS5'',
PC.UpsPresent0 as ''UpsPresent'',
PC.VideoDimPresent0 as ''VideoDimPresent'',
PC.MinDeviceWakeState0 as ''MinDeviceWakeState'',
PC.RtcWake0 as ''RtcWake'',
COALESCE(CS.Name0, S.Netbios_Name0) + ''.'' + COALESCE(CS.Domain0, S.Resource_Domain_OR_Workgr0) AS ''PrincipalName''
FROM [LFXSTG].POWERv5_vex_GS_POWER_MANAGEMENT_CAPABIL PC
INNER JOIN [LFXSTG].POWERv5_vex_R_System S
ON PC.ResourceID=S.ResourceID AND PC.Lfx_SourceId = S.Lfx_SourceId
INNER JOIN [LFXSTG].POWERv5_vex_GS_COMPUTER_SYSTEM CS
ON S.ResourceID=CS.ResourceID
AND S.Lfx_SourceId = CS.Lfx_SourceId
')
</Script>
<Script>
if not object_id('[LFXSTG].[v_CollectionV5ContainsComputer]') is null
drop view [LFXSTG].[v_CollectionV5ContainsComputer]
exec ('
CREATE VIEW [LFXSTG].[v_CollectionV5ContainsComputer] AS
SELECT S.Lfx_RowId AS Lfx_RowId,
S.Lfx_SourceId,
(case
when isnull(S.Lfx_Timestamp,''1900-01-01'') > isnull(CS.Lfx_Timestamp,''1900-01-01'')
then S.Lfx_Timestamp
else CS.Lfx_Timestamp end) AS Lfx_Timestamp,
CM.Lfx_Status as Lfx_Status,
COALESCE(CS.Name0, S.Netbios_Name0) + ''.'' + COALESCE(CS.Domain0, S.Resource_Domain_OR_Workgr0) AS ''PrincipalName'',
C.Name as ''Name'',
C.CollID AS ''CollID'',
C.CollectionID AS ''CollectionID''
FROM [LFXSTG].POWERv5_vex_GS_COMPUTER_SYSTEM CS,
[LFXSTG].POWERv5_vex_FullCollectionMembership CM,
[LFXSTG].POWERv5_vex_Collection C,
[LFXSTG].POWERv5_vex_R_System S
WHERE
-- join system to collection membership
S.ResourceID = CM.ResourceID AND
S.Lfx_SourceId = CM.Lfx_SourceId AND
-- join collection membership to collection
CM.CollectionID=C.CollectionID AND
C.Lfx_SourceId = CM.Lfx_SourceId AND
-- join sytem to computer system
S.ResourceID=CS.ResourceID AND
S.Lfx_SourceId = CS.Lfx_SourceId AND
-- additional restrictions
S.Netbios_Name0 IS NOT NULL AND
S.Resource_Domain_OR_Workgr0 IS NOT NULL
Home
ENU