Query to see value matching values from two columns

• Stamey

  SSCertifiable

  Points: 5600

  More actions

  May 5, 2017 at 7:57 am

  #402032

  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.

• ravdep

  SSC Journeyman

  Points: 99

  More actions

  May 5, 2017 at 9:14 am

  #1941334

  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

• Stamey

  SSCertifiable

  Points: 5600

  More actions

  May 13, 2017 at 8:18 am

  #1942429

  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.