SQL Queries

SQL Queries

Here’s a public documentation of some of the queries that have been helpful for me in the past. I often had the problem that I missed to save or document my queries and had to research and write them again the next time the same question came up.

Packages and Advertisements

Get Package and Advertisement information.

SELECT v_Package.PackageID, v_Package.Name [Package Name],v_package.Version,V_Package.Language,
COUNT(v_Program.PackageID) AS ProgramCount, v_Advertisement.AdvertisementName,v_Advertisement.AdvertisementID,
FROM v_Package
LEFT OUTER JOIN v_Program ON v_Package.PackageID = v_Program.PackageID
LEFT OUTER JOIN v_Advertisement ON v_Package.PackageID = v_Advertisement.PackageID
left join v_Collection on V_collection.CollectionID=v_Advertisement.collectionID
WHERE (v_Package.PackageID NOT IN
(SELECT ReferencePackageID
FROM v_TaskSequenceReferencesInfo)) OR
(v_Package.PackageID IN
FROM v_TaskSequenceReferencesInfo AS v_TaskSequenceReferencesInfo_1))
GROUP BY v_Package.PackageID, v_Package.Name, v_Package.PackageType,v_package.version,
HAVING (v_Package.PackageType = '0')

List of required updates

select vrs.Name0, CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
ui.bulletinid [BulletinID],ui.articleid [ArticleID], ui.Title,
   Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
   IsRequired=(case when css.Status=2 then '*' else '' end),
   ui.InfoURL as InformationURL,
   ui.dateposted [Date Posted] ,
   from v_UpdateComplianceStatus  css
   join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
   left join v_CITargetedMachines  ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
   INNER join v_CICategories_All catall2 on catall2.CI_ID=css.CI_ID
   INNER  join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID
   and catinfo2.CategoryTypeName='UpdateClassification'
   JOIN dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID
      outer apply (
      select Deadline=min(a.EnforcementDeadline)
      from v_CIAssignment  a
      join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
      ) cdl
      LEFT join v_GS_WORKSTATION_STATUS as wss on vrs.ResourceID =  wss.ResourceID
  WHERE vrs.Name0 like '%'  --unlimited selection
  ui.Severity IN (8, 10) --severity: 0 = none, 6 =  moderate, 8 = important, 10 = critical
AND css.Status=2  --for required
ORDER BY vrs.Name0

The basis was provided by Eswar Koneti

SCCM Configmgr SQL Query How to get list of patches required by specific Computer

List of deployments to machines in a particular location

Some machines in a particular site had a certain problem so the question came up what had been deployed to them recently. The query below gives an answer. Replace @CollectionID with a Collection ID containing the machines.

select computer.Netbios_name0 as 'Host Name'
      , ws.LastHWScan as 'Last HW Scan'
    , ad.AdvertisementID as 'Advertisement ID'
    , ad.AdvertisementName as 'Advertisement Name'
    , pkg.Name AS 'Package Name'
    , ad.ProgramName as 'Program Name'
    , adState.LastAcceptanceStatusTime as 'Adv. Acceptance Status Time'
    , adState.LastAcceptanceStateName as 'Adv. Acceptance State'
    , adState.LastAcceptanceMessageIDname as 'Adv. Acceptance Status'
    , adState.LastStatusmessageIDName as 'Adv. Status Message'
    , adState.LastStatusTime as 'Adv. State Time'
    , adState.LaststateName as 'Adv. State'
    , adState.LastExecutionResult  as 'Adv. Last Result Code'
   , adState.LastExecutionContext as 'Adv. Last User Context'
      , users.Full_User_Name0 as 'Top User Full Name'
from v_Advertisement ad
join v_Package pkg
   on ad.PackageID = pkg.PackageID
join v_ClientAdvertisementStatus adState
   on ad.AdvertisementID = adState.AdvertisementID
join v_R_System computer
   on computer.Resourceid = adState.resourceid
   on computer.resourceid = ws.resourceid
left join v_GS_SYSTEM_CONSOLE_USAGE usage
   on computer.ResourceID = usage.ResourceID
left join v_R_User users
   on usage.TopConsoleUser0 = users.Unique_User_Name0
where ad.AdvertisementID in (
select AdvertisementID
              from v_advertisement
      ) and computer.ResourceID in (select machineid from vCollectionMembers where SiteID = @CollectionID)
select [CollectionID] = SiteID, CollectionName from v_Collections col
where CollectionType = 2 and col.SiteID not in ('SMS000US', 'SMSDM001') 

The basis was provided by Chris Kibble
Querying for Advertisement Status in SCCM
see also Software Update Views Configuration Manager

List of updates in an update group

SELECT distinct v_AuthListInfo.Title As [SoftwareUpdateGroup], v_UpdateInfo.ArticleID, v_UpdateInfo.BulletinID, v_UpdateInfo.Title 
FROM v_UpdateInfo
INNER JOIN v_CIAssignmentToCI ON v_UpdateInfo.CI_ID = v_CIAssignmentToCI.CI_ID
INNER JOIN v_CIRelation ON v_UpdateInfo.CI_ID = v_CIRelation.ToCIID
INNER JOIN v_AuthListInfo ON v_CIRelation.FromCIID = v_AuthListInfo.CI_ID
where v_AuthListInfo.Title like '%'
ORDER BY v_UpdateInfo.ArticleID

Active Connections to the SQL server

See the connections to the database, the connection type (NTLM/Kerberos), the ConfigMgr components that are connected, the number of reads and writes per connection and more.

USE master

FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS connection
ON connection.session_id = sdes.session_id


SELECT DB_NAME(dbid) AS DatabaseName
,OBJECT_NAME(objectid) AS ObjName
SELECT TEXT AS [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(connection.most_recent_sql_handle)
), '') AS Query

FROM sys.dm_exec_sql_text(connection.most_recent_sql_handle)

) sdest

Based on the excellent query from Marcelo Miorelli provided on https://stackoverflow.com/questions/1248423/how-do-i-see-active-sql-server-connections/1248444, only slightly modified.


Sample queries: https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/sample-queries-software-updates-configuration-manager 

Find a string by searching all tables: https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008