T-SQL query

Hi,

In the following query i am getting valid value as well as invalid values. Basically i wanted to see kernelrelease data. This column is needed to display for reporting purposes.

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

Before add this kernelrelease i had 256 records now i am getting 8000 above records,


This is actual table, when i run then i get one single records.
select * from [dbo].[puppetdb_certname_facts] where fact = 'kernelrelease' and certname ='xxxxxx'

How can i calculate to get a single record. Please help!
Zahid AhamedDatabase AdministratorAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Well, JOINs create products of rows, thus is not unexpected that this can happen. Depending on your data model this could be correct or indicating an error in your JOIN. So, without knowledge about your model this is hard to tell.

But there is an alias name error in your query (copy'n'paste I guess), so this may return the correct amount of rows:
 
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 q.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;

Open in new window

You had  n.certname = o.hostname AND q.fact = 'kernelrelease' instead of q.certname = o.hostname AND q.fact = 'kernelrelease',
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
I getting record but if i use max(q.value) as kernelrelease then i get 256 records. i dont need max record. How could i achieve this value

The exact record should be

Hostname      fact                                              value
usd-vv-276.ccccc.net      kernelrelease      4.1.12-94.5.7.el7uek.x86_64
0
 
Mark WillsTopic AdvisorCommented:
Just a comment on the case... Should be
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'
         ELSE 'PROD' 
         END AS is_Prod_or_Dev ,

Open in new window


I think ste5an is on top of it, so will leave the rest to him....
He identified the incorrect alias and that would have returned all 'q' rows with  q.fact = 'kernelrelease'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Zahid AhamedDatabase AdministratorAuthor Commented:
This is exact query i call a report everyday.... now management wanted to see the kernerrelease column data

select CASE WHEN ipaddress like '%10.4%' or ipaddress like '%10.4%' or ipaddress like '%10.4%' or ipaddress like '%10.4%'  or ipaddress like '%10.4.%' THEN 'DEV'
WHEN ipaddress not like '%10.4%' or ipaddress not like '%10.4%' or ipaddress not like '%10.4%' or ipaddress not like '%10.4%'  or ipaddress not like '%10.4%' 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
0
 
Zahid AhamedDatabase AdministratorAuthor Commented:
Problem is now if add q.value as kernelrelease then i am getting that exact data as well as unreleated data.



certname
usd--xxx..net      kernelrelease  
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease
usd--xxx..net      kernelrelease

value
kernelrelease
2.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
3.10.0-693.2.2.el7.x86_64
3.10.0-693.5.2.el7.x86_64
3.8.13-118.10.2.el7uek.x86_64
3.8.13-118.14.2.el6uek.x86_64
3.8.13-118.4.1.el7uek.x86_64
4.1.12-103.6.1.el7uek.x86_64
4.1.12-103.7.3.el6uek.x86_64
4.1.12-103.7.3.el7uek.x86_64
4.1.12-61.1.17.el6uek.x86_64
4.1.12-61.40.1.el6uek.x86_64
4.1.12-94.1.8.el6uek.x86_64
4.1.12-94.1.8.el7uek.x86_64
4.1.12-94.3.9.el7uek.x86_64
4.1.12-94.5.7.el6uek.x86_64
4.1.12-94.5.7.el7uek.x86_64
4.1.12-94.6.4.el6uek.x86_64
4.9.0-3-amd64
4.9.41-v7+


i should get this 4.1.12-94.5.7.el7uek.x86_64

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

i guess it is clear now.
0
 
Mark WillsTopic AdvisorCommented:
Yep,  Look at the bold/underlined table alias...

instead of :
left join puppetdb_certname_facts q on n.certname = o.hostname AND q.fact = 'kernelrelease'


use :
left join puppetdb_certname_facts q on q.certname = o.hostname AND q.fact = 'kernelrelease'
1
 
Zahid AhamedDatabase AdministratorAuthor Commented:
Thanks a lot Mark, It worked for me.
0
 
ste5anSenior DeveloperCommented:
=8)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.