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
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)
Please log in to answer
Posted by:
dchristian
12 years ago
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.
DisplayName0 like '%Adobe%'
It'll save you some typing.
Posted by:
mj@acp.com.au
12 years ago
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
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