locked
T-SQL query output RRS feed

  • Question

  • Hi,

    In the following query i am getting huge amount of data repeating which i don't expect. The original query was 

    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

    But i need the data for kernelrelease. 

    This is the table i need to join and get the data

    select * from [dbo].[puppetdb_certname_facts] where fact = 'kernelrelease' and certname='xxxx'

    certname fact                   value
    XXXX kernelrelease 4.1.12-94.5.7.el7uek.x86_64

    Thursday, January 25, 2018 3:59 PM

All replies

  • 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
  • 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
  • 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