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