:::: MENU ::::
Browsing posts in: SCCM Reports ( SCCM SQL Queries)

SCCM Different types of Views

 

SCCM Different types of Views :—(In one line )

V_*                 — > By default view(EX:-V_add_remove)

V_R_  *          — > It will fill the values with Discovery data

V_RA_  *      — > It will, fill the values Once Discovered and Assigned

V_GS_*         —-> HINV/SINV/Software Granular data- Present/Current inv data

V_HS_*         —-> Historical data-After 90 days Task mainenance data HINV/SINV/Software Historical data

V_AI_*         —-> Asset Intelligence related

V_LU_*         —–> Part of AI(Localized Unit)

V_CI_*          ——> Configuration Item 5 types

V_CH_*         —–> Client Health validation related

V_CM_RES_COLL_<SMS00012>  ——> Member Class name / Machine details of that collection

 

Download Reference -SCCM 2007 SQL Views SCCM 2007 List of views from Technet

Download  ConfigMgr2012R2_SQLViews SCCM 2012 List of views from Technet

Download  Configmgr Tech Preview Default Reports   SCCM 2012 List of Reports Technet

Download  ConfigMgr 2012 R2 Status Messages  SCCM 2012 List of  Status Messages from Technet

 


Hardware/Software Scan with last boot up time on specific machines – SQL Query

SELECT

v_R_System.Name0AS[Server Name],

v_GS_OPERATING_SYSTEM.Caption0AS[Operating System],

v_GS_OPERATING_SYSTEM.LastBootUpTime0AS[Last Machine Boot Up Time],

v_GS_WORKSTATION_STATUS.LastHWScanAS[Last HW Scan Date],

V_GS_LASTSOFTWARESCAN.LASTSCANDATEAS[LAST SOFTWARE SCAN DATE]

FROMv_R_SystemINNERJOINv_GS_OPERATING_SYSTEM

ONv_GS_OPERATING_SYSTEM.ResourceID=v_R_System.ResourceIDINNERJOIN

v_GS_LastSoftwareScanONv_R_System.ResourceID=v_GS_LastSoftwareScan.ResourceIDINNERJOIN

v_GS_WORKSTATION_STATUSONv_R_System.ResourceID=v_GS_WORKSTATION_STATUS.ResourceID

ANDv_R_System.Name0IN(‘Machine01’,‘Machine02’)


SQL query for “X” package DP status

SQL  query  for “X” package DP status..By using Package Name

 

 

select

SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) – CHARINDEX(‘\\’, dp.ServerNALPath) – 3 ) AS [Code Server Name],

dp.SiteCode,stat.SourceVersion,pstat.UpdateTime,stat.InstallStatus,

dp.PackageID,V_package.Name AS [Package Name] from v_DistributionPoint dp

left join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath

and dp.PackageID=stat.PackageID left join v_PackageStatus pstat on dp.ServerNALPath=pstat.PkgServer

and dp.PackageID=pstat.PackageID INNER JOIN

V_package ON V_package.packageid=dp.PackageID

where V_package.name = ‘Package Name’


All SCCM Servers with Roles in a Site hierarchy – SQL Query

–Displays ServerType now with roles, not just saying remote.
SET NOCOUNT ON

select distinct sum.SiteCode,
SUBSTRING(SiteSystem, (CHARINDEX(‘\\’,sum.SiteSystem)+2), (CHARINDEX(‘\’,SiteSystem,(CHARINDEX(‘\\’,sum.SiteSystem)+2)) – (CHARINDEX(‘\\’,sum.SiteSystem)+2))) ‘Server’,
CASE Sum.Role
WHEN ‘AI Update Service Point’ THEN ‘X’ ELSE ‘ ‘
End ‘AI Point’,
CASE Sum.Role
WHEN ‘SMS Distribution Point’ THEN ‘X’ ELSE ‘ ‘
End ‘DP’,
CASE Sum.Role
WHEN ‘SMS Fallback Status Point’ THEN ‘X’ ELSE ‘ ‘
End ‘FSP’,
CASE Sum.Role
WHEN ‘SMS Management Point’ THEN ‘X’ ELSE ‘ ‘
End ‘MP’,
CASE Sum.Role
WHEN ‘SMS PXE Service Point’ THEN ‘X’ ELSE ‘ ‘
End ‘PXE’,
CASE Sum.Role
WHEN ‘SMS Server Locator Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SLP’,
CASE Sum.Role
WHEN ‘SMS Site Server’ THEN ‘X’ ELSE ‘ ‘
End ‘Site Server’,
CASE Sum.Role
WHEN ‘SMS Software Update Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SUP’,
CASE Sum.Role
WHEN ‘SMS SQL Server’ THEN ‘X’ ELSE ‘ ‘
End ‘SQL’,
CASE Sum.Role
WHEN ‘SMS SRS Reporting Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SSRS’,
CASE Sum.Role
WHEN ‘SMS Reporting Point’ THEN ‘X’ ELSE ‘ ‘
End ‘RP’,
Sum.Role
into ##temp
from v_SiteSystemSummarizer sum
where not sum.Role = ‘SMS Component Server’

