T-SQL query output

All replies

• 

  

  0

  Sign in to vote

  Check puppetdb_certname_facts's   certname  and oraclehosts's hostname data and relation.

  If they are unique for each table, you should OK. It seems you have duplicate information for some rows.

  Thursday, January 25, 2018 4:18 PM
• 

  

  0

  Sign in to vote

  How can i get unique data for certname.

  For an example i should get this record when i run a query from select * from [dbo].[puppetdb_certname_facts] where fact = 'kernelrelease' and certname='xxxx'

  certname      fact      value
  usd--xxx..net      kernelrelease      4.1.12-94.5.7.el7uek.x86_64

  but in the above query then i getting many records where i should get only this record

  usd--xxx..net      kernelrelease      4.1.12-94.5.7.el7uek.x86_64

  
  

  certname      fact      value
  usd--xxx..net      kernelrelease      4.1.12-94.5.7.el7uek.x86_642.6.18-238.el5
  2.6.18-274.el5
  2.6.18-348.el5
  2.6.32-358.14.1.el6.x86_64
  2.6.32-431.11.2.el6.x86_64
  2.6.32-504.30.3.el6.x86_64
  2.6.32-642.13.1.el6.x86_64
  2.6.32-642.4.2.el6.x86_64
  2.6.32-696.13.2.el6.x86_64
  2.6.32-696.6.3.el6.x86_64
  2.6.39-200.24.1.el6uek.x86_64
  2.6.39-400.215.10.el6uek.x86_64
  2.6.39-400.215.3.el6uek.x86_64
  2.6.39-400.250.11.el6uek.x86_64
  3.10.0-327.18.2.el7.x86_64
  3.10.0-327.el7.x86_64
  3.10.0-693.11.1.el7.x86_64

  select CASE WHEN ipaddress like '%10.4.%' or ipaddress like '%10.4.%' or ipaddress like '%10.4.%' or ipaddress like '%10.4.139%'  or ipaddress like '%10.%' THEN 'DEV' 
  WHEN ipaddress not like '%10.%' or ipaddress not like '%10.4%' or ipaddress not like '%10.4.%' or ipaddress not like '%10.4%'  or ipaddress not like '%10.%' THEN 'PROD'
  
  END AS is_Prod_or_Dev
  
  ,   o.hostname
  ,      o.ipaddress
  ,      CASE WHEN o.hardware_servicetag like '%000%' THEN '' ELSE o.hardware_servicetag END as hardware_servicetag
  ,      o.oracle_sizeofd02
  ,      o.oracle_spacedused_d02
  ,      o.is_virtual
  ,      o.lsbdistdescription
  ,      o.uptime
  ,      o.productname
  ,   q.value as kernelrelease
  ,CASE o.is_virtual WHEN
  'False' THEN o.processorcount/2 ELSE o.processorcount END AS processorcount
  ,      o.memorysize
  ,      o.appname
  ,   max(w.warrantyEndDate) as LeaseEndDate
  ,   m.value as crowdstrike
  ,   n.value as tibslastlogin
  ,   p.value as reportdate
  
  from oraclehosts o
  left join hostwarranty w on w.hostname = o.hostname Collate SQL_Latin1_General_CP1_CI_AS  
  left join puppetdb_certname_facts p on p.certname = o.hostname AND p.fact = 'reportdate' 
  left join puppetdb_certname_facts m on m.certname = o.hostname AND m.fact = 'crowdstrike' 
  left join puppetdb_certname_facts n on n.certname = o.hostname AND n.fact = 'tibslastlogin'
  left join puppetdb_certname_facts q on n.certname = o.hostname AND q.fact = 'kernelrelease'
  
  group by 
      o.hostname
  ,      o.ipaddress
  ,      o.operatingsystem
  
  ,      o.hardware_servicetag
  ,      o.oracle_sizeofd02
  ,      o.oracle_spacedused_d02
  ,      o.is_virtual
  ,      o.lsbdistdescription
  ,      o.uptime
  ,      o.productname
  ,      o.processorcount
  ,      o.memorysize
  ,      o.appname
  ,m.value
  ,n.value
  ,p.value
  ,q.value
  
  
  order by reportdate desc

  Thursday, January 25, 2018 4:48 PM
• 

  

  0

  Sign in to vote

  Use a subquery or cte to retrieve only the row you want and join on the subquery.

  You may use row_number or other way to find the row you need. I don't know your other data rows and logic to get the row you need from your sample. But hope you get the idea.

  Thursday, January 25, 2018 4:59 PM