May 5, 2017 at 7:57 am
The situation is I have a table that stores my server names and their node names (all two node) in case of a cluster.CREATE TABLE [REP].[Servers](
[ServerName] [varchar](50) NOT NULL,
[Active] [bit] NULL,
[Environment] [varchar](255) NULL,
[SQLVersion] [varchar](6) NOT NULL,
[Edition] [varchar](255) NULL,
[ServicePack] [varchar](255) NULL,
[SQLBuildNumber] [varchar](15) NULL,
[InServiceDate] [datetime] NULL,
[DecommissionDate] [datetime] NULL,
[IsCluster] [bit] NULL,
[SQLNode1] [varchar](255) NULL,
[SQLNode2] [varchar](255) NULL,
[OSClusterName] [varchar](255) NULL,
[GatherInfo] [int] NOT NULL,
[LastUpdated] [datetime] NOT NULL,
[UpdatedBy] [varchar](30) NOT NULL,
[IPAddress] [varchar](max) NULL,
[Port] [int] NULL)
I have another table where I gather OS patch info with Powershell for all of my servers, over 100 of them.CREATE TABLE [REP].[ServerOSUpdates](
[UpdateID] [int] NOT NULL,
[ServerName] [varchar](200) NOT NULL,
[UpdateKBNumber] [varchar](50) NOT NULL,
[UpdateInstallDate] [datetime] NULL,
[UpdateInstalledBy] [varchar](50) NULL,
[UpdateDescription] [varchar](50) NULL,
[SampleDate] [smalldatetime] NOT NULL)
In the case of the clustered boxes, I get the actual OS name (node name) of the server that Powershell gathered the patch info for, at the time. For reporting, I would like to match the server name to the node name when running queries like you see below. In that case, I am trying to match the node name (ServerName in ServerOSUpdates) , if there is one, to the servername in the Rep.Servers table. The following does a goo job, except it only matches to the first node. I am wondering how I can tell it to match to either node columns, SQLNode1 or SQLNode2, if the server is a cluster.
This query is to find the servers that are not being captured by the process that collects the data.
Select distinct S.ServerName, IsNull(S.SQLNode1, 'NA'), IsNull(S.SQLNode2, 'NA') from [REP].[Servers] S
Left Join [REP].[ServerOSUpdates] U On
Case S.IsCluster
When 1 Then S.SQLNode1
When 0 Then S.ServerName
End = U.ServerName
Where S.Active = 1 and S.GatherInfo = 1
And U.ServerName is Null
I was thinking of something like this but it doesn't work.Case S.IsCluster
When 1 Then S.SQLNode1 Or S.SQLNode2
When 0 Then S.ServerName
End = U.ServerName
I am hoping someone has a creative way of doing this in TSQL. I want to match on ServerName, unless IsCluster = 1 Then (SQLNode1 OR SQLNode2).
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 5, 2017 at 9:14 am
maybe something like :
;with a as
(select servername from [REP].Servers where iscluster=0 and Active = 1 and GatherInfo = 1
union select [SQLNode1] from [REP].Servers where iscluster=1 and Active = 1 and GatherInfo = 1
union select [SQLNode2] from [REP].Servers where iscluster=1 and Active = 1 and GatherInfo = 1)
select * from a
left join Rep.[ServerOSUpdates] b on a.servername=b.servername
Where b.servername is null
May 13, 2017 at 8:18 am
I will try this when I get back from vacation.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply