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.

Information about a particular Software that is registered Programs and Features

Query 1: Count Versions. Query 2: Details. Mind that this is for 32 bit software, for 64 bit use v_GS_ADD_REMOVE_PROGRAMS_64.

Declare @SoftwareName varchar (255)
Set @SoftwareName = '%chrome%'

select Version0, count(*) as Count from v_GS_ADD_REMOVE_PROGRAMS as ARP
where ARP.DisplayName0 like @SoftwareName
group by ARP.Version0


Select
distinct vrs.Name0,
vrs.User_Name0,
ARP.DisplayName0,
ARP.Version0,
ARP.InstallDate0,
ARP.Publisher0,
ARP.ProdID0
from v_R_System as Vrs
inner join v_GS_ADD_REMOVE_PROGRAMS as ARP on Vrs.ResourceID=ARP.ResourceID
where ARP.DisplayName0 like @SoftwareName
and ARP.Publisher0 is not null

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,
v_Collection.Name
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
(SELECT PackageID
FROM v_TaskSequenceReferencesInfo AS v_TaskSequenceReferencesInfo_1))
GROUP BY v_Package.PackageID, v_Package.Name, v_Package.PackageType,v_package.version,
V_Package.Language,v_Advertisement.AdvertisementName,v_Advertisement.AdvertisementID,v_Collection.Name
HAVING (v_Package.PackageType = '0')
ORDER BY Name

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] ,
   Deadline=cdl.Deadline,
      vrs.Active0,
      wss.LastHWScan,
      vrs.AD_Site_Name0
   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
  and
  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
http://eskonr.com/2015/10/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
left join v_GS_WORKSTATION_STATUS ws
   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
https://www.christopherkibble.com/querying-advertisement-status-sccm/
see also Software Update Views Configuration Manager
https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/software-updates-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
go
SELECT
sdes.session_id
,connection.auth_scheme
,sdes.program_name
,sdest.DatabaseName
,sdes.login_time
,sdes.last_request_start_time
,sdes.last_request_end_time
,sdes.is_user_process
,sdes.host_name
,sdes.login_name
,sdes.status
,connection.num_reads
,connection.num_writes
,connection.last_read
,connection.last_write
,sdes.reads
,sdes.logical_reads
,sdes.writes
,sdest.ObjName
,sdes.client_interface_name
,sdes.nt_domain
,sdes.nt_user_name
,connection.client_net_address
,connection.local_net_address

FROM sys.dm_exec_sessions AS sdes

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

CROSS APPLY (

SELECT DB_NAME(dbid) AS DatabaseName
,OBJECT_NAME(objectid) AS ObjName
,COALESCE((
SELECT TEXT AS [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(connection.most_recent_sql_handle)
FOR XML PATH('')
,TYPE
), '') 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.

Links

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

SCCM SQL Queries https://kapatac.com/wp-content/uploads/2018/05/SCCM-SQL-Queries.pdf