Report on multiple software labels
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.
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
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.NAMEIf 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
Answers (5)
Please log in to answer
Posted by:
dchristian
12 years ago
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.
"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.
Comments:
-
I'm looking for a simmilar report, but could we have the uninstall key or PID on the report - Kdebiasse 10 years ago
Posted by:
RichB
12 years ago
OK, here's the new code for an individual label that works great:
When modified to include more labels:
It works! I must have had a typo before. Thanks for the help.
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.
Posted by:
RichB
12 years ago
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.
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.
Posted by:
dchristian
12 years ago
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
That was you can break on LABEL_NAME.
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_NAMEThat was you can break on LABEL_NAME.
Posted by:
RichB
12 years ago
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.