JJ's SQL Ethiques- Blogging SMS/SCCM

SMS Report to find machines with duplicate serial numbers

2011-06-08T05:45:00.000-07:00

SELECT
a.Name0,
a.UserName0,
d.sms_installed_sites0 as 'Site Code',
e.Lasthwscan as 'Last hardware Scan',
repeat.SerialNumber0
FROM
v_GS_COMPUTER_SYSTEM a,v_r_system c,v_RA_System_SMSInstalledSites d,v_GS_WORKSTATION_STATUS e,
v_GS_PC_BIOS b,
(SELECT b.SerialNumber0 FROM v_GS_PC_BIOS b GROUP BY
b.SerialNumber0 HAVING (Count(b.SerialNumber0)>1)) as
repeat
WHERE
b.ResourceID = a.ResourceID
and a.ResourceID=c.resourceid
and b.resourceid=d.resourceid
and b.resourceid=e.resourceid
and b.SerialNumber0 = repeat.SerialNumber0
and (c.client0=1 and c.obsolete0=0 and c.Active0=1)
order by
b.SerialNumber0,
a.Name0,
a.UserName0

Collection query to find all machines with 1 GB or more free space in C Drive

2011-04-01T00:18:00.001-07:00

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1 and SMS_G_System_LOGICAL_DISK.DriveType = 3 and SMS_G_System_LOGICAL_DISK.DeviceID = "C:" and SMS_G_System_LOGICAL_DISK.FreeSpace >= 1024

Collection query to find windows 2003 server with Service pack 1 version

2011-03-18T09:21:00.001-07:00

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Obsolete = 0 and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.Caption like "%windows%2003%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion like "Service Pack 1"

Collection query to find machines without MS Office versions

2011-02-17T07:19:00.000-08:00

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where Client = 1 and Obsolete = 0 and Active = 1 and OperatingSystemNameandVersion not like "%server%" and ResourceId not in (select RESOURCEID from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_SoftwareFile.FileName like "excel.exe" or SMS_G_System_SoftwareFile.FileName like "winword.exe"))

Description: There are different ways to reach out to this solution. The above method is by using software inventory and querying machines with 'excel.exe' or 'winword.exe'. If these exe's present on the machines, it is assumed that it have MS office installed. You can go for additional conditions in the filepath column and restrict it to find only in program files folder etc etc. But this should almost do your job. Once you write this query, then using SUBSELECT method you can filter the machines which don;t have this software.

Also you can make use of mso.dll and adding a filepath condition should do. Another method is by making hardware inventory data from your add or remove programs and querying the displayname. This i won't recommend because you have to put lot of displaynames (ms office 2003, ms office 2007, ms office XP proffessional, ms office enterprise% etc etc) to make sure you are getting accurate result. If you are sure about what are the exact displaynames then go for this. whichever method you choose, you need to make a subselect query to get it done.

Good Luck!

SMS Report/Query to find the current IP Address of client machines

2011-01-24T10:24:00.000-08:00

SELECT
[ComputerName],
[IP Address],
[Subnet Mask],
[Timestamp]
FROM
(
select
row_number() over (partition by cfg.IPAddress0 order by cfg.Timestamp desc) as rownum,
sys.netbios_name0 AS [ComputerName],
cfg.IPAddress0 AS [IP Address],
cfg.IPSubnet0 AS [Subnet Mask],
cfg.Timestamp AS [Timestamp]
FROM
dbo.v_R_System AS sys
INNER JOIN dbo.v_GS_Network_Adapter_Configur AS cfg
ON sys.resourceID = cfg.resourceID
AND cfg.IPEnabled0 = 1
WHERE
sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
AND sys.Obsolete0=0
AND cfg.IPaddress0 NOT LIKE '%,%'
AND cfg.IPAddress0 NOT LIKE '0.0%')as dt
where rownum=1
order by [Timestamp] desc

SMS Report to list all the machines that haven't reported its discovery/inventory since past 30 days

2011-01-21T07:02:00.001-08:00

