The report below finds all machines with Adobe Acrobat Pro 7 for Windows installed. I'd like to expand the report to other similar labels to get a complete report on all Adobe Acrobat versions. In other words instead of just "LABEL.NAME='s-Ado Acrobat Pro 7 Win'" I'd like to use "LABEL.NAME LIKE 's-Ado Acrobat%'" but that doesn't work.
select MACHINE.NAME, MACHINE.SYSTEM_DESCRIPTION as DESCRIPTION,
MACHINE.IP, SUBSTRING(USER_LOGGED, LOCATE('\\\\',USER_LOGGED)+1) as USER,
MACHINE.ID as MID, SOFTWARE.ID from MACHINE,
MACHINE_SOFTWARE_JT, SOFTWARE,
SOFTWARE_LABEL_JT SWL, LABEL

where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and
SOFTWARE.ID=SWL.SOFTWARE_ID and
LABEL.ID=SWL.LABEL_ID and
LABEL.NAME='s-Ado Acrobat Pro 7 Win'
order by MACHINE.NAME

If that would work then it would report on these 16 labels rather than having to create 16 separate reports to merge. Ideally it would also GROUP BY LABEL.NAME:
s-Ado Acrobat 4.X Win
s-Ado Acrobat 5.X Win
s-Ado Acrobat 9 Pro Ext Win
s-Ado Acrobat Pro 6 Mac
s-Ado Acrobat Pro 6 Win
s-Ado Acrobat Pro 7 Mac
s-Ado Acrobat Pro 7 Win
s-Ado Acrobat Pro 8 Mac
s-Ado Acrobat Pro 8 Win
s-Ado Acrobat Pro 9 Mac
s-Ado Acrobat Pro 9 Win
s-Ado Acrobat Std 6 Mac
s-Ado Acrobat Std 6 Win
s-Ado Acrobat Std 7 Win
s-Ado Acrobat Std 8 Mac
s-Ado Acrobat Std 8 Win
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
RichB,

"LABEL.NAME LIKE 's-Ado Acrobat%'" should work....

Post your intire code with that section put in.

I bet its a silly error tripping you up.
Answered 05/20/2011 by: dchristian
Red Belt

  • I'm looking for a simmilar report, but could we have the uninstall key or PID on the report
Please log in to comment
0
OK, here's the new code for an individual label that works great:
select MACHINE.NAME as COMPUTER,
SUBSTRING(USER_LOGGED, LOCATE('\\\\',USER_LOGGED)+1) as USER,
MACHINE.SYSTEM_DESCRIPTION as DESCRIPTION, MACHINE.IP from MACHINE,
MACHINE_SOFTWARE_JT, SOFTWARE, SOFTWARE_LABEL_JT SWL, LABEL
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and
SOFTWARE.ID=SWL.SOFTWARE_ID and LABEL.ID=SWL.LABEL_ID and
LABEL.NAME='s-Ado Acrobat Pro 7 Win'
group by MACHINE.ID
order by MACHINE.NAME


When modified to include more labels:
select MACHINE.NAME as COMPUTER,
SUBSTRING(USER_LOGGED, LOCATE('\\\\',USER_LOGGED)+1) as USER,
MACHINE.SYSTEM_DESCRIPTION as DESCRIPTION, MACHINE.IP from MACHINE,
MACHINE_SOFTWARE_JT, SOFTWARE, SOFTWARE_LABEL_JT SWL, LABEL
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and
SOFTWARE.ID=SWL.SOFTWARE_ID and LABEL.ID=SWL.LABEL_ID and
LABEL.NAME LIKE 's-Ado Acrobat%'
group by MACHINE.ID
order by MACHINE.NAME

It works! I must have had a typo before. Thanks for the help.
Answered 05/20/2011 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
How can I have the report broken into groups by those 16 software labels? If I add "LABEL.NAME" to the Break On Columns field it changes it to just "NAME" when saving and then the report separates every machine instead of the labels.

Would LABEL.NAME have to be added as a column using a select? I tried breaking on column "LABEL" after adding "select LABEL.NAME as LABEL from ___" but don't know what to fill in there or if that would even work.
Answered 05/20/2011 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Richb,

Change your SQL statement to sort by Label name, then machine name.

Finally you'll need to alias you LABEL.NAME column.

Something like
LABEL.NAME AS LABEL_NAME
That was you can break on LABEL_NAME.
Answered 05/20/2011 by: dchristian
Red Belt

Please log in to comment
0
Yeah! It's working and is awesome. Here's the latest version with a Break On Columns: LABEL_NAME
select MACHINE.NAME as COMPUTER,
SUBSTRING(USER_LOGGED, LOCATE('\\\\',USER_LOGGED)+1) as USER,
MACHINE.SYSTEM_DESCRIPTION as DESCRIPTION, MACHINE.IP,
LABEL.NAME AS LABEL_NAME from MACHINE,
MACHINE_SOFTWARE_JT, SOFTWARE, SOFTWARE_LABEL_JT SWL, LABEL
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and
SOFTWARE.ID=SWL.SOFTWARE_ID and LABEL.ID=SWL.LABEL_ID and
LABEL.NAME LIKE 's-Ado Acrobat%'
group by MACHINE.ID
order by LABEL.NAME,MACHINE.NAME
Answered 05/20/2011 by: RichB
Fourth Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity