question

Jockel avatar image
Jockel asked

Finding Circular looped relationships

I have a single table that contains data about relationships. The when I run a query to trace the relationships I get caught in loops because some of the relationships are incorrectly nested because logical loops exist in the data. I want to build a query that would identify these invalid looped relationships so they can be called out to be fixed.

Table has about a million rows. Each row is a one to one relationship. Query worked fine on a limited amount of test data but failed to complete on the full table.

Here is revised code without the prints that seem to have held it up the first time. Hopefully the revised presentation below will read better.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Declare @current varchar(1000)
declare @Currentrow int
--
declare @numberofrows int
declare @sql nvarchar (500)
declare @totalrows int
declare @domainacct table( acct varchar(128), rownum [int] IDENTITY(1,1) NOT NULL)
insert into @domainacct (acct)
select distinct (rtrim(DomainADAccount) + '\' + ADAccount)
	from dbo.ADMemberOF join adgroups ON admemberof.adaccount = adgroups.lansama and admemberof.domainadaccount = adgroups.domain 
	where admemberof.rowtype = 'G' and admemberof.DomainADAccount = 'UP'
			and cast(adgroups.grouptype as numeric) < 1
	order by (rtrim(DomainADAccount) + '\' + ADAccount)

set @Currentrow = 1
set @totalrows = (select count(*) from @domainacct)
select @totalrows
while (@Currentrow) < @totalrows
begin
     set @current = (select acct from @domainacct where rownum = @Currentrow)
	SET NOCOUNT ON
	DECLARE @lvl int, @line varchar(1000)
	declare @usertree table (founditem varchar(1000), foundlvl int)			         declare @stack TABLE(item varchar(1000), lvl int) --Create a tempory stack.
	INSERT INTO @stack VALUES (@current, 1) --Insert current node to the stack.
	SELECT @lvl = 1 
		WHILE @lvl > 0 --From the top level going down.
		BEGIN
		IF EXISTS (SELECT * FROM @stack WHERE lvl = @lvl)
		BEGIN
	SELECT @current = item --Find the first node that matches current node's name.
	FROM @stack
	WHERE lvl = @lvl
	SELECT @line = space(@lvl - 1) + @current --@lvl - 1 s spaces before the node name.
	insert into @usertree (founditem, foundlvl)
	values(@current, @lvl)
	DELETE FROM @stack
	WHERE lvl = @lvl
	AND item = @current --Remove the current node from the stack.
	INSERT @stack --Insert the childnodes of the current node into the stack.
	SELECT distinct DomainMemberOf + '\' + ADMemberOf, @lvl + 1
	FROM dbo.ADMemberOF 
	WHERE DomainADAccount + '\' + ADAccount = @current
	and DomainMemberOf + '\' + ADMemberOf not in (select founditem from @usertree)
	-- THIS IS THE TABLE THAT WILL CONTAIN ACCOUNTS THAT HAVE A PROBLEM
	INSERT invalid --Insert the childnodes of the current node into the stack.
	SELECT distinct DomainADAccount + '\' + ADAccount, DomainMemberOf + '\' + ADMemberOf, @lvl + 1
	FROM dbo.ADMemberOF --dbo.INV_AEX_DIS_Memberof_AD
	WHERE DomainADAccount + '\' + ADAccount = @current
	and DomainMemberOf + '\' + ADMemberOf in (select founditem from @usertree) 
	IF @@ROWCOUNT > 0 --If the previous statement added one or more nodes, go down for its first child.
	SELECT @lvl = @lvl + 1 --If no nodes are added, check its brother nodes.
	END
	   ELSE
	SELECT @lvl = @lvl - 1 --Back to the level immediately above.
	   END --While
	set @Currentrow = @Currentrow + 1
	end
	select * from invalid
loop
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wow this needs a whole lot of editing to get it looking nicer. To format it as script you need to add four spaces before each line. I would do it but I don't have the required access yet.
1 Like 1 ·
I have to say, I think you'd get a better response by explaining the data model, rather than asking people to figure it out based on your query... especially when the formatting is as it is.
1 Like 1 ·
You are right - Issue is that I have a table full of data that has relationships - These are accounts/groups assigned to groups. We have found that some of the groups have been unintentionally assigned in a circular fashion. This causes or searches for full memberships to loop back on themselves indefinitely.
0 Likes 0 ·
We have cleaned up the query- the "prints" cause the initial failure. The final query ran for 23 plus hours to run through a table with 900K rows of relationships. Source table has essentially two sets of columns, One defines the group the second defines the member of the group. One row per relationship assignment
0 Likes 0 ·
Results were written to a new table that ended up with 400K plus results. I think the logic may have misidentified invalid nested relationships. Base Hierachy code was picked up from this site.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.