SET NOCOUNT OFF

–select * from ##temp

select distinct t1.SiteCode,t1.[Server],
(select top 1 [AI Point] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [AI Point],
(select top 1 [DP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [DP],
(select top 1 [FSP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [FSP],
(select top 1 [MP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [MP],
(select top 1 [PXE] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [PXE],
(select top 1 [SLP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SLP],
(select top 1 [Site Server] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [Site Server],
(select top 1 [SUP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SUP],
(select top 1 [SQL] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SQL],
(select top 1 [SSRS] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SSRS],
(select top 1 [RP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [RP],
CASE Site1.Type
WHEN 1 THEN ‘Secondary’
WHEN 2 Then ‘Primary’
ELSE
case when (select top 1 [AI Point] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘AIPoint ‘ else ” end+
case when (select top 1 [DP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘DP ‘ else ” end+
case when (select top 1 [FSP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘FSP ‘ else ” end+
case when (select top 1 [MP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘MP ‘ else ” end+
case when (select top 1 [PXE] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘PXE ‘ else ” end+
case when (select top 1 [SLP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SLP ‘ else ” end+
case when (select top 1 [Site Server] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SiteServer ‘ else ” end+
case when (select top 1 [SUP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SUP ‘ else ” end+
case when (select top 1 [SQL] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SQL ‘ else ” end+
case when (select top 1 [SSRS] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SSRS ‘ else ” end+
case when (select top 1 [RP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘RP ‘ else ” end
End as [ServerType],
–site1.ReportingSiteCode
(select distinct site11.ReportingSiteCode from v_Site site11
where site11.SiteCode = t1.SiteCode and site11.ReportingSiteCode is not null) as [ReportingSiteCode],
–site1.Version
(select distinct site11.Version from v_Site site11
where site11.SiteCode = t1.SiteCode and site11.Version is not null) as [Version]
from ##temp t1
left join v_Site site1 on t1.SiteCode = site1.SiteCode and t1.Server = site1.ServerName

drop table ##temp

 


SCCM report for all expired adverts

SELECT v_Advertisement.AdvertisementID AS [Advertisement ID],
v_Advertisement.AdvertisementName AS [Advertisement Name],
v_Advertisement.PackageID AS [Package ID],
v_Package.Name AS [Package Name],
v_Advertisement.ProgramName AS [Program Name],
v_Collection.CollectionID AS [Collection ID],
v_Collection.Name AS [Collection Name],
v_Advertisement.PresentTime AS [Advertisement Creation Date],
v_Advertisement.ExpirationTime AS [Advertisement Expiration Date]
FROM v_Advertisement INNER JOIN
v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID INNER JOIN
v_Package ON v_Package.PackageID = v_Advertisement.PackageID
WHERE (v_Advertisement.ExpirationTimeEnabled = 2) AND (v_Advertisement.ExpirationTime < GETDATE()) AND (v_Advertisement.PresentTimeIsGMT = 0) OR (v_Advertisement.ExpirationTimeEnabled = 2) AND (v_Advertisement.ExpirationTime < GETUTCDATE()) AND (v_Advertisement.PresentTimeIsGMT = 1)


SCCM Clients status on List of Machines – SQL Query

SELECT Name0 AS [Machine Name], User_Name0 AS [User Name],
CASE Active0 WHEN ‘0’ THEN ‘InActive’ WHEN ‘1’ THEN ‘Active’ ELSE ‘Unknown’ END AS [Active Client],
CASE Client0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Client Status],
CASE obsolete0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Obsolete Client], DATEDIFF(D,V_GS_WORKSTATION_STATUS.LASTHWSCAN,GETDATE()) AS [HW SCAN DIFFERENCE]
FROM v_R_System LEFT JOIN V_GS_WORKSTATION_STATUS ON V_R_SYSTEM.RESOURCEID = V_GS_WORKSTATION_STATUS.RESOURCEID
WHERE (Name0 IN (‘Machine1’, ‘Machine2’))


To Find the Package or Task sequence Folder Hive -SQL Query

WITHfolderHierarchy(ContainerNodeID,Name,ObjectType,ParentContainerNodeID,[Path])

AS(SELECTContainerNodeID,Name,ObjectType,ParentContainerNodeID,CAST(‘/’+[Name]+‘/’ASVARCHAR(MAX))AS[Path]

FROMdbo.FoldersWHEREParentContainerNodeID= 0

UNIONALL

SELECTchild.ContainerNodeID,child.Name,child.ObjectType,child.ParentContainerNodeID,parent.[Path]+child.[Name]+‘/’AS[Path]

FROMdbo.FoldersASchild

INNERJOINfolderHierarchyASparent

ONparent.ContainerNodeID=child.ParentContainerNodeID)

SELECTdistinctfldr.[Path],mbr.InstanceKeyASPackageID,pkg.Name

FROMfolderHierarchyASfldr

INNERJOINdbo.FolderMembersASmbrONfldr.ContainerNodeID=mbr.ContainerNodeID

INNERJOINdbo.v_packageASpkgONpkg.PackageID=mbr.InstanceKey

WHEREpkg.Namein(‘Packagename’)


SCCM / SMS Client Status on List of Specific Machines:

SELECT     Name0 AS [Machine Name], User_Name0 AS [User Name],

CASE Active0 WHEN ‘0’ THEN ‘InActive’ WHEN ‘1’ THEN ‘Active’ ELSE ‘Unknown’ END AS [Active Client],

CASE Client0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Client Status],

CASE obsolete0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Obsolete Client], DATEDIFF(D,V_GS_WORKSTATION_STATUS.LASTHWSCAN,GETDATE()) AS [HW SCAN DIFFERENCE]

FROM         v_R_System  LEFT JOIN V_GS_WORKSTATION_STATUS ON V_R_SYSTEM.RESOURCEID = V_GS_WORKSTATION_STATUS.RESOURCEID

WHERE     (Name0 IN (‘Machine1’))


Failed Advert Machine list Based on Site Code Wise

SELECT DISTINCT

v_R_System.AD_Site_Name0 AS [AD Site Code], v_Advertisement.ProgramName,

v_ClientAdvertisementStatus.LastStatusMessageIDName AS [Last Status Message], v_ClientAdvertisementStatus.LastStateName AS [Last Status],

v_ClientAdvertisementStatus.LastExecutionResult, COUNT(v_ClientAdvertisementStatus.LastStatusMessageIDName) AS ‘Error  Count’

FROM         v_ClientAdvertisementStatus INNER JOIN

v_R_System ON v_ClientAdvertisementStatus.ResourceID = v_R_System.ResourceID INNER JOIN

v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID INNER JOIN

v_Package ON v_Package.PackageID = v_Advertisement.PackageID

WHERE     (v_ClientAdvertisementStatus.AdvertisementID IN (‘AdvertID1’,

‘AdvertID2’)) AND (v_ClientAdvertisementStatus.LastStateName = ‘Failed’)

GROUP BY v_ClientAdvertisementStatus.LastExecutionResult, v_Advertisement.ProgramName, v_ClientAdvertisementStatus.LastStatusMessageIDName,

v_ClientAdvertisementStatus.LastStateName, v_ClientAdvertisementStatus.LastExecutionResult, v_R_System.AD_Site_Name0

ORDER BY v_Advertisement.ProgramName


To find the AdvertID on which folder structure created in SCCM console

To find the AdvertID on which Folder structure created in SCCM console

 

SELECT     Folders.name AS [Advertisement Created Under(Folder Name)], Foldermembers.instancekey AS AdvertID,

V_advertisement.Advertisementname AS [Advertisement Name]

FROM         dbo.Folders INNER JOIN

dbo.FolderMembers ON Foldermembers.containernodeid = folders.containernodeid INNER JOIN

dbo.v_Advertisement ON V_advertisement.advertisementid = Foldermembers.instancekey

WHERE     (V_advertisement.advertisementid = ‘AdvertID’)


Pages:123