select sys.Netbios_Name0,
sys.Client_Type0,
sys.Creation_date0 as 'Creation Date',
sys.Client_Version0,
sys.User_Domain0,
sys.User_Name0,
inst.sms_assigned_sites0 as 'sitecode',
disc.AgentTime as 'Last Discovery date',
hw.LastHWScan as 'Last HW Scan',
sw.LastScanDate as 'Last SW Scan'
from v_R_System sys
left join
(
select ResourceId, MAX(AgentTime) as AgentTime
from v_AgentDiscoveries
group by ResourceId
) as disc on disc.ResourceId = sys.ResourceID
left join v_GS_WORKSTATION_STATUS hw on hw.ResourceID = sys.ResourceID
left join v_GS_LastSoftwareScan sw on sw.ResourceID = sys.ResourceID
left join v_RA_System_SMSAssignedSites inst on inst.resourceid=sys.resourceid
where
disc.AgentTime < getdate()-30
or hw.LastHWScan < getdate()-30
or sw.LastScanDate < getdate()-30
and (sys.Client0 = 1 and sys.Obsolete0=0)
and sys.Name0 in (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) < 2)
order by disc.AgentTime asc

Collection Query based off specific model and bios version

2011-01-13T08:40:00.000-08:00

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model like "OptiPlex 380" and SMS_G_System_PC_BIOS.SMBIOSBIOSVersion < "A04" and SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0

Description: This query lists all the OptiPlex 380 machine models which is having a bios version less than A04.

Query to find Number of days count of all client machines since Last Bootuptime

2011-01-13T06:55:00.000-08:00

select a.Netbios_name0, e.Caption0 as OS, e.CSDversion0 as 'SP level',a.resourceid,a.Creation_date0,a.client0, a.ad_site_name0,a.obsolete0,
b.sms_installed_sites0 as 'sitecode',c.Lasthwscan,d.Agenttime as 'Last heartbeat discovery ran',e.LastBootUpTime0,DATEDIFF(dd, LastBootUpTime0, GETDATE())AS 'Number of Days since last bootup'
from v_r_system a
left outer join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
Left outer join v_AgentDiscoveries d on d.resourceid=a.resourceid
left outer join v_gs_operating_system e on e.resourceid=a.resourceid
where d.AgentName='Heartbeat Discovery' and (a.client0=1 and a.Obsolete0=0 and a.Active0=1)
and a.Name0 IN (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) < 2)
order by e.LastBootUpTime0 desc

Query to find Bootuptime of all SMS client machines for a specific time frame

2011-01-11T07:15:00.001-08:00

select a.Netbios_name0, e.Caption0 as OS, e.CSDversion0 as 'SP level',a.resourceid,a.Creation_date0,a.client0, a.ad_site_name0,a.obsolete0,
b.sms_installed_sites0 as 'sitecode',e.LastBootUpTime0,c.Lasthwscan,d.Agenttime as 'Last heartbeat discovery ran'
from v_r_system a
left outer join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
Left outer join v_AgentDiscoveries d on d.resourceid=a.resourceid
left outer join v_gs_operating_system e on e.resourceid=a.resourceid
where d.AgentName='Heartbeat Discovery' and d.Agenttime >=getdate()-30

SMS Report for the packages advertised but not yet updated the DP

2010-12-29T03:30:00.000-08:00

SELECT a.AdvertisementID,a.AdvertisementName,a.PresentTime as 'Advert Start Date',b.Name as 'Package Name',b.PackageID,count(b.PackageID) as 'Count of pending DP'
FROM v_Advertisement a
INNER JOIN v_Package b ON a.PackageID = b.PackageID
INNER JOIN v_DistributionPoint c ON b.PackageID = c.PackageID
INNER JOIN v_PackageStatusDistPointsSumm d ON c.PackageID = d.PackageID
WHERE d.state=1
GROUP BY a.AdvertisementID,a.AdvertisementName,a.PresentTime,b.PackageID,b.Name

Description: This report gives the count of each distribution points in which a specific advertised package is not updated.

Query to find non sms clients with recent AD discovery

2010-12-15T06:33:00.000-08:00

SELECT
[Name],
[Resourceid],
[Obsolete],
[Client],
[LastSWscan],
[LastHWscan],
[Last discovery run]
FROM
(SELECT
row_number() over (partition by a.Name0 order by e.AgentTime desc) as rownum,
a.Name0 as [Name],
a.resourceid as [Resourceid],
a.Obsolete0 as [Obsolete],
a.Client0 as [Client],
b.LastScandate AS [LastSWscan],
c.LastHWscan as [LastHWscan],
e.AgentTime as [Last discovery run]
FROM v_r_system AS a
LEFT OUTER JOIN v_GS_LastSoftwareScan AS b
ON a.resourceid = b.resourceid
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS c
ON a.resourceid = c.resourceid
LEFT OUTER JOIN v_AgentDiscoveries e
on e.resourceid=a.resourceid
WHERE a.Name0 IN (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) < 2) AND (c.LastHWscan is NULL and b.LastScandate is NULL and a.Client0 is NULL) AND e.AgentTime > getdate()-10
AND e.AgentName='SMS_AD_SYSTEM_DISCOVERY_AGENT')as dt
where rownum=1

Description: This query will get you all the resources which are having a recent Active directory system discovery date but do not have SMS client agents installed.

SQL Query to find the users logged on to SMS console recently

2010-12-09T07:10:00.000-08:00

select stat.MachineName as 'Accessing the console from',stat.Sitecode,att1.AttributeValue as 'UserName',att1.AttributeTime as 'Loggedin Time'
FROM vStatusMessages stat
left join v_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID
WHERE
stat.MessageType = 768
and stat.Sitecode like '%'
and stat.Time <= getdate()
and stat.MessageID=30102
order by stat.Time desc

Description: This query gives you the list of users who have created a session with SMS console recently.

SMS Report to find last logon user details of computers

2010-11-29T07:44:00.000-08:00

Select distinct
v_R_System.Netbios_Name0 AS "Computer Name",
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS "Console User",
v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS "Console Logons",
v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS "Total Minutes on Console",
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS "Last Console Use"
from v_R_System INNER JOIN
(SELECT
ResourceID,
SystemConsoleUser0,
NumberOfConsoleLogons0,
TotalUserConsoleMinutes0,
LastConsoleUse0,
ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Point
FROM
v_GS_SYSTEM_CONSOLE_USER) v_GS_SYSTEM_CONSOLE_USER
ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
AND v_GS_SYSTEM_CONSOLE_USER.Point = 1
Order by v_R_System.Netbios_Name0

Description: This will get you the top console user info who logged in to the machines most recently.

Another way to find this is, If you have hardware inventory configured to run on a shorter period, something like it is configured to run daily, get the last logon user info from v_GS_COMPUTER_SYSTEM (This view gets updated with user info every time hardware inventory runs).
If you have heartbeat discovery running daily, get the last logon user info from v_r_system (This view gets updated with user info every time hearbeat discovery runs).

SMS report to track SMS site systems with low disk space

2010-11-17T04:26:00.000-08:00

select SUBSTRING(Sitesystem, CHARINDEX('\\', Sitesystem) + 2, CHARINDEX('"]', Sitesystem) - CHARINDEX('\\',
Sitesystem) - 3 ) AS siteSystem,
SiteObject,Sitecode,Role,BytesTotal/1024 as [Total disk space(MB)],BytesFree/1024 as [Free Space(MB)],
PercentFree as 'PercentFreespace',Downsince
from v_SiteSystemSummarizer where Role not like 'SMS SQL Server' and Percentfree <10

WQL query to create a collection with all machines that do not have IE8 installed

2010-11-16T03:16:00.001-08:00

SMS Report to list all machines that do not have IE8 installed

2010-11-16T03:11:00.000-08:00

select distinct(c.FileDescription +' '+left(c.fileversion,3)) as 'Internet Explorer Version',c.Fileversion as 'IE version number',
a.Name0 as 'Host Name',a.User_name0 as 'User Name',b.Caption0 + ' ' + b.CSDVersion0 as 'Operating System Version'
from v_r_system a inner join v_gs_Operating_system b on a.resourceid=b.resourceid
inner join v_GS_SoftwareFile c on a.resourceid=c.resourceid
where c.FileName = 'iexplore.exe' AND c.FilePath like '%prog%internet%' and c.Fileversion not like '8.%' and (a.client0=1 and a.Obsolete0=0 and a.Active0=1)
Group By c.FileDescription +' '+left(c.fileversion,3),c.Fileversion,a.Name0,a.User_name0,b.Caption0 + ' ' + b.CSDVersion0
ORDER BY c.FileDescription +' '+left(c.fileversion,3)

