SMS Report to find machines with duplicate serial numbers

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

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

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

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

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

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

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

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

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

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.