/build/static/layout/Breadcrumb_cap_w.png

SCCM 2007 Report Query

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

Answers (2)

Posted by: dchristian 12 years ago
Red Belt
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.
Posted by: mj@acp.com.au 12 years ago
Senior Yellow Belt
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
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