Recon Database design HELP (newbie alert)
-
I have a scenario that I'm hoping you can help with. I need to build a Recon database that will essentially need to check 6 lists of servers against each other, and then flag which servers are missing in which lists.
To clarify, here's an example: I need to recon the Master list against the Backup, asset, Patch and Outage lists. But then I also need to check the Backup against the Master, asset, patch, outage lists and so on. Basically I need to highlight any discrepancies between any combination of these lists so that I can provide some way to comment on them.
I think I'm actually asking for a datacube, but I have no experience with that and wouldn't know where to even start!
Any insight would be greatly appreciated.
-
--Brute force? (Mind the mess, now)
create table #list1(server_nm varchar(7))
create table #list2(server_nm varchar(7))
create table #list3(server_nm varchar(7))
create table #list4(server_nm varchar(7))
create table #list5(server_nm varchar(7))
create table #list6(server_nm varchar(7))
insert into #list1 values('Server1')
insert into #list1 values('Server2')
insert into #list1 values('Server3')
insert into #list2 values('Server1')
insert into #list2 values('Server2')
insert into #list2 values('Server4')
insert into #list3 values('Server2')
insert into #list3 values('Server4')
insert into #list4 values('Server4')
insert into #list4 values('Server3')
insert into #list4 values('Server1')
insert into #list4 values('Server6')
insert into #list5 values('Server5')
insert into #list6 values('Server1')
insert into #list6 values('Server4')
insert into #list6 values('Server6')
select server_nm,
list1_ind = convert(bit, max(t.list1_ind)),
list2_ind = convert(bit, max(t.list2_ind)),
list3_ind = convert(bit, max(t.list3_ind)),
list4_ind = convert(bit, max(t.list4_ind)),
list5_ind = convert(bit, max(t.list5_ind)),
list6_ind = convert(bit, max(t.list6_ind))
into #masterlist
from (
select top 0
server_nm='',
list1_ind=0,
list2_ind=0,
list3_ind=0,
list4_ind=0,
list5_ind=0,
list6_ind=0
union
select server_nm, 1, 0, 0, 0, 0, 0 from #list1
union
select server_nm, 0, 1, 0, 0, 0, 0 from #list2
union
select server_nm, 0, 0, 1, 0, 0, 0 from #list3
union
select server_nm, 0, 0, 0, 1, 0, 0 from #list4
union
select server_nm, 0, 0, 0, 0, 1, 0 from #list5
union
select server_nm, 0, 0, 0, 0, 0, 1 from #list6
) as t
group by server_nm
select * from #masterlist order by server_nm
drop table #list1
drop table #list2
drop table #list3
drop table #list4
drop table #list5
drop table #list6
drop table #masterlist
/*
By the way, the whole "SELECT TOP 0" part of the union is
pointless and is only there to assign the subquery fields
their names without messing up the following SELECT.
*/
-
Woaw, finger trouble with last post!
What I meant to say was Thank you very much Dave for looking at this! And sorry I haven't replied earlier (and my information was so vague). I've been inspired by today's "Mind your manners" (http://www.sqlservercentral.com/columnists/siyer/2645.asp) article to do better
So have a look at this, what I was after was a cross join (never thought Id need that!) to generate a factual table with data (all combos between all the distinct list and distinct server names). Next, I do a outer join to the imported table and flag the missing ones.
Please have a look at this code:
/* create schema */
-- used as the staging table for all the lists
create table [dbo].[serverreconimport] (
[servername] [varchar] (100) collate sql_latin1_general_cp1_ci_as null ,
[listsourcename] [varchar] (100) collate sql_latin1_general_cp1_ci_as null ,
) on [primary]
-- used to hold distinct list names i.e. backup, asset, patch etc...
create table [dbo].[listsource] (
[listsourceid] [int] identity (1, 1) not null ,
[listsourcename] [varchar] (50) not null ,
constraint [pk_sourcelist] primary key clustered ([listsourceid]) on [primary]
) on [primary]
-- used to hold distinct severnames
create table [dbo].[server] (
[serverid] [int] identity (1, 1) not null ,
[servername] [varchar] (50) collate sql_latin1_general_cp1_ci_as not null ,
constraint [pk_server] primary key clustered (serverid) on [primary] ,
) on [primary]
-- this is the Factual table, and data is generated via the cross join
create table [dbo].[serverlistsource] (
[serverlistsourceid] [int] identity (1, 1) not null ,
[serverid] [int] not null ,
[listsourceid] [int] not null ,
[createdate] [datetime] null constraint [df_serverlistsource_createdate] default (getdate()),
[updatedate] [datetime] null ,
[ismissing] [tinyint] not null constraint [df_serversourcelist_todelete] default (0),
[reason] [varchar] (255) collate sql_latin1_general_cp1_ci_as not null constraint [df_serverlistsource_reason] default ('none specified'),
constraint [pk_serversourcelist] primary key clustered ([serverlistsourceid]) on [primary] ,
constraint [fk_serversourcelist_server] foreign key ([serverid]) references [dbo].[server] ([serverid]) on delete cascade ,
constraint [fk_serversourcelist_sourcelist] foreign key ([listsourceid]) references [dbo].[listsource] ([listsourceid])
) on [primary]
go
create index [ix_serverid] on [dbo].[serverlistsource]([serverid]) on [primary]
go
create index [ix_listsourceid] on [dbo].[serverlistsource]([listsourceid]) on [primary]
go
/* insert combos into factual table */
insert into serverlistsource -- insert any new combonations of listnames and servernames into the serverlistsource table that aren't present already.
(serverid, listsourceid, createdate)
select ss.serverid
,ss.listsourceid
,getdate()
from ( select s.serverid
,l.listsourceid
from server s
cross join listsource l
 
ssleft outer join serverlistsource sl on sl.serverid = ss.serverid
and sl.listsourceid = ss.listsourceid
where sl.serverid is null and sl.listsourceid is null
/* use a cursor to mark the ones that are present in the imported table, so check combonations of listid + serverid
Analyse the imported data and set all the flags in the ServerListSource table to reflect the
Data as recieved from the current imported Lists
ps: I know this is ugly; I'm sure there may be a better way to flag them, and will look into it
as soon as I have a moment.
*/
declare ccsourcelist scroll cursor for
select l.listsourceid
,l.listsourcename
from listsource l
inner join serverreconimport sri on sri.listsourcename = l.listsourcename
open ccsourcelist
fetch next from ccsourcelist into @listsourceid, @listsourcename
while (@@fetch_status <> -1)
begin
--1. update all servernames that are on the current list in @listsourcename to missing (ismissing = 1)
update serverlistsource -- with this listsourceid = 0. this way we can selectively flag the ones that we have
set ismissing = 1 -- we do have back to zero in the next step
where listsourceid = @listsourceid
and ismissing = 0
--2. flag the servers that match up in the serverlistsource back to '0' (ismissing = 0)
update serverlistsource
set ismissing = 0,
updatedate = getdate()
from serverreconimport sri inner join server s on s.servername = sri.servername
inner join listsource l on l.listsourcename = sri.listsourcename
where serverlistsource.serverid = s.serverid
and serverlistsource.listsourceid = @listsourceid
and sri.listsourcename = @listsourcename
fetch next from ccsourcelist into @listsourceid, @listsourcename
end
close ccsourcelist
deallocate ccsourcelist
/* finally, do a select for any still with a ismissing = '1' */
SELECT S.ServerName Server
,LS.ListSourceName MissingIN
FROM Server S INNER JOIN ServerListSource SL ON S.ServerID = SL.ServerID
INNER JOIN ListSource LS on LS.ListSourceID = SL.ListSourceId
WHERE SL.isMissing = 1
--============== END OF SCRIPT ===========
I've been busy adding meat to it (more server info, added contact and patch tables etc) but in essence this was what I realised I was after!
Thanks again mate!
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply