Wednesday, September 29, 2010

Query to find renamed computers in the environment

SELECT a.Name0 as 'discovered Name', a.ResourceID,b.Name0 as 'Name from recent inventory',
b.TimeStamp FROM v_R_System as a
INNER JOIN v_GS_System as b on a.ResourceID = b.ResourceID
WHERE a.Name0 <> b.Name0

Description: This query compares the machine name which got discovered, with the machine name got updated through recent inventory.

Monday, September 27, 2010

Query to find Machine model & Manufacturer information of a laptop/desktop

select a.Name0,a.User_name0,a.resourceid,a.AD_Site_Name0,comp.Model0,comp.Manufacturer0,ch.Chassistypes0,
CASE ch.Chassistypes0
when '1' then 'DESKTOP'
when '2' then 'DESKTOP'
when '3' then 'DESKTOP'
when '4' then 'DESKTOP'
when '5' then 'DESKTOP'
when '6' then 'DESKTOP'
when '7' then 'DESKTOP'
when '15' then 'DESKTOP'
when '8' then 'LAPTOP'
when '10' then 'LAPTOP'
when '12' then 'LAPTOP'
when '14' then 'LAPTOP'
when '18' then 'LAPTOP'
when '21' then 'LAPTOP'
when '23' then 'SERVER'
when '' then 'UNKNOWN'
END as 'Category',
b.LastScandate as LastSWscan,c.LastHWscan
from v_r_system a left outer join v_GS_LastSoftwareScan b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
left outer join v_GS_COMPUTER_SYSTEM comp on a.resourceid=comp.resourceid
left outer join v_GS_SYSTEM_ENCLOSURE ch on ch.ResourceId = a.ResourceId
left outer join v_GS_Operating_SYSTEM os on a.resourceid=os.resourceid
where
a.Client0=1 and a.Obsolete0=0 AND a.Active0=1
Order by ch.chassistypes0

Thursday, September 23, 2010

Query to find all the machines with a specific software installed

select distinct v_R_System.Name0,v_R_System.ResourceID,v_R_System.ResourceType,
v_R_System.SMS_Unique_Identifier0,v_R_System.Resource_Domain_OR_Workgr0,
v_R_System.Client0,Add_Remove_Programs_DATA.DisplayName00,Add_Remove_Programs_DATA.Version00 from v_R_System inner join Add_Remove_Programs_DATA
on Add_Remove_Programs_DATA.MachineID = v_R_System.ResourceId
where Add_Remove_Programs_DATA.DisplayName00 like 'applicationnamehere'
and Add_Remove_Programs_DATA.Version00 like '%'
and v_r_system.Obsolete0 <> '1' and v_R_System.Client0=1

Query to find Detailed Advertisement status of a specific package

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,
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_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 pkg.PackageID = 'packageidhere' and adv.ProgramName='programnamehere'
order by advstate.LaststateName

Wednesday, September 22, 2010

Query to find machines with hardware inventory FUTURE scan Date

select a.Name0,b.LastHwscan from v_r_system a inner join v_GS_WORKSTATION_STATUS b on
a.resourceID=b.resourceid where b.LastHwscan >getdate()

Description: This query will help you to find machines which are sending back a future scan date to the SMS server. This normally happens when the clock on the workstation is changed by someone (may be accidentally), and then if it ran the scan, it would report the local scan date. To resolve this you may to manually initiate a delta hardware scan on the problematic machines.

Tuesday, September 21, 2010

Query to count total number of windows updates and total number of softwares installed on machines

select a1.n1,a1.OSVersion1,a1.totalnumberofwindowsupdatesinstalled,b1.totalnumberofsoftwaresintalled from
(select a.Netbios_Name0 as 'n1', b.caption0 as 'OSVersion1', count(c.resourceid)
as 'totalnumberofwindowsupdatesinstalled' from v_r_system a inner join
v_gs_operating_system b on a.resourceid=b.resourceid
inner join v_gs_add_remove_programs c on a.resourceid=c.resourceid
where (c.displayname0 is not null and c.prodid0 like 'KB%')
group by a.Netbios_Name0, b.caption0, c.resourceid)a1,
(select a.Netbios_Name0 as 'n2', b.caption0 as 'OS Version', count(c.resourceid)
as 'totalnumberofsoftwaresintalled' from v_r_system a inner join
v_gs_operating_system b on a.resourceid=b.resourceid
inner join v_gs_add_remove_programs c on a.resourceid=c.resourceid
where (c.displayname0 is not null and c.prodid0 not like 'KB%')
group by a.Netbios_Name0, b.caption0, c.resourceid)b1
where a1.n1=b1.n2
order by a1.n1

Description:

This query will give you the count of windows updates and count of all software's installed on each machines in your SMS database.

Monday, September 20, 2010

Query to find all unused collections without any advert targeted

select a.collectionID,a.Name,a.lastChangeTime, a.LastMemberChangetime,b.AdvertisementID
from v_collection as a left outer join v_advertisement as b
on a.CollectionID=b.collectionID
where a.CollectionID not in (select collectionID from v_FullCollectionMembership)
and a.CollectionID not like 'SMS%'
and b.AdvertisementID is null
order by name

Description:

This query will list all the unused collections without any advertisements targeted against it. This will help when you have lot of collections get created in your console
and you want to filter all those unused one to cleanup your console.

Friday, September 17, 2010

Query to list all the collections in which a machine is a member of

select a.netbios_name0,a.resourceid,a.Client0,a.Creation_date0,a.Obsolete0,a.Active0,b.CollectionID,c.Name,b.SiteCode
from v_r_system a
inner join v_FullCollectionMembership b on a.resourceid=b.resourceid
inner join v_collection c on b.Collectionid=c.Collectionid
where a.Netbios_Name0 like ''
Order by c.Name

Description:

This query lists all the collection Names in which a given machine is member of.

Thursday, September 16, 2010

Query to find all the newly installed SMS client machines since 30 days

select a.Netbios_Name0,a.resourceid,a.Client0,a.Active0,a.Obsolete0,a.Creation_date0,
c.sms_Assigned_sites0 as 'sitecode',d.Lasthwscan
from v_r_system a
left outer join v_RA_System_SMSAssignedSites c on a.resourceid=c.resourceid
left outer join v_GS_WORKSTATION_STATUS d on a.resourceid=d.resourceid
where DATEDIFF (dd,a.Creation_date0,GetDate())<30
order by a.Creation_date0 desc

Description:

This query lists all the newly installed SMS client machines for the last 30 days with their assigned site code and last hardware scan date.

Tuesday, September 14, 2010

SMS Query to find a specific local subnet in SMS Site Boundaries

select * from v_SiteBoundary_IPSubnet where IPSubnet like '172.31.46.0%'

Description:
The Above query can be used to perform a quick check on the local IP Subnets configured on your SMS Site boundaries of your SMS Server.

Monday, September 13, 2010

SMS Query to get Client OS Count

select OS.Operating_System_Name_and0,
CASE OS.Operating_System_Name_and0 when 'Microsoft Windows NT Workstation 5.0' THEN 'Microsoft Windows 2000 Professional'
when 'Microsoft Windows NT Workstation 5.1' THEN 'Microsoft Windows XP Professional'
WHEN 'Microsoft Windows NT Workstation 6.0' THEN 'Microsoft Windows Vista'
WHEN 'Microsoft Windows NT Workstation 6.1' THEN 'Microsoft Windows 7 Professional'
ELSE 'UNKNOWN OS'
END
'OS Version'
, COUNT(*) as count
from v_R_System os
inner join v_RA_System_SMSInstalledSites inst on os.resourceid=inst.resourceid
where (os.Client_Type0 = 1 and os.Decommissioned0 = 0) and (os.Obsolete0 = 0 and OS.Operating_System_Name_and0 like '%Workstation%')
GROUP BY Operating_System_Name_and0

SMS Query to count Client Operating Systems per SMS site

select OS.Operating_System_Name_and0, COUNT(*) as 'Client count', inst.SMS_Installed_Sites0
from v_R_System os
inner join v_RA_System_SMSInstalledSites inst on os.resourceid=inst.resourceid
where (os.Client_Type0 = 1 and os.Decommissioned0 = 0) and (os.Obsolete0 = 0 and OS.Operating_System_Name_and0 like '%Workstation%')
GROUP BY Operating_System_Name_and0, SMS_Installed_Sites0

Description:

The Above query gives you the count of different operating systems per each SMS Site.

Find Machines with NULL Software Inventory

SELECT DISTINCT
a.Name0,
a.resourceid,
a.Creation_date0,
a.Obsolete0,
a.Client0,
a.Active0,
b.LastScandate AS LastSWscan,
c.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(*) < 2)
AND (b.LastScandate is NULL and a.Client0 =1 and a.Obsolete0=0)
ORDER BY c.LastHWscan, b.LastScandate desc

Find SMS Client Machines with NULL hardware inventory

SELECT DISTINCT
a.Name0,
a.resourceid,
a.Creation_date0,
a.Obsolete0,
a.Client0,
a.Active0,
b.LastScandate AS LastSWscan,
c.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(*) < 2)
AND (c.LastHWscan is NULL and a.Client0 =1 and a.Obsolete0=0)
ORDER BY c.LastHWscan, b.LastScandate desc

Find the column name of the given SMS table or view

select column_name,table_name from information_schema.columns
where table_name like '%' and column_name like '%serialnumber%' order by column_name

Description:

Many times , your client might have requested you to generate some adhoc reports from SMS Server. Sometimes we may not have information about the tables\views that contains the requested data. For example, if you do not know where is the information stored about serial number of SMS client machines, you can have a quick check by running above query. It lists out all the tables\views which contains the specified column name and easily you can reach out what you need!

SMS Query to Find Remote tool initiated for a site in a specified time duration

SELECT distinct stat.RecordID, stat.MachineName,stat.ModuleName,stat.SiteCode,Stat.Component,stat.time FROM vStatusMessages stat
LEFT OUTER JOIN StatusMessageInsStrs ins ON stat.RecordID = ins.RecordID
left join v_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID
WHERE
stat.MessageType = 768 and
stat.MessageID = 30076 and
stat.SiteCode like '%' and
(stat.Time between '2010-08-01' and '2010-08-31') order by stat.Time desc

Description:

This query lists all the machines name from which a remote tool activity initiated through SMS Console. IN the date column replace the date range you want.

Wednesday, September 8, 2010

Query to Count SMS Client versions

SELECT SYS.Client_Version0, SYS.Client_Type0, count(*) as 'Count'
FROM v_R_System as SYS
WHERE SYS.Client0=1
group by SYS.Client_Version0, SYS.Client_Type0
order by SYS.Client_Version0, SYS.Client_Type0

Query to find all the laptops in an SMS environment

select v_r_system.Name0
from v_R_System
inner join v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceId = v_R_System.ResourceId
where v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('8','9','10','11','12','14','18','21')

Description:

The query lists laptops based on the chassis info from one of the SQL view in SMS DB.

SMS Query to list all the distribution points in an SMS Environment

select distinct(serverName) from v_SystemResourceList where RoleName='SMS Distribution Point'

Find the IP Address History of SMS client machines

SELECT
sys.netbios_name0 AS ComputerName,
cfg.IPAddress0 AS [IP Address],
cfg.IPSubnet0 AS [Subnet Mask],
cfg.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 cfg.IPaddress0 NOT LIKE '%,%'
AND cfg.IPAddress0 NOT LIKE '0.0%'
order by sys.netbios_name0, cfg.Timestamp

Description:

This query will list you the machine names with IP address assigned to each of them.

Query to find largest SMS Package in the console

SELECT dbo.SMSPackages.PkgID as PackageID, dbo.SMSPackages.Name as [Package Name], dbo.SMSPackages.Source as [Source Location],
dbo.SMSPackages.SourceSize/1024 as [Package Size in MB], dbo.SMSPackages.SourceDate,
dbo.v_Advertisement.AdvertisementID, dbo.v_Advertisement.AdvertisementName
FROM dbo.SMSPackages INNER JOIN
dbo.v_Advertisement ON dbo.SMSPackages.PkgID = dbo.v_Advertisement.PackageID
WHERE dbo.v_Advertisement.AssignedScheduleEnabled=16 ORDER BY dbo.SMSPackages.SourceSize DESC

Description:

This query gives you all the packages exist in the SMS console with their size in MB.

Find Machines coming under a Roaming boundary Subnet

select distinct a.Name0,b.IP_subnets0,c.SiteCode,d.LastHwscan, c.ServerName from v_r_system a inner join v_RA_System_IPSubnets b
on a.resourceid=b.resourceid
inner join v_GS_WORKSTATION_STATUS d on a.resourceID=d.resourceID
left outer join RoamingBoundaryIPSubnet c on b.IP_Subnets0=c.IPSubnet
where c.ServerName != ''
and a.Obsolete0=0
order by a.Name0


Description:

The Above query will give you the total strength of each roaming boundary subnet that a Server supports. You can categorize machines under each roaming boundary.

Find all the SMS Packages with disabled programs

SELECT
*
FROM
dbo.v_Program p
Where
(0x00001000 & p.ProgramFlags)/0x00001000 = 1

Description:

The Above simple query will get you the list of all packages which have a disabled program in SMS Admin Console.

Tuesday, September 7, 2010

Find all machines with less than 100MB free space in their C Drive

select distinct
SYS.Name0 as 'Machine Name',
SYS.Resource_Domain_OR_Workgr0 as 'Domain',
SYS.User_Domain0 + '\' + SYS.User_Name0 as 'Username',
ASSG.SMS_Installed_Sites0 as 'SMS Sitecode',
Site.PrimaryOwnerName0,
Build.Caption0 as 'OS',
Build.CSDVersion0 as 'SP Level',
ldisk.deviceid0 as 'Drive Letter',
ldisk.Size0 as 'Total Drive Size',
ldisk.freespace0 as 'Free space avaiable',
scan.LastHWscan as 'Last Hardware Scan',
DateDiff(Day,scan.LastHWscan,GetDate()) as 'No. of Days Since Last scan'
FROM
v_R_System as SYS inner join v_RA_System_SMSInstalledSites as ASSG on ASSG.ResourceID = SYS.ResourceID
inner join v_GS_OPERATING_SYSTEM as Build on SYS.ResourceID=Build.resourceid
Inner JOIN v_GS_LOGICAL_DISK as LDISK on SYS.ResourceID=LDISK.ResourceID
inner join v_GS_COMPUTER_SYSTEM as Site on SYS.resourceid=Site.resourceid
Left outer Join v_GS_WORKSTATION_STATUS as scan on sys.ResourceID=scan.ResourceID
WHERE
LDISK.DriveType0=3 AND
ldisk.deviceid0='C:' AND
ASSG.SMS_Installed_Sites0 like '%'
AND build.Name0 not like '%server%'
and SYS.Obsolete0<>1
and ldisk.freespace0<=100
ORDER BY SYS.Name0

Description:

The Above query finds all the SMS client machines(workstations) those who have 100MB or below free space on their C Drive.

Quick check on the status of SMS Client machine

select a.Netbios_name0, a.Creation_date0,a.client0, a.ad_site_name0,a.Creation_date0,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 a.Netbios_name0 like 'machinenamehere'

Description:

This basic query will help you to get you a quick status of an SMS client machine. The report will tell you the SMS client agent creation date, client exists or not, whether it is active or not, The AD site it belongs to,whether it is marked as obsolete or not, The installed SMS Site Code, and the Last hardware scan date.

SMS Query to find manually installed applications

SELECT dbo.v_GS_SYSTEM.Domain0, dbo.v_GS_SYSTEM.Name0, dbo.v_GS_SYSTEM.SystemType0, dbo.v_GS_SYSTEM.SystemRole0,
dbo.v_GS_INSTALLED_SOFTWARE.ResourceID, dbo.v_GS_INSTALLED_SOFTWARE.InstallDate0,
dbo.v_GS_INSTALLED_SOFTWARE.ARPDisplayName0, dbo.v_GS_INSTALLED_SOFTWARE.InstalledLocation0,
dbo.v_GS_INSTALLED_SOFTWARE.InstallSource0, dbo.v_GS_INSTALLED_SOFTWARE.ProductVersion0,
dbo.v_GS_INSTALLED_SOFTWARE.Publisher0, dbo.v_GS_INSTALLED_SOFTWARE.ProductName0
FROM dbo.v_GS_INSTALLED_SOFTWARE INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_GS_INSTALLED_SOFTWARE.ResourceID = dbo.v_GS_SYSTEM.ResourceID
Where dbo.v_GS_INSTALLED_SOFTWARE.InstallSource0 not like 'C:\programData\1E\SMSNomad\%'
order by dbo.v_GS_INSTALLED_SOFTWARE.InstallSource0 desc

Description:

The above query helps to find manually installed applications list from SMS database. You can give your SMS cache folder in the 'where' clause and put a not like to avoid all those applications got installed through SMS. So, the query result would contain all those applications got installed but are not through SMS Server. (But the core applications which have installed with the Image is an exception. You may need to filter the list a bit).