Query to get detailed summary of all the advertisments

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!!

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

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

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

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

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

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%'

Query to list all the machines with their physical memory capacity

select 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',mem.Capacity0 as 'Memory in MB',c.Lasthwscan
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
left outer Join v_GS_PHYSICAL_MEMORY mem on mem.resourceid=a.resourceid
Order by mem.Capacity0 desc

Computers reported Hardware Inventory within past 30 minutes

select a.Netbios_name0, a.Creation_date0,a.User_name0,a.client0, a.ad_site_name0,a.obsolete0,b.sms_installed_sites0 as 'sitecode',c.Lasthwscan
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
where c.Lasthwscan >=DATEADD(minute,-30,GETDATE())
and c.Lasthwscan <= GETDATE()
Order by c.Lasthwscan desc

Web Report - SMS Site server Component StateTracker

SELECT a.SiteCode,a.Status, CASE a.Status
when 0 then 'OK'
when 1 then 'Warning'
When 2 then 'Critical'
Else 'Unknown'
end as 'Component Health',
a.ComponentName, a.Errors,
a.Warnings,a.State, CASE a.State
when 1 then 'Started'
when 0 then 'Stopped'
Else 'UNKNOWN'
end as 'Component State',
a.LastStarted, a.LastContacted
FROM v_ComponentSummarizer a WHERE a.TallyInterval='0001128000080008'