Skip to main content

Office SQL

By
-- Counts Office version
select 

count(Total.Name0) as Total_Systems,COUNT(office2003.Name0) as MSoffice2003,COUNT(Office2007.Name0) as MSOffice2007,COUNT(Office2010.Name0) as MsOffice2010, COUNT(Office2013.Name0) as MsOffice2013

From 

(select vr.Name0 from v_R_System vr) 

as Total 

left join 

(select distinct sys.Name0, 

      displayname00 as OfficeEdition, 

      version00 as OfficeVersion  from system_disc sys 

left join 

add_remove_programs_data arp on sys.itemkey = arp.machineid where 

(displayname00 = 'Microsoft Office Basic Edition 2003' 

OR displayname00 = 'Microsoft Office Excel 2003' 

OR displayname00 = 'Microsoft Office Outlook 2003' 

OR displayname00 = 'Microsoft Office Personal Edition 2003' 

OR displayname00 = 'Microsoft Office Professional Edition 2003' 

OR displayname00 = 'Microsoft Office Small Business Edition 2003' 

OR displayname00 = 'Microsoft Office Standard Edition 2003') 

and client0 = 1)as office2003 

on Total.Name0=office2003.Name0 

left join 

(Select distinct sys.Name0, 

      displayname00 as OfficeEdition, 

      version00 as OfficeVersion  from system_disc sys 

left join 

add_remove_programs_data arp on sys.itemkey = arp.machineid where 

(displayname00 like 'Microsoft Office Personal 2007' 

or displayname00 like 'Microsoft Office Professional 2007' 

or displayname00 like 'Microsoft Office Professional 2007 Trial' 

or displayname00 like 'Microsoft Office Professional Hybrid 2007' 

or displayname00 like 'Microsoft Office Professional Plus 2007' 

or displayname00 like 'Microsoft Office Professional Plus 2007 (Beta)' 

or displayname00 like 'Microsoft Office Standard 2007' 

or displayname00 like 'Microsoft Office Standard 2007 Trial' 

or displayname00 like 'Microsoft Office Ultimate 2007') 

and sys.client0 = 1)as Office2007 

on Total.Name0=office2007.Name0 

left join 

(select distinct sys.Name0, 

      displayname00 as OfficeEdition, 

      version00 as OfficeVersion  from system_disc sys 

left join 

add_remove_programs_data arp on sys.itemkey = arp.machineid where 

(displayname00 like 'Microsoft Office 2010' 

or displayname00 like 'Microsoft Office Professional Plus 2010' 

or displayname00 like 'Microsoft Office Standard 2010' 

or displayname00 like 'Microsoft Office Professional 2010' 

or displayname00 like 'Microsoft Office Home and Student 2010' 

or displayname00 like 'Microsoft Office Home and Business 2010' 

or displayname00 like 'Microsoft Office Professional Plus 2010 (Beta)' 

or displayname00 like 'Microsoft Office Starter 2010 – English') 

and sys.client0 = 1) as office2010 

on Total.Name0=office2010.Name0

left join

(select distinct sys.Name0, 

      displayname00 as OfficeEdition, 

      version00 as OfficeVersion  from system_disc sys 

left join 

add_remove_programs_data arp on sys.itemkey = arp.machineid where 

(displayname00 like 'Microsoft Office Professional Plus 2013'  )

--or displayname00 like 'Microsoft Office Professional Plus 2013' 

and sys.client0 = 1) as office2013 

on Total.Name0=office2013.Name0

Comments

Post a Comment

Popular posts from this blog

Windows 7 Offline files will not go Online when connected to network

By
Issue Several laptop users move between networks, domain, home, etc and when they attempt to access DFS shares explorer status is working offline.  The issue only resolves it self after a reboot. Connecting directly to the share works and i am able to ping network resources.  This behavior occurs for VPN users as well. Possible Causes "slow-link mode". In win7 (with default settings) a client will enter slow-link mode if the latency to the server is above 80ms. In slow-link mode all writes are made to the local cache and a background sync only happens every 6 hours.  Depending on your connection the default slow link detection speed is 64,000 bps On client computers running Windows 7 or Windows Server 2008 R2, a shared folder automatically transitions to the slow-link mode if the round-trip latency of the network is greater than 80 milliseconds, or as configured by the "Configure slow-link mode" policy. After transitioning a folder to the slow-link mode, Offline Fil...
152 comments
Read more

SCCM Unknown computer not able to see Task Sequences after installing Current Branch 1702

By
Soon after installing SCCM CB 1702 we were unable to see Task Sequences deployed to the unknown collection. This issue was identified as a random system taking the GUID of the 'x64 Unknown Computer (x64 Unknown Computer)' record. As a result it was now a known GUID; as we were only deploying Task Sequences to the Unknown collection none were made available. 'x64 Unknown Computer (x64 Unknown Computer)' record 'x86 Unknown Computer (x86 Unknown Computer)' record To get the GUID of your unknown systems open SQL management studio and run the following command: --Sql Command to list the name and GUID for UnknownSystems record data select ItemKey, Name0,SMS_Unique_Identifier0 from UnknownSystem_DISC Using the returned GUID (SMS_Unique_Identifier0) we can find the hostname that has been assigned the 'x64 Unknown Computer (x64 Unknown Computer)' GUID by running the query below. --x64 Unknown Computers select Name0,SMS_Unique_Identifier0,Decommissioned0 from Sys...
171 comments
Read more

SCCM - Change the drive used for offline servicing of OSD images

By
Change the drive used for offline servicing of OSD images Offline Servicing OSD images wihin SCCM is a useful feature.  I have heard SCCM Professional say it is good and bad but this post is is specifically around changing the default location used to stage the Windows image. By default SCCM will use the drive in which the Configuration Manager is installed (i.e. Drive E) even if it is running low on diskspace.  But assuming you have a new drive F: with plenty of space the procedure below will details how to change this. Assuming your site code is P01 Launch wbemtest.exe and connect to:  root\sms\site_P01 Click Query and enter: SELECT * FROM SMS_SCI_Component WHERE SiteCode=’P01’ AND ItemName LIKE ‘SMS_OFFLINE_SERVICING_MANAGER%’ Double click the  query result Double click Props (Scroll down second from bottom) Click View Embedded There will be four entries listed. Through a process of trial and error double click each one looking for PropertyName...
1 comment
Read more