/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Would someone please give me a hand with a report I have been fighting with? I simply need to generate a detailed patching report for a specific label. What was requested of me is that for each patch, it would show the patch status for all computers that are assigned to a specific label. I was able to get the report to run for all computers in my domain, but when I try to add narrow the SQL down to a specific label, things go sour.

I took this logic (that works but is not specific to a particular label):

select P.TITLE AS PatchName, MACHINE.NAME as ServerName, IP, S.STATUS
from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P
where
MACHINE.ID = S.MACHINE_ID and
S.PATCHUID = P.UID and
P.IMPACTID = 'CRITICAL'
order by P.TITLE


And tried to insert label logic...so this part is what needs help. I am receiving: mysql error: [1054: Unknown column 'Label.Name' in 'field list'

select P.TITLE AS PatchName, MACHINE.NAME as ServerName, Label.Name, IP, S.STATUS
from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P, MACHINE_LABEL_JT, LABEL
where
MACHINE.ID = S.MACHINE_ID and
S.PATCHUID = P.UID and
P.IMPACTID = 'CRITICAL' and
MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and
MACHINE_LABEL_JT.LABEL_ID = Label.ID and
Label.Name = 'Application Servers'
order by P.TITLE


I break on PatchName. Is there something dumb I am overlooking? SQL and I have a strained relationship.

Thanks for any and all help!
Mike
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

0
MySQL is case sensitive on table names.

Change all references of label to LABEL all uppercase.
Answered 01/19/2012 by: dchristian
Red Belt

Please log in to comment
0
OMG! Thank you very much! I had a feeling it was something minor. I was definitely over-thinking this one! Works great now. Thanks again!
Answered 01/19/2012 by: mbretzke
Senior Yellow Belt

Please log in to comment
0
lol, its always the little things.
Answered 01/19/2012 by: dchristian
Red Belt

Please log in to comment
0
My SQL query look like this:

select P.TITLE AS PATCHNAME, MACHINE.NAME as SERVERNAME, LABEL.NAME, IP, S.STATUS
from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P, MACHINE_LABEL_JT, LABEL
where
MACHINE.ID = S.MACHINE_ID and
S.PATCHUID = P.UID and
P.IMPACTID = 'CRITICAL' and
MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and
MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and
LABLE.Name = '1068-HF-STONERIDGE (1068-Refreshed)'
order by P.TITLE

I am getting this error :mysql error: [1054: Unknown column 'LABLE.Name' in 'where clause'] in EXECUTE(

Can anyone assist me please?

THX
Answered 02/02/2012 by: Mariusja
Second Degree Green Belt

Please log in to comment
0
There is a typo is this line:

LABLE.Name = '1068-HF-STONERIDGE (1068-Refreshed)'

should be:
LABEL.Name = '1068-HF-STONERIDGE (1068-Refreshed)'
Answered 02/02/2012 by: mbretzke
Senior Yellow Belt

Please log in to comment
0
Thanks it is working loke a charm. Can't believe I missed a spelling error.....
Answered 02/28/2012 by: Mariusja
Second Degree Green Belt

Please log in to comment