WQL query to create a collection with all Desktops

2010-11-09T22:01:00.002-08:00

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "1", "2", "3", "4", "5", "6", "7", "15" ) and SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1

WQL query to create a collection with all laptops

2010-11-09T22:01:00.000-08:00

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "8", "10", "12", "14", "18", "21" ) and SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1

Query to get detailed summary of all the advertisments

2010-10-28T04:00:00.001-07:00

SELECT a.Netbios_name0 as 'Host Name',
site.sms_installed_sites0 as 'Sitecode',
a.Client0,
a.Obsolete0,
adv.AdvertisementName,
adv.AdvertisementID,
adv.Comment as 'Description',
pkg.Name AS 'Package Name',
adv.ProgramName,
col.Name as 'Collection Name',
adv.CollectionID,
advstate.LastAcceptanceMessageIDname,
advstate.LastAcceptanceStateName,
advstate.LastAcceptanceStatusTime,
advstate.LastStatusmessageIDName,
advstate.LaststateName,
advstate.LastStatusTime,
advstate.LastExecutionResult,
advstate.LastExecutionContext
FROM v_Advertisement adv
INNER JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
INNER JOIN V_COLLECTION col ON adv.CollectionID=col.CollectionID
INNER JOIN v_ClientAdvertisementStatus advstate on adv.AdvertisementID=advstate.AdvertisementID
INNER JOIN V_R_SYSTEM a ON a.Resourceid=advstate.resourceid
INNER JOIN v_GS_WORKSTATION_STATUS HW ON a.resourceid=hw.resourceid
INNER JOIN v_GS_LastSoftwareScan sw ON a.resourceid=sw.resourceid
LEFT OUTER JOIN v_RA_System_SMSInstalledSites site ON a.resourceid=site.resourceid
WHERE adv.AdvertisementID LIKE '%'
order by adv.AdvertisementName

SQL Query to find duplicates from siamese twins machines!!

2010-10-25T09:45:00.000-07:00

SELECT
[Name0],
[resourceid],
[Obsolete],
[Client],
[LastSWscan],
[LastHWscan]
FROM
(
select
row_number() over (partition by a.Name0 order by c.Lasthwscan desc) as rownum,
a.Name0 as [Name0],
a.resourceid as [resourceid],
a.Obsolete0 as [Obsolete],
a.Client0 as [Client],
b.Lastscandate as [LastSWscan],
c.Lasthwscan as [Lasthwscan]

from v_r_system AS a
LEFT OUTER JOIN v_GS_LastSoftwareScan AS b
ON a.resourceid = b.resourceid
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS c
ON a.resourceid = c.resourceid
WHERE a.Name0 IN (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) > 1)
AND (c.LastHWscan is NOT NULL and b.LastScandate is NOT NULL and a.Client0=1 and a.Obsolete0=0 and a.Active0=1)) as dt
where rownum<>1
order by [Lasthwscan] desc

Description: Obsolete machines are common in all the SMS\SCCM environments. This can be easily tracked and maintained by checking the Obsolete flag from the v_r_system view. But what if you have more than one entry with the same hostname which is active non Obsolete and client=1 and with recent hardware\software and discovery information's?...It is sometimes very tough to differentiate the real one from the duplicate. The above query is written to overcome this issue. The query lists all those boxes which have an older hardware scan time compare to it's twin machine.(and 99% of times, this gives you the correct value).

To double check, take random number of hostnames from the above query result, and provide in the where condition of below query...

select a.Netbios_name0, a.resourceid,a.Creation_date0,a.client0, a.ad_site_name0,a.Creation_date0,a.obsolete0,
b.sms_installed_sites0 as 'sitecode',c.Lasthwscan,d.Agenttime
from v_r_system a
left outer join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
Left outer join v_AgentDiscoveries d on d.resourceid=a.resourceid
where a.Netbios_name0 like 'machinenamehere' and d.AgentName='Heartbeat Discovery'

Query to find Installed Application list from all machines

2010-10-21T03:51:00.000-07:00

select distinct (a.Netbios_name0), a.Creation_date0,a.User_name0,a.ad_site_name0,b.sms_installed_sites0 as 'sitecode',
os.Caption0 + ' '+ os.CSDversion0 as 'Operating System',addr.DisplayName0,addr.Version0,addr.Publisher0,addr.InstallDate0
from v_r_system a
left outer join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
left outer join v_GS_Operating_system OS on os.resourceid=a.resourceid
inner join v_GS_ADD_REMOVE_PROGRAMS addr on addr.resourceid=a.resourceid
where addr.DisplayName0 not like '%kb%'
and (a.Client0=1 and a.Obsolete0=0 and a.Active0=1)
order by a.Netbios_name0,addr.Publisher0

WQL query to find machines with IE version 7

2010-10-14T07:04:00.000-07:00

select distinct SMS_G_System_SoftwareFile.FileDescription, SMS_G_System_SoftwareFile.FileVersion,SMS_G_System_SYSTEM.Name,
SMS_G_System_OPERATING_SYSTEM.Caption from SMS_R_System inner join SMS_G_System_SoftwareFile on
SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on
SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId WHERE
SMS_G_System_SoftwareFile.FileName = "iexplore.exe" AND SMS_G_System_SoftwareFile.FilePath like "%prog%internet%"
and SMS_G_System_SoftwareFile.Fileversion like "7.%"

Query to find all machines with Internet Explorer 7 version

2010-10-14T03:22:00.000-07:00

select distinct(c.FileDescription +' '+left(c.fileversion,3)) as 'Internet Explorer Version',c.Fileversion as 'IE version number',
a.Name0 as 'Host Name',a.User_name0 as 'User Name',b.Caption0 + ' ' + b.CSDVersion0 as 'Operating System Version'
from v_r_system a inner join v_gs_Operating_system b on a.resourceid=b.resourceid
inner join v_GS_SoftwareFile c on a.resourceid=c.resourceid
where c.FileName = 'iexplore.exe' AND c.FilePath like '%prog%internet%' and c.Fileversion like '7.%' and (a.client0=1 and a.Obsolete0=0 and a.Active0=1)
Group By c.FileDescription +' '+left(c.fileversion,3),c.Fileversion,a.Name0,a.User_name0,b.Caption0 + ' ' + b.CSDVersion0
ORDER BY c.FileDescription +' '+left(c.fileversion,3)

Query to find the workstations thrown access denied against a client installation attempt

2010-10-08T06:18:00.000-07:00

select InsStrValue, [time], [Site Code], [Site Server], Win32Error
from
(
select
row_number() over (partition by ins.InsStrValue order by stat.[time] desc) as rownum,
ins.InsStrValue,
stat.[time],
stat.Sitecode as [Site Code],
stat.MachineName as [Site Server],
stat.Win32Error
from v_StatusMessage as stat
left join v_StatMsgInsStrings as ins
on stat.RecordID = ins.RecordID

where stat.Component = 'SMS_CLIENT_CONFIG_MANAGER'
and stat.Win32Error = '5'
) as dt
where rownum=1
order by [time] desc

Description: This query will list you all the machines which have thrown an access denied error against client installation attempts etc.. This query will work only if you have SQL 2005 or above as your SMS DB.

Query to list machines with their MS Office versions

2010-10-04T11:24:00.001-07:00

select distinct (a.Netbios_name0), a.Creation_date0,a.User_name0,a.client0, a.ad_site_name0,a.obsolete0,b.sms_installed_sites0 as 'sitecode',
os.Caption0 + ' '+ os.CSDversion0 as 'Operating System',addr.DisplayName0,addr.Version0,addr.Publisher0,addr.InstallDate0
from v_r_system a
left outer join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
left outer join v_GS_Operating_system OS on os.resourceid=a.resourceid
inner join v_GS_ADD_REMOVE_PROGRAMS addr on addr.resourceid=a.resourceid
where addr.DisplayName0 like 'Microsoft Office 2%'