USE [sophos4]
GO
/****** Object:  UserDefinedFunction [dbo].[ExtractTextSegments]    Script Date: 10/30/2009 09:32:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER FUNCTION [dbo].[ExtractTextSegments]
(
	@in NTEXT,
	@left NVARCHAR(256),
	@right NVARCHAR(256)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @Ret NVARCHAR(MAX);
	SET @Ret = '';
	DECLARE @offset INT, @start INT, @end INT, @rightlen INT;
	SELECT	@offset=1, 
			@start=0, 
			@end=0, 
			@rightlen = LEN(@right);
	WHILE 1=1
	BEGIN
		SELECT @start = PATINDEX('%'+@left+'%',SUBSTRING(@in,@offset,4000)),
			   @end	  = PATINDEX('%'+@right+'%',SUBSTRING(@in,@offset,4000));
		if COALESCE(@start,0) = 0 OR COALESCE(@end,0) = 0 
		BEGIN
			break;
		END
		SELECT @Ret = @Ret + SUBSTRING(@in,@offset+@start-1,@end-@start+@rightlen),
			   @offset = @offset + @end + @rightlen - 1;
	END
	RETURN @Ret;
END

GO


USE [sophos4]
GO
/****** Object:  StoredProcedure [dbo].[ReportRun_UpdateConfiguration]    Script Date: 10/30/2009 09:36:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ReportRun_UpdateConfiguration]
	@RowsetFilter INT = 0
AS  BEGIN
	SET NOCOUNT ON;
	DECLARE @DefaultPolicyTag NVARCHAR(64)
	SET @DefaultPolicyTag = N'*Default*'
	DECLARE @PolicyID INT, @xml NVARCHAR(MAX), @ComputerID INT;
	if OBJECT_ID('tempdb..#XMLParserOutput') IS NOT NULL
	BEGIN
		DROP TABLE #XMLParserOutput;
	END;
	CREATE TABLE #XMLParserOutput
	(
		Source nvarchar(512) COLLATE DATABASE_DEFAULT,
		Value1 NVARCHAR(1048) COLLATE DATABASE_DEFAULT,
		Value2 NVARCHAR(1048) COLLATE DATABASE_DEFAULT,
		Value3 NVARCHAR(1048) COLLATE DATABASE_DEFAULT,
		RN INT IDENTITY(1,1)
	);
	DECLARE @UpstreamPolicyLocations TABLE
	(
		PolicyID INT,
		Location NVARCHAR(1024),
		Position INT
	);
	DECLARE curUpstream CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
			SELECT ID, PolicyXML FROM dbo.Policies WHERE Type=9;
	OPEN curUpstream;
	FETCH NEXT FROM curUpstream INTO @PolicyID, @xml;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM #XMLParserOutput
		INSERT INTO #XMLParserOutput EXEC dbo.XMLParser3V @xml,
												@PolicyID , 										
												'<root xmlns:c="http://www.sophos.com/msys/sddm/common.xsd" />' ,
												'//c:sourceSite[not(c:custom)]',
												'.//@uri',
												'@searchOrder'
		INSERT INTO @UpstreamPolicyLocations SELECT Source,Value1,Value2 FROM #XMLParserOutput
		FETCH NEXT FROM curUpstream INTO @PolicyID, @xml
	END;
	CLOSE curUpstream;
	DEALLOCATE curUpstream;
	DECLARE @DownstreamPolicyLocations TABLE
	(
		PolicyID INT,
		Location NVARCHAR(1024),
		Position INT
	);
	DECLARE curDownstream CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
			SELECT ID, PolicyXML FROM dbo.Policies WHERE Type=10;
	OPEN curDownstream;
	FETCH NEXT FROM curDownstream INTO @PolicyID, @xml;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM #XMLParserOutput;
		INSERT INTO #XMLParserOutput EXEC dbo.XMLParser3V @xml,
												@PolicyID , 										
												'<root xmlns:c="http://www.sophos.com/msys/sddm/common.xsd" />' ,
												'//c:targetSite[not(c:custom)]',
												'.//@uri';
		INSERT INTO @DownstreamPolicyLocations SELECT Source,Value1,0 FROM #XMLParserOutput;
		FETCH NEXT FROM curDownstream INTO @PolicyID, @xml;
	END
	CLOSE curDownstream;
	DEALLOCATE curDownstream;
	INSERT INTO @DownstreamPolicyLocations 
		SELECT p.ID, N'\\'+c.Name+N'\SophosUpdate' , 0
			FROM dbo.Policies p 
				INNER JOIN dbo.ComputerPolicyMapping cpm ON p.ID=cpm.PolicyID
				INNER JOIN dbo.AliveComputers c ON cpm.ComputerID=c.ID
			WHERE p.Type=10;
	DECLARE @UpdatePolicyLocations TABLE
	(
		PolicyID INT,
		Location NVARCHAR(1024),
		Position INT
	);
	DECLARE curUpdate CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
			SELECT ID, PolicyXML FROM dbo.Policies WHERE Type=18;
	OPEN curUpdate;
	FETCH NEXT FROM curUpdate INTO @PolicyID, @xml;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM #XMLParserOutput;
		INSERT INTO #XMLParserOutput EXEC dbo.XMLParser3V @xml,
												@PolicyID , 										
												'<root xmlns:u="http://www.sophos.com/xml/msys/updatingpolicy.xsd" />' ,
												'//u:sourceSite',
												'.//@uri',
												'@searchOrder';
		INSERT INTO @UpdatePolicyLocations SELECT Source,Value1,Value2 FROM #XMLParserOutput;
		FETCH NEXT FROM curUpdate INTO @PolicyID, @xml;
	END;
	CLOSE curUpdate;
	DEALLOCATE curUpdate;
	DECLARE @LegacyUpdatePolicyLocations TABLE
	(
		PolicyID INT,
		Location NVARCHAR(1024),
		Position INT
	);
	DECLARE curLegacyUpdate CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
			SELECT ID,  N'<r>' + dbo.ExtractTextSegments(PolicyXML, '<primary_location>', '</primary_location>' ) +
					   dbo.ExtractTextSegments(PolicyXML, '<secondary_location>', '</secondary_location>' ) + N'</r>'
			FROM dbo.Policies 
			WHERE Type=1 AND Name NOT LIKE N'*%'; 
	OPEN curLegacyUpdate;
	FETCH NEXT FROM curLegacyUpdate INTO @PolicyID, @xml;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM #XMLParserOutput;
		INSERT INTO #XMLParserOutput EXEC dbo.XMLParser3V @xml,
												@PolicyID , 										
												'' ,
												'//server',
												'./@ConnectionAddress',
												'./parent::primary_location',
												'./parent::secondary_location';
		INSERT INTO @LegacyUpdatePolicyLocations SELECT Source,Value1,
														CASE WHEN Value3 IS NOT NULL THEN  2 
															 WHEN Value2 IS NOT NULL THEN  1 
															 ELSE 0
														 END
												  FROM #XMLParserOutput WHERE Value1<>N'';
		FETCH NEXT FROM curLegacyUpdate INTO @PolicyID, @xml;
	END
	CLOSE curLegacyUpdate;
	DEALLOCATE curLegacyUpdate;
	DECLARE @EMLibraryCIDPaths TABLE
	(
		ComputerID INT,
		CIDpath NVARCHAR(1024)
	);
	DECLARE curEML CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
			SELECT ComputerID, N'<root>'+dbo.ExtractTextSegments(StatusXML,N'<path>',N'</path>')+N'</root>' FROM dbo.EMLibraryServers;
	OPEN curEML;
	FETCH NEXT FROM curEML INTO @ComputerID, @xml;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM #XMLParserOutput;
		INSERT INTO #XMLParserOutput EXEC dbo.XMLParser3V @xml,
												@ComputerID , 										
												'' ,
												'//path';
		INSERT INTO @EMLibraryCIDPaths SELECT Source,Value1 FROM #XMLParserOutput;
		FETCH NEXT FROM curEML INTO @ComputerID, @xml;
	END;
	CLOSE curEML;
	DEALLOCATE curEML;
	if OBJECT_ID('tempdb..#ReportRun_UpdateConfiguration_actual1') IS NULL
	BEGIN
		CREATE TABLE  #ReportRun_UpdateConfiguration_actual1 
		(
			ComputerHost			NVARCHAR(512),
			Share					NVARCHAR(1024),
			LocationListPosition	INT,
			DomainName				NVARCHAR(512),
			DNSName					NVARCHAR(512),
			IPAddress				BIGINT,
			RN						INT IDENTITY(1,1)
		);
	END	;
	IF @RowsetFilter = 0 OR @RowsetFilter = 1
	BEGIN
		DELETE FROM #ReportRun_UpdateConfiguration_actual1;
	INSERT INTO #ReportRun_UpdateConfiguration_actual1 (ComputerHost,Share,LocationListPosition,DomainName,DNSName,IPAddress)
			SELECT	c.Name						AS ComputerHost, 
					pl.Location					AS Share, 
					pl.Position					AS LocationListPosition,
					COALESCE(c.DomainName,N'')	AS DomainName,  
					COALESCE(c.DNSName,N'')		AS DNSName,		
					COALESCE(c.IPAddress,0)		AS IPAddress	
				FROM @UpstreamPolicyLocations pl 
					 INNER JOIN dbo.ComputerPolicyMapping cpm ON pl.PolicyID = cpm.PolicyID
					 INNER JOIN dbo.AliveComputers c ON c.ID = cpm.ComputerID
				WHERE cpm.Type = 9
			ORDER BY c.Name, pl.Position;
		SELECT * FROM #ReportRun_UpdateConfiguration_actual1 ORDER BY RN;
	END;
	IF OBJECT_ID('tempdb..#ReportRun_UpdateConfiguration_actual2') IS NULL
	BEGIN
		CREATE TABLE  #ReportRun_UpdateConfiguration_actual2 
		(
			ComputerHost	NVARCHAR(512),
			Share			NVARCHAR(1024),
			EndpointsUsing1	INT,
			EndpointsUsing2 INT,
			DomainName		NVARCHAR(512),
			DNSName			NVARCHAR(512),
			IPAddress		BIGINT,
			RN				INT IDENTITY(1,1)
		);
	END;	
	IF @RowsetFilter = 0 OR @RowsetFilter = 2
	BEGIN
		DELETE FROM #ReportRun_UpdateConfiguration_actual2;
		INSERT INTO #ReportRun_UpdateConfiguration_actual2 (ComputerHost,Share,EndpointsUsing1,EndpointsUsing2,DomainName,DNSName,IPAddress)
				SELECT	COALESCE(c.Name,N'')		AS ComputerHost, 
						COALESCE(upd2.Location,down.Location,N'') AS Share,
						COALESCE(upd2.EndpointsUsing1,0) AS EndpointsUsing1,
						COALESCE(upd2.EndpointsUsing2,0) AS EndpointsUsing2,
						COALESCE(c.DomainName,N'')	AS DomainName,		
						COALESCE(c.DNSName,N'')		AS DNSName,			
						COALESCE(c.IPAddress,0)		AS IPAddress		
				FROM 
					(
						SELECT upd1.Location,SUM(upd1.EndpointsUsing1) AS EndpointsUsing1,SUM(upd1.EndpointsUsing2) AS EndpointsUsing2
						FROM (
								SELECT	upd.PolicyID				AS PolicyID, 
										upd.Location				AS Location, 
										upd.Position				AS Position, 
										CASE WHEN upd.Position = 1 THEN ISNULL(endp1.EndpointsUsing,0) ELSE 0 END AS EndpointsUsing1, 
										CASE WHEN upd.Position = 2 THEN ISNULL(endp1.EndpointsUsing,0) ELSE 0 END AS EndpointsUsing2
								FROM @UpdatePolicyLocations upd
									 LEFT OUTER JOIN (SELECT p.ID AS PolicyID, count(cpm.PolicyID) AS EndpointsUsing 
														FROM dbo.Policies p LEFT OUTER JOIN 
															 dbo.ComputerPolicyMapping cpm ON p.ID=cpm.PolicyID
														WHERE cpm.Type = 18 AND
															  cpm.ComputerID IN (SELECT ComputerID FROM dbo.ComputerPolicyMapping WHERE PolicyID IN (SELECT ID FROM dbo.Policies WHERE Type=1 AND Name LIKE N'*%' ))															
														GROUP BY p.ID) endp1 ON endp1.PolicyID = upd.PolicyID
							 ) upd1
						GROUP BY Location 
					) upd2
					 FULL OUTER JOIN @DownstreamPolicyLocations down ON down.Location = upd2.Location
					 LEFT OUTER JOIN dbo.ComputerPolicyMapping cpm ON down.PolicyID = cpm.PolicyID
					 LEFT OUTER JOIN dbo.AliveComputers c ON c.ID = cpm.ComputerID
				ORDER BY COALESCE(c.Name,N''), COALESCE(upd2.Location,down.Location,N'');
		SELECT * FROM #ReportRun_UpdateConfiguration_actual2 ORDER BY RN;
	END
	if OBJECT_ID('tempdb..#ReportRun_UpdateConfiguration_actual3') IS NULL
	BEGIN
		CREATE TABLE  #ReportRun_UpdateConfiguration_actual3
		(
			ComputerHost			NVARCHAR(512),
			Share					NVARCHAR(1024),
			PolicyName				NVARCHAR(512),
			LocationListPosition	INT,
			DomainName				NVARCHAR(512),
			DNSName					NVARCHAR(512),
			IPAddress				BIGINT,
			RN						INT IDENTITY(1,1)
		);
	END;
	IF @RowsetFilter = 0 OR @RowsetFilter = 3
	BEGIN
		DELETE FROM #ReportRun_UpdateConfiguration_actual3;
		INSERT INTO #ReportRun_UpdateConfiguration_actual3 (ComputerHost,Share,PolicyName,LocationListPosition,DomainName,DNSName,IPAddress)
			SELECT	COALESCE(c.Name,N'')		AS ComputerHost, 
					COALESCE(upd.Location,down.Location,'')	AS Share, 
					CASE WHEN def.Value IS NULL THEN COALESCE(p.Name,'') ELSE @DefaultPolicyTag END	AS PolicyName,
					COALESCE(upd.Position,0)	AS LocationListPosition,
					COALESCE(c.DomainName,N'')	AS DomainName,  
					COALESCE(c.DNSName,N'')		AS DNSName,		
					COALESCE(c.IPAddress,0)		AS IPAddress	
				FROM  @UpdatePolicyLocations upd
					 INNER JOIN dbo.Policies p ON upd.PolicyID = p.ID			
					 FULL OUTER JOIN @DownstreamPolicyLocations down ON upd.Location = down.Location
					 LEFT OUTER JOIN dbo.ComputerPolicyMapping cpmdown ON down.PolicyID = cpmdown.PolicyID
					 LEFT OUTER JOIN dbo.AliveComputers c ON c.ID = cpmdown.ComputerID
					 LEFT OUTER JOIN dbo.Defaults def ON p.ID=def.Value
				ORDER BY COALESCE(upd.Location,down.Location,''), COALESCE(c.Name,N''),COALESCE(p.Name,'');
		SELECT * FROM #ReportRun_UpdateConfiguration_actual3 ORDER BY RN;
	END;
	if OBJECT_ID('tempdb..#ReportRun_UpdateConfiguration_actual4') IS NULL
	BEGIN
		CREATE TABLE  #ReportRun_UpdateConfiguration_actual4
		(
			PolicyName	 NVARCHAR(512),
			GroupName	 NVARCHAR(512),
			GroupPath	 NVARCHAR(2048),
			NumberOfEndpoints INT,
			RN INT IDENTITY(1,1)
		);
	END;
	IF @RowsetFilter = 0 OR @RowsetFilter = 4
	BEGIN
		DELETE FROM #ReportRun_UpdateConfiguration_actual4;
		INSERT INTO #ReportRun_UpdateConfiguration_actual4 (PolicyName,GroupName,GroupPath,NumberOfEndpoints)
			SELECT	CASE WHEN def.Value IS NULL THEN COALESCE(p.Name,'') ELSE @DefaultPolicyTag END	AS PolicyName,
					COALESCE(g.Name,N'')				AS GroupName,
					COALESCE(gp.PathAndName,N'')		AS GroupPath,
					COALESCE(endp.NumberOfEndpoints,0)	AS NumberOfEndpoints
				FROM dbo.Policies p 
					 LEFT OUTER JOIN dbo.GroupPolicyMapping gpm ON p.ID=gpm.PolicyID
					 LEFT OUTER JOIN dbo.Groups g ON gpm.GroupID=g.ID
					 LEFT OUTER JOIN dbo.GroupPathAndNameTable() gp ON gpm.GroupID=gp.GroupID
					 LEFT OUTER JOIN (SELECT GroupID, count(*) AS NumberOfEndpoints 
										FROM dbo.ComputerGroupMapping cpm 
										GROUP BY GroupID) endp ON endp.GroupID = gpm.GroupID
					 LEFT OUTER JOIN dbo.Defaults def ON p.ID=def.Value
				WHERE p.Type=18 AND 
					  gpm.GroupID IN (SELECT GroupID FROM dbo.GroupPolicyMapping WHERE PolicyID IN (SELECT ID FROM dbo.Policies WHERE Type=1 AND Name LIKE N'*%' ))
				ORDER by p.Name, COALESCE(gp.PathAndName,N'');
		SELECT * FROM #ReportRun_UpdateConfiguration_actual4 ORDER BY RN;
	END
	if OBJECT_ID('tempdb..#ReportRun_UpdateConfiguration_actual5') IS NULL
	BEGIN
		CREATE TABLE  #ReportRun_UpdateConfiguration_actual5
		(
			ComputerHost NVARCHAR(512),
			Share		 NVARCHAR(1024),
			PolicyName	 NVARCHAR(512),
			LocationListPosition INT,
			DomainName	 NVARCHAR(512),
			DNSName		 NVARCHAR(512),
			IPAddress	 BIGINT,
			RN INT IDENTITY(1,1)
		);
	END;
	IF @RowsetFilter = 0 OR @RowsetFilter = 5
	BEGIN
		DELETE FROM #ReportRun_UpdateConfiguration_actual5;
		INSERT INTO #ReportRun_UpdateConfiguration_actual5 (ComputerHost,Share,PolicyName,LocationListPosition,DomainName,DNSName,IPAddress)
			SELECT	COALESCE(c.Name,N'')		AS ComputerHost, 
					COALESCE(upd.Location,eml.CIDpath,N'')	AS Share, 
					CASE WHEN def.Value IS NULL THEN COALESCE(p.Name,'') ELSE @DefaultPolicyTag END	AS PolicyName,
					COALESCE(upd.Position,N'')	AS LocationListPosition,
					COALESCE(c.DomainName,N'')	AS DomainName,  
					COALESCE(c.DNSName,N'')		AS DNSName,		
					COALESCE(c.IPAddress,0)		AS IPAddress	
				FROM  @LegacyUpdatePolicyLocations upd
					 INNER JOIN dbo.Policies p ON upd.PolicyID = p.ID			
					 FULL OUTER JOIN @EMLibraryCIDPaths eml ON upd.Location = eml.CIDpath
					 LEFT OUTER JOIN dbo.AliveComputers c ON c.ID = eml.ComputerID
					 LEFT OUTER JOIN dbo.Defaults def ON p.ID=def.Value
				ORDER BY COALESCE(upd.Location,eml.CIDpath,N''), COALESCE(p.Name,N'');
		SELECT * FROM #ReportRun_UpdateConfiguration_actual5 ORDER BY RN;
	END;
	if OBJECT_ID('tempdb..#ReportRun_UpdateConfiguration_actual6') IS NULL
	BEGIN
		CREATE TABLE  #ReportRun_UpdateConfiguration_actual6
		(
			PolicyName	 NVARCHAR(512),
			GroupName	 NVARCHAR(512),
			GroupPath	 NVARCHAR(2048),
			NumberOfEndpoints INT,
			RN INT IDENTITY(1,1)
		);
	END;
	IF @RowsetFilter = 0 OR @RowsetFilter = 6
	BEGIN
		DELETE FROM #ReportRun_UpdateConfiguration_actual6;
		INSERT INTO #ReportRun_UpdateConfiguration_actual6 (PolicyName,GroupName,GroupPath,NumberOfEndpoints)
			SELECT	CASE WHEN def.Value IS NULL THEN COALESCE(p.Name,'') ELSE @DefaultPolicyTag END	AS PolicyName,
					COALESCE(g.Name,N'')				AS GroupName,
					COALESCE(gp.PathAndName,N'')		AS GroupPath,
					COALESCE(endp.NumberOfEndpoints,0)	AS NumberOfEndpoints
				FROM dbo.Policies p 
					 LEFT OUTER JOIN dbo.GroupPolicyMapping gpm ON p.ID=gpm.PolicyID
					 LEFT OUTER JOIN dbo.Groups g ON gpm.GroupID=g.ID
					 LEFT OUTER JOIN dbo.GroupPathAndNameTable() gp ON gpm.GroupID=gp.GroupID
					 LEFT OUTER JOIN (SELECT GroupID, count(*) AS NumberOfEndpoints 
								FROM dbo.ComputerGroupMapping cpm GROUP BY GroupID) endp ON endp.GroupID = gpm.GroupID
					 LEFT OUTER JOIN dbo.Defaults def ON p.ID=def.Value
				WHERE p.Type=1 AND p.Name NOT LIKE N'*%'
				ORDER by p.Name, COALESCE(gp.PathAndName,N'');
		SELECT * FROM #ReportRun_UpdateConfiguration_actual6 ORDER BY RN;
	END
	if OBJECT_ID('tempdb..#XMLParserOutput') IS NOT NULL
	BEGIN
		DROP TABLE #XMLParserOutput;
	END;
    RETURN 0;
END;

GO