SCCM Query to find how many sites have PXE service point enabled?

This report will help to identify all the PXE service point enabled within any SCCM environment.

Select vs.sitecode,vs.sitename,vs.servername,vss.role from v_Site vs, v_SiteSystemSummarizer vss
where vs.SiteCode=vss.SiteCode and
vss.Role like 'SMS PXE Service Point'

Status message query to find out all sccm servers with specific component status

select stat.*, ins.*, att1.*, stat.Time from  SMS_StatusMessage as stat left join SMS_StatMsgInsStrings as ins on ins.RecordID = stat.RecordID left join SMS_StatMsgAttributes as att1 on att1.RecordID = stat.RecordID where stat.Component = "SMS_EXECUTIVE" and stat.Time >= ##PRM:SMS_StatusMessage.Time## order by stat.Time DESC

Cheers!

SQL query to get packages which are advertised without updating DPs

SELECT dbo.v_Advertisement.AdvertisementID, dbo.v_Advertisement.AdvertisementName, dbo.v_Advertisement.CollectionID,

dbo.v_Advertisement.PresentTime, dbo.v_Advertisement.ExpirationTime, dbo.v_Advertisement.SourceSite, dbo.v_Package.PackageID,
dbo.v_Package.Name, dbo.v_Package.Version, dbo.v_Package.SourceVersion, dbo.v_Package.SourceDate, dbo.v_Package.LastRefreshTime,
dbo.v_DistributionPoint.ServerNALPath, dbo.v_DistributionPoint.SiteCode, dbo.v_DistributionPoint.SiteName,
dbo.v_DistributionPoint.LastRefreshTime AS Expr1, dbo.v_DistributionPoint.Status, dbo.v_PackageStatusDistPointsSumm.ServerNALPath AS Expr2,
dbo.v_PackageStatusDistPointsSumm.SourceVersion AS Expr3, dbo.v_PackageStatusDistPointsSumm.SiteCode AS Expr4,
dbo.v_PackageStatusDistPointsSumm.State, dbo.v_PackageStatusDistPointsSumm.LastCopied, dbo.v_PackageStatusDistPointsSumm.SummaryDate,
dbo.v_PackageStatusDistPointsSumm.InstallStatus
FROM dbo.v_Advertisement INNER JOIN
dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
dbo.v_DistributionPoint ON dbo.v_Package.PackageID = dbo.v_DistributionPoint.PackageID INNER JOIN
dbo.v_PackageStatusDistPointsSumm ON dbo.v_DistributionPoint.PackageID = dbo.v_PackageStatusDistPointsSumm.PackageID
WHERE dbo.v_PackageStatusDistPointsSumm.state=1

Monitor your packagers ;)

SQL query to get computer names which do NOT have specific file installed

-- It returns all computer names which do NOT have specific file installed:
SELECT DISTINCT Netbios_Name0
FROM v_R_System
WHERE Netbios_Name0 NOT IN
(SELECT DISTINCT v_R_System.Netbios_Name0
FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
WHERE v_GS_SoftwareFile.FileName = 'filename.exe')
ORDER by Netbios_Name0

Query to get machines with specific exe

SELECT DISTINCT v_R_System.Netbios_Name0
FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
WHERE v_GS_SoftwareFile.FileName = 'Notepad.exe'

-- it returns machines with specific file name. You can change file name as per your  requirements.

Query to get SQL Server version and edition

Pls use the below query to get the SQL server version and edition-

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SMS Query to get Server Inventory Report

For the server inventory, pls run the below query on SQL server management studio.

select distinct a.name0,a.user_name0, a.operating_system_name_and0, a.ad_site_name0,
b.manufacturer0,b.model0,c.releasedate0,c.serialnumber0,e.size0,
f.csdversion0,g.currentclockspeed0,h.totalphysicalmemory0
from v_r_system a,v_GS_COMPUTER_SYSTEM b,v_GS_PC_BIOS c,
v_GS_ADD_REMOVE_PROGRAMS d,v_GS_PARTITION e,v_GS_OPERATING_SYSTEM f,
v_GS_PROCESSOR g,v_GS_X86_PC_MEMORY h
where (a.resourceid=b.resourceid and b.resourceid=c.resourceid
and c.resourceid=d.resourceid and d.resourceid=e.resourceid
and e.resourceid=f.resourceid and f.resourceid=g.resourceid
and g.resourceid=h.resourceid)and a.operating_system_name_and0 like '%server%'

