<ObjectTemplate ID="DataConsumer.Microsoft.EnterpriseManagement.ServiceManager.Connector.AD" TypeID="LFX!System.LinkingFramework.DataConnector.Projection">
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/DataConnectorName$">MomStore</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/IsProvider$">False</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/SolutionName$">Active Directory Integration</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_AD_User]') is null
drop view [LFXSTG].[v_AD_User];
</Script><Script>
CREATE VIEW [LFXSTG].[v_AD_User] As
select
Lfx_RowId,
Lfx_SourceId,
Lfx_Status,
Lfx_Timestamp,
isnull(DisplayName,Domain+'\'+UserName) as DisplayName,
FirstName,
Initials,
LastName,
Company,
Department,
Office,
Title,
DistinguishedName,
Domain,
UserName,
objectguid as ObjectGuid,
objectsid as [SID],
SipAddress,
Email,
EmployeeId,
StreetAddress,
City,
[State],
Zip,
Country,
BusinessPhone,
null as BusinessPhone2,
HomePhone,
null as HomePhone2,
Fax,
Mobile,
Pager,
Locale,
FQDN,
UPN
from [LFXSTG].AD_User with (nolock) where not UserName is null
;
</Script><Script>
if not object_id('[LFXSTG].[v_NotificationEndpoint]') is null
drop view [LFXSTG].[v_NotificationEndpoint];
</Script><Script>
CREATE VIEW [LFXSTG].[v_NotificationEndpoint] As
select
Lfx_Status as Lfx_Status,
Lfx_SourceId as Lfx_SourceId,
Lfx_Timestamp as Lfx_Timestamp,
Lfx_RowId as Lfx_RowId,
'SMTP' as [ChannelName],
[Email] as [TargetAddress],
[Domain]+'_'+[UserName]+'_SMTP' as Id
from AD_User A with (nolock) where not [Email] is null and not UserName is null and not [Domain] is null
union all
select
Lfx_Status as Lfx_Status,
Lfx_SourceId as Lfx_SourceId,
Lfx_Timestamp as Lfx_Timestamp,
Lfx_RowId as Lfx_RowId,
'SIP' as [ChannelName],
[SipAddress] as [TargetAddress],
[Domain]+'_'+[UserName]+'_SIP' as Id
from [LFXSTG].AD_User A with (nolock) where not [SipAddress] is null and not UserName is null and not [Domain] is null
</Script><Script>
if not object_id('[LFXSTG].[v_AD_UserManager]') is null
drop view [LFXSTG].[v_AD_UserManager];
</Script><Script>
CREATE VIEW [LFXSTG].[v_AD_UserManager] As
select
M.Lfx_RowId,
M.Lfx_SourceId,
M.Lfx_Status,
case when isnull(M.Lfx_Timestamp,'1900-01-01') > isnull(U.Lfx_Timestamp,'1900-01-01')
then M.Lfx_Timestamp else U.Lfx_Timestamp end AS Lfx_Timestamp,
U.Domain as [UserDomain],
M.Domain as [ManagerDomain],
U.UserName as [UserName],
M.UserName as [ManagerName]
from [LFXSTG].AD_User U with (nolock)
inner join [LFXSTG].AD_User M with (nolock)
on U.manager = M.DistinguishedName and U.Lfx_SourceId = M.Lfx_SourceId
where not U.DistinguishedName is null and not M.DistinguishedName is null
and not M.UserName is null and not U.UserName is null
;
</Script><Script>
if not object_id('[LFXSTG].[v_UserNotificationPoint]') is null
drop view [LFXSTG].[v_UserNotificationPoint];
</Script><Script>
CREATE VIEW [LFXSTG].[v_UserNotificationPoint] As
select
Lfx_Status as Lfx_Status,
Lfx_SourceId as Lfx_SourceId,
Lfx_Timestamp as Lfx_Timestamp,
Lfx_RowId as Lfx_RowId,
'SMTP' as [ChannelName],
isnull([Email],'') as [TargetAddress],
[Domain] as [Domain],
[UserName] as [UserName],
[Domain]+'_'+[UserName]+'_SMTP' as Id
from [LFXSTG].AD_User A with (nolock) where not UserName is null and not [Domain] is null
union all
select
Lfx_Status as Lfx_Status,
Lfx_SourceId as Lfx_SourceId,
Lfx_Timestamp as Lfx_Timestamp,
Lfx_RowId as Lfx_RowId,
'SIP' as [ChannelName],
isnull([SipAddress],'') as [TargetAddress],
[Domain] as [Domain],
[UserName] as [UserName],
[Domain]+'_'+[UserName]+'_SIP' as Id
from [LFXSTG].AD_User A with (nolock) where not UserName is null and not [Domain] is null
;
</Script><Script>
if not object_id('[LFXSTG].[v_GroupNotificationPoint]') is null
drop view [LFXSTG].[v_GroupNotificationPoint];
</Script><Script>
CREATE VIEW [LFXSTG].[v_GroupNotificationPoint] As
select
Lfx_Status as Lfx_Status,
Lfx_SourceId as Lfx_SourceId,
Lfx_Timestamp as Lfx_Timestamp,
Lfx_RowId as Lfx_RowId,
'SMTP' as [ChannelName],
[Email] as [TargetAddress],
[Domain] as [Domain],
[UserName] as [UserName],
[Domain]+'_'+[UserName]+'_SMTP' as Id
from [LFXSTG].AD_Groups A with (nolock) where not [Email] is null and not UserName is null and not [Domain] is null
;
</Script><Script>
if not object_id('[LFXSTG].[v_AD_Computer]') is null
drop view [LFXSTG].[v_AD_Computer];
</Script><Script>
CREATE VIEW [LFXSTG].[v_AD_Computer] As
select
Lfx_RowId,
Lfx_SourceId,
Lfx_Status,
Lfx_Timestamp,
ActiveDirectorySite,
DNSName,
IPAddress,
NetworkName,
[Fullname]+'.'+[FQDN] as [PrincipalName],
DisplayName,
NetbiosComputerName,
objectsid as ActiveDirectoryObjectSid,
OrganizationalUnit,
Domain as NetbiosDomainName,
operatingSystem as OSVersionDisplayName,
operatingSystem as OSDisplayName,
operatingSystemVersion as OSVersion,
operatingSystemServicePack as ServicePackVersion
from [LFXSTG].AD_Computers with (nolock)
;
</Script><Script>
if not object_id('[LFXSTG].[v_AD_Group]') is null
drop view [LFXSTG].[v_AD_Group];
</Script><Script>
CREATE VIEW [LFXSTG].[v_AD_Group] As
select
Lfx_RowId,
Lfx_SourceId,
Lfx_Status,
Lfx_Timestamp,
isnull(DisplayName,Domain+'\'+UserName) as DisplayName,
Email,
DistinguishedName,
UserName,
objectguid as ObjectGuid,
objectsid as SID,
Domain,
FQDN,
UserName+'@'+FQDN as UPN
from [LFXSTG].AD_Groups with (nolock) where not UserName is null
;
</Script><Script>
if not object_id('[LFXSTG].[AD_ProcessUsers]') is null
drop procedure [LFXSTG].AD_ProcessUsers
</Script><Script>
CREATE PROCEDURE [LFXSTG].AD_ProcessUsers
@TableName varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
declare @ErrorMessage varchar(max)
if not exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @ErrorMessage = OBJECT_NAME(@@PROCID)+': cache table not found < '+@TableName+' >'
raiserror (@ErrorMessage, 10, 1)
end
else
begin
declare @cmd nvarchar(max)
set @cmd = N'
update [LFXSTG].AD_User
set Lfx_Status = ''D'',
Lfx_Timestamp = t.Lfx_Timestamp
from [LFXSTG].AD_User
inner join '+@TableName + ' t
on [LFXSTG].AD_User.objectguid = t.objectguid
and [LFXSTG].AD_User.Lfx_SourceId = t.Lfx_SourceId
where [LFXSTG].AD_User.Domain <> t.Domain
or [LFXSTG].AD_User.UserName <> t.UserName'
exec (@cmd)
end
END
</Script><Script>
if not object_id('[LFXSTG].[AD_ProcessComputers]') is null
drop procedure [LFXSTG].AD_ProcessComputers
</Script><Script>
CREATE PROCEDURE [LFXSTG].AD_ProcessComputers
@TableName varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
declare @ErrorMessage varchar(max)
if not exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @ErrorMessage = OBJECT_NAME(@@PROCID)+': cache table not found < '+@TableName+' >'
raiserror (@ErrorMessage, 18, 1)
end
else
begin
declare @cmd nvarchar(max)
set @cmd = N'
update [LFXSTG].AD_Computers
set Lfx_Status = ''D'',
Lfx_Timestamp = t.Lfx_Timestamp
from [LFXSTG].AD_Computers
inner join '+@TableName + ' t
on [LFXSTG].AD_Computers.objectguid = t.objectguid
and [LFXSTG].AD_Computers.Lfx_SourceId = t.Lfx_SourceId
where [LFXSTG].AD_Computers.[Fullname] <> t.[Fullname]
or [LFXSTG].AD_Computers.[FQDN] <> t.[FQDN]'
exec (@cmd)
end
END
</Script><Script>
if not object_id('[LFXSTG].[AD_ProcessPrinters]') is null
drop procedure [LFXSTG].AD_ProcessPrinters
</Script><Script>
CREATE PROCEDURE [LFXSTG].AD_ProcessPrinters
@TableName varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
declare @ErrorMessage varchar(max)
if not exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @ErrorMessage = OBJECT_NAME(@@PROCID)+': cache table not found < '+@TableName+' >'
raiserror (@ErrorMessage, 18, 1)
end
else
begin
declare @cmd nvarchar(max)
set @cmd = N'
update [LFXSTG].AD_Printers
set Lfx_Status = ''D'',
Lfx_Timestamp = t.Lfx_Timestamp
from [LFXSTG].AD_Printers
inner join '+@TableName + ' t
on [LFXSTG].AD_Printers.objectguid = t.objectguid
and [LFXSTG].AD_Printers.Lfx_SourceId = t.Lfx_SourceId
where [LFXSTG].AD_Printers.UNCName <> t.UNCName
'
exec (@cmd)
end
END
</Script><Script>
if not object_id('[LFXSTG].[AD_ProcessGroups]') is null
drop procedure [LFXSTG].AD_ProcessGroups
</Script><Script>
CREATE PROCEDURE [LFXSTG].AD_ProcessGroups
@TableName varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
declare @ErrorMessage varchar(max)
if not exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @ErrorMessage = OBJECT_NAME(@@PROCID)+': cache table not found < '+@TableName+' >'
raiserror (@ErrorMessage, 18, 1)
end
else
begin
declare @cmd nvarchar(max)
set @cmd = N'
update [LFXSTG].AD_Groups
set Lfx_Status = ''D'',
Lfx_Timestamp = t.Lfx_Timestamp
from [LFXSTG].AD_Groups
inner join '+@TableName + ' t
on [LFXSTG].AD_Groups.objectguid = t.objectguid
and [LFXSTG].AD_Groups.Lfx_SourceId = t.Lfx_SourceId
where [LFXSTG].AD_Groups.Domain <> t.Domain
or [LFXSTG].AD_Groups.UserName <> t.UserName'
exec (@cmd)
end
END
</Script></Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataConnector']/UninstallSQLScripts$"><Script>
if not object_id('[LFXSTG].[v_AD_User]') is null
drop view [LFXSTG].[v_AD_User];
</Script><Script>
if not object_id('[LFXSTG].[v_AD_UserManager]') is null
drop view [LFXSTG].[v_AD_UserManager];
</Script><Script>
if not object_id('[LFXSTG].[v_AD_Computer]') is null
drop view [LFXSTG].[v_AD_Computer];
</Script><Script>
if not object_id('[LFXSTG].[v_AD_Group]') is null
drop view [LFXSTG].[v_AD_Group];
</Script><Script>
if not object_id('[LFXSTG].[v_NotificationEndpoint]') is null
drop view [LFXSTG].[v_NotificationEndpoint];
</Script><Script>
if not object_id('[LFXSTG].[v_UserNotificationPoint]') is null
drop view [LFXSTG].[v_UserNotificationPoint];
</Script><Script>
if not object_id('[LFXSTG].[v_GroupNotificationPoint]') is null
drop view [LFXSTG].[v_GroupNotificationPoint];
</Script><Script>
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[LFXSTG].AD_ProcessUsers') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [LFXSTG].AD_ProcessUsers
</Script><Script>
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[LFXSTG].AD_ProcessComputers') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [LFXSTG].AD_ProcessComputers
</Script><Script>
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[LFXSTG].AD_ProcessPrinters') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [LFXSTG].AD_ProcessPrinters
</Script><Script>
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[LFXSTG].AD_ProcessGroups') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [LFXSTG].AD_ProcessGroups
</Script></Property>
<Object Path="$Context/Path[Relationship='LFX!System.LinkingFramework.ConnectorEmbedsTables' SeedRole='Source' TypeConstraint='LFX!System.LinkingFramework.DataTable']$">
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/DataName$">AD_User_Out</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/WatermarkField$">A.Lfx_Timestamp</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/WatermarkType$">0</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/BatchIdField$">A.Lfx_RowId</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/BatchIdType$">0</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/BatchIdSize$">50</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/UseCache$">false</Property>
<Property Path="$Context/Property[Type='LFX!System.LinkingFramework.DataTable']/QueryString$">
Home
JPN