Hi

AM trying to write a report that returns all versions of Adobe products inclusive of hostname and then by last log on user.
It is the last log on user components that are mucking me up as it works fine without the last logged on user (or top console user)

Could someone please help as I am no SQL guru : )
Ta


select a.name0, AD_Site_Name0, b.DisplayName0,b.Publisher0,b.Version0,LastLogon0
from V_R_system a,v_Add_Remove_Programs b,v_FullCollectionMembership c
where a.ResourceID=b.ResourceID and a.ResourceID=c.ResourceID and
(DisplayName0 = 'Adobe Creative Suite 5 Design Standard' or
DisplayName0 = 'Adobe Creative Suite 5 Design Premium' or
DisplayName0 = 'Adobe Creative Suite 5 Design Standard' or
DisplayName0 = 'Adobe Creative Suite 5 Master Collection' or
DisplayName0 = 'Adobe Creative Suite 5 Production Premium' or
DisplayName0 = 'Adobe Creative Suite 5.5 Design Premium' or
DisplayName0 = 'Adobe Creative Suite 5.5 Design Standard' or
DisplayName0 = 'Adobe Creative Suite 5.5 Master Collection' or
DisplayName0 = 'Adobe Design Standard CS5' or
DisplayName0 = 'Adobe After Effects CS5' or
DisplayName0 = 'Adobe Fireworks CS5' or
DisplayName0 = 'Adobe Flash Professional CS5' or
DisplayName0 = 'Adobe Illustrator CS5' or
DisplayName0 = 'Adobe Illustrator CS5.5' or
DisplayName0 = 'Adobe InDesign CS5' or
DisplayName0 = 'Adobe InDesign CS5.5' or
DisplayName0 = 'Adobe Photoshop CS5' or
DisplayName0 = 'Adobe Photoshop CS5.5' or
DisplayName0 = 'Adobe Illustrator CS5' or
DisplayName0 = 'Adobe Illustrator CS5.5' or
DisplayName0 = 'Adobe Premiere CS5' or
DisplayName0 = 'Adobe Premiere CS5.5' or
DisplayName0 = 'Adobe Dreamweaver CS5' or
DisplayName0 = 'Adobe Dreamweaver CS5.5' or
DisplayName0 = 'Adobe InDesign CS5' or


DisplayName0 = 'Adobe Design Standard CS5 64-bit'
)
group by a.name0, AD_Site_Name0, b.DisplayName0,b.Publisher0,b.Version0,LastLogon0
order by AD_Site_Name0, b.DisplayName0
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2
I'm not sure about the table names (we don't have SCCM) but if you wanted to find all versions of Adobe you could do this:
DisplayName0 like '%Adobe%'

It'll save you some typing.
Answered 04/04/2012 by: dchristian
Red Belt

Please log in to comment
2
Thanks but I did need the complete breakdown of products, as there are more versions installed

Resolved Report Query

select a.name0, AD_Site_Name0, b.DisplayName0,b.Publisher0,b.Version0,d.TopConsoleUser0
from V_R_system a,v_GS_SYSTEM_CONSOLE_USAGE d,v_Add_Remove_Programs b,v_FullCollectionMembership c
where a.ResourceID=b.ResourceID and a.ResourceID=c.ResourceID and a.ResourceID=d.ResourceID and
(DisplayName0 = 'Adobe Creative Suite 5 Design Standard' or
DisplayName0 = 'Adobe Creative Suite 5 Design Premium' or
DisplayName0 = 'Adobe Creative Suite 5 Design Standard' or
DisplayName0 = 'Adobe Creative Suite 5 Master Collection' or
DisplayName0 = 'Adobe Creative Suite 5 Production Premium' or
DisplayName0 = 'Adobe Creative Suite 5.5 Design Premium' or
DisplayName0 = 'Adobe Creative Suite 5.5 Design Standard' or
DisplayName0 = 'Adobe Creative Suite 5.5 Master Collection' or
DisplayName0 = 'Adobe Design Standard CS5' or
DisplayName0 = 'Adobe After Effects CS5' or
DisplayName0 = 'Adobe Fireworks CS5' or
DisplayName0 = 'Adobe Flash Professional CS5' or
DisplayName0 = 'Adobe Illustrator CS5' or
DisplayName0 = 'Adobe Illustrator CS5.5' or
DisplayName0 = 'Adobe InDesign CS5' or
DisplayName0 = 'Adobe InDesign CS5.5' or
DisplayName0 = 'Adobe Photoshop CS5' or
DisplayName0 = 'Adobe Photoshop CS5.5' or
DisplayName0 = 'Adobe Illustrator CS5' or
DisplayName0 = 'Adobe Illustrator CS5.5' or
DisplayName0 = 'Adobe Premiere CS5' or
DisplayName0 = 'Adobe Premiere CS5.5' or
DisplayName0 = 'Adobe Dreamweaver CS5' or
DisplayName0 = 'Adobe Dreamweaver CS5.5' or
DisplayName0 = 'Adobe InDesign CS5' or
DisplayName0 = 'Adobe Design Standard CS5 64-bit'
)
group by a.name0, AD_Site_Name0, b.DisplayName0,b.Publisher0,b.Version0,d.TopConsoleUser0
order by AD_Site_Name0, b.DisplayName0
Answered 04/04/2012 by: mj@acp.com.au
Senior Yellow Belt

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