For any further help, pls leave a comment.

ConfigMgr/SMS Query and Report for Spyware

SELECT DISTINCT
RSYS.Name0 AS 'Computer',
RSYS.User_Name0 As 'Last User ID',
SF.FileName As 'File Name',
SF.FileDescription As 'File Description',
SF.FilePath As 'File Path',
SF.FileSize As 'File Size',
SF.FileVersion As 'File Version'
FROM
V_R_SYSTEM RSYS
INNER JOIN V_GS_SoftwareFile SF
ON RSYS.ResourceID = SF.ResourceID
AND ( SF.FileDescription like '%doom%' OR /* DOOM Game */
SF.FileDescription like '%GNUTE%' OR /* MP3 Resources */
SF.FileDescription like '%l0pht%'OR /* Password cracker */
SF.FileDescription like 'Lime%' OR /* Peer-to-Peer file sharing */
SF.FileDescription like '%nuke%' OR /* DOOM Game */
SF.FileDescription like '%orafice%' OR /* Keystroke mapper */
SF.FileDescription like '%sniff%' OR /* Network sniffer */
SF.FileDescription like '%unreal%' OR /* Games */
SF.FileName like '%as-101%' OR
SF.FileName like '%babylon%' OR
SF.FileName like '%bearshare%' OR
SF.FileName like '%bindery%' OR
/* SF.FileName like '%bindin%' OR */
SF.FileName like '%bo2k%' OR
SF.FileName like '%chknull%' OR
SF.FileName like '%Cracker%' OR /* Password cracker */
SF.FileName like '%Craserv%' OR
SF.FileName like '%doom%' OR /* DOOM game */
SF.FileName like '%EbatesMoeMoney%' OR /* Spyware */
SF.FileName like '%expolit%' OR
SF.FileName like 'gator%' OR /* Gator Spyware/Adware */
SF.FileName like '%getadmin%' OR
SF.FileName like '%gnucleus%' OR
SF.FileName like '%GNUTE%' OR /* MP3 Resources */
SF.FileName like '%GROK%' OR
SF.FileName like '%hack%' OR /* Password cracker */
SF.FileName like '%hotbar%' OR /* IE Toolbar - Spyware/Adware */
SF.FileName like '%kazaa%' OR /* Peer-to-Peer file sharing */
SF.FileName like 'keygen%'OR /* Password cracker */
SF.FileName like '%l0phtcrack%' OR /* Password cracker */
SF.FileName like '%lc252install%' OR /* Password cracker */
SF.FileName like '%LIME%' OR /* Peer-to-Peer file sharing */
SF.FileName like '%morpheus%' OR
SF.FileName like '%Napster%' OR /* Peer-to-Peer file sharing - MP3 Resources */
SF.FileName like '%nbsvr%' OR
SF.FileName like '%nbtscan%' OR
SF.FileName like '%ndssnoop%' OR
SF.FileName like '%netbusr%' OR
SF.FileName like '%nmapNT%' OR
SF.FileName like '%nuke%' OR /* DOOM Game */
SF.FileName like '%nwpcrack%' OR
SF.FileName like '%orafice%' OR /* Keaystroke mapper */
SF.FileName like '%otglove%' OR
SF.FileName like '%precisiontime%' OR
SF.FileName like '%pwdump%' OR /* Password cracker */
SF.FileName like '%quake%' OR /* DOOM game */
SF.FileName like '%Retina%' OR
SF.FileName like '%RFPoison%' OR
SF.FileName like '%smbdie%' OR
SF.FileName like '%smurf%' OR
SF.FileName like '%unreal%' OR
SF.FileName like '%XUPITER%' OR
SF.FileName like 'POPSRV%' OR
SF.FileName IN ('_DLL.exe', /* Troj_Bagle.AC Trojan */
'ARR.exe', /* Dial-up Hijacker - high cost toll number */
'asart.exe', /* ? */
'av.exe', /* W32.Alphx.Word.A Virus */
'BackWeb.exe', /* Spyware - BackWeb Technologies */
'Bargains.exe', /* BargainBuddy - Adware/Spyware */
'BELT.exe', /* Spyware - SearchV.com */
'Bling.exe', /* W32.SDBot-OH.Worm */
'BLSS.exe', /* Spyware - CBlaster Trojan */
'Bootconf.exe', /* Sypware - Homepage Hijacker */
'BonziBdy.exe', /* Spyware */
'botzor.exe', /* W32.ZOTOB.Worm */
'BPC.exe', /* Spyware - Grokster */
'Bundle.exe', /* Adware.SAHAgent */
'businessbg0002.exe', /* Spyware - ? */
'cmesys.exe', /* Adware.W32.Claria */
'crafty.exe', /* ? */
'CFD.exe', /* Spyware - Motive Cleint Foudation */
'csm.exe', /* W32.ZOTOB.B Worm */
'Datemanager.exe', /* Pop-Ups via Gator */
'DIVX.exe', /* MASTAK Virus or NALDEM Trojan */
'DPPS2.exe', /* Don't Panic! Pop-up blocker - Spyware */
'DSSagent.exe', /* Adware - Broderbund - Spyware? */
'eanthology.exe', /* eAcceleration Software Station - Spyware? */
'EditSRV.exe', /* Spyware - Email_Update.exe */
'email_Update.exe', /* StopSign Email Scanner - eAcceleration Software - Spyware? */
'EMSW.exe', /* Spyware - Alset Inc. */
'Gator.exe', /* Adware.W32.Claria */
'gmt.exe', /* Adware.W32.Claria */
'haha.exe', /* Myet Trojan */
'Hbinst.exe', /* Spyware - HotBar */
'HBSRV.exe', /* Spyware - HotBar */
'Hotbar.exe', /* Spyware - HotBar */
'HXDL.exe', /* HXDL Spyware - Gator */
'HXIUL.exe', /* Adware - HelpExpress - Alset Inc. */
'IDHost.exe', /* Topicks Spyware */
'IEDll.exe', /* Homepage Hijacker */
'IEDriver.exe', /* Peer-To-Peer File Sharing */
'INFUS.exe', /* Dial-up Hijacker - high cost toll number */
'InfWin.exe', /* MSView Parasite */
'INTDEL.exe', /* Adware - Pop-ups */
'ISTSVC.exe', /* Spyware - Integrated Search Technologies */
'KeenValue.exe', /* Spyware - Gator */
'loader.exe', /* Backdoor.Prorat Virus */
'lol.exe', /* W32.HLLW.Rackus Virus */
'Lspmonitor.exe', /* Spyware - StopSign */
'mapisvc32.exe', /* KX Virus */
'MD.exe', /* System MD Virus */
'MDie.exe', /* Backdoor.Win32.Rbot.Gen Virus */
'MemoryMeter.exe', /* Grokster Peer-To-Peer File Sharing Suite */
'MFIN32.exe', /* Adware - MyFreeInternet Update */
'MMod.exe', /* Adware.W32.EarnBundleWare */
'MOStat.exe', /* Spyware - Wurld Media */
'mousebm.exe', /* W32.ESBot Virus */
'mousemm.exe', /* W32.ESBot.A Virus */
'MSBB.exe', /* Adware.W32.BargainBuddy - 180Solutions */
'MSCache.exe', /* Spyware - Integrated Search Technologies */
'MSCMan.exe', /* Spyware - Odysseus Marketing */
'msdefr.exe', /* Spybot Worm */
'MSMACROPROTXZ.exe', /* Spybot Worm */
'MSMGT.exe', /* Spyware - Total Velocity */
'MSSVR.exe', /* Spyware - 2020DownLoader - 2020 Internet Search Toolbar */
'MSUpdater.exe', /* TrojanDownLoader.Win32.WinShow Trojan */
'MWSOEMON.exe', /* MyWebSearch Toolbar */
'mwsvm.exe', /* Adware - Adw.ScanPortAL.A */
'Nail.exe', /* Trojan.Win32.Stervis.B Trojan */
'nb32ext2.exe', /* MyDoom.BV worm */
'nbmanager.exe', /* Spyware - eAnthology */
'netbutler.exe', /* ? */
'onsrvr.exe', /* Spyware - OnWebMedia */
'PC32.exe', /* Mastak Virus */
'per.exe', /* Worm.ZOTOB.C Virus */
'PGMonitr.exe', /* Adware.W32.DelFin */
'PowerScan.exe', /* Adware.W32.PowerScan */
'PRMVR.exe', /* Spyware - Adtomi.com */
'pnpsrv.exe', /* W32.SDBOT.Worm Virus */
'Precisiontime.exe', /* Adware.W32.ClariaPrecision */
'PrizeSurfer.exe',/* Spyware - PrizeSurfer */
'Prmt.exe', /* Spyware - OpiStat */
'RAY.exe', /* Homepage Hijacker */
'RB32.exe', /* Adware.W32.RapicBlaster */
'RCSync.exe', /* Spyware - PrizeSurfer */
'Run32DLL.exe', /* Key Recorder - Screen Capture - PAL PC Spy */
'SAHAgent.exe', /* Adware.W32.CyDoor - CyDoor Desktop Media */
'savenow.exe', /* Coupons - WhenU.com */
'SBHC.exe', /* IE Plugin - GIGATech Software */
'ShowBehind.exe', /* Adware - MicroSmarts Enterprise */
'SLMSS.exe', /* Spyware - 2nd Thourgh by CPM Media */
'SRNG.exe', /* Spyware - Search Hijacker */
'STCLoader.exe', /* Spyware - 2nd Thourgh by CPM Media */
'SUSP.exe', /* Spyware - ABetterInternet */
'SVCINIT.exe', /* Backdoor.Sinit Trojan */
'svnlitup32.exe', /* Worm.RBOT.CBJ */
'syscpy.exe', /* Backdoor.Hogle Trojan */
'Systesm32.exe', /* Spyware - Bling.exe */
'thefourthcoming.exe', /* ? */
'Trickler.exe', /* Spyware - Gator GAIN (Gator Advertising and Info Network) */
'TSADBot.exe', /* Adware */
'TVMD.exe', /* Spyware */
'TVTMD.exe', /* Spyware */
'UCMWESKU.exe', /* ? */
'Updates32.exe', /* Spyware - Bling.exe */
'uptodate.exe', /* Adware - BrowserPal */
'veloz.exe', /* StopSign Email Scanner - eAcceleration Software */
'velozsys.exe', /* StopSign Email Scanner - eAcceleration Software */
'Weather.exe', /* Adware */
'webcel.exe', /* eAcceleration Software - Spyware - ? */
'WebDev.exe', /* ? */
'Win32US.exe', /* Dial-up Hijacker - high cost toll number */
'WinActive.exe', /* Homepage Hijacker */
'windrg32.exe', /* W32.ZOTOB.D Worm */
'WinMain.exe', /* Trojan.KonDeli */
'WinNet.exe', /* Adware/Spyware - CommonName I.E. Search */
'winpnp.exe', /* W32.SDBOT.Worm */
'WinServN.exe', /* Adware.W32.PurityScan - ClickSpring LLC */
'WinStart.exe', /* Homepage Hijacker - iGetNet */
'WinStart001.exe', /* Adware */
'wintbp.exe', /* W32.ZOTOB.E Worm */
'wintbpx.exe', /* W32.BOZORI.Worm.B */
'WNAD.exe', /* Spyware - TwistedHumor.com */
'wpa.exe', /* ESBOT Worm */
'ygpmrgsb.exe', /* ? */
'zeus.exe', /* Zeus:Master of Olympus game */
'zmanager.exe' /* Spyware - eAcceleration */
)
)
ORDER BY
RSYS.Name0