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   [ + ] Show 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.

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
Answer this question or Comment on this question for clarity