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, that’s why I created this page.
Software from Russian companies or authors
The following Configuration Manager SQL query searches for software from companies based in Russia. The list of software publishers has been taken from wikipedia (link). I do not want to judge these publishers in any way. Mind that there are also freeware products from Russian authors available of which Igor Pavlov’s 7-zip is the most popular. Others are AIMP media player, the browser Yandex and the messenger app Telegram. The founder of Telegram, Pavel Durov, meanwhile lives outside of Russia.
select distinct v_R_System.Name0, v_GS_ADD_REMOVE_programs.DisplayName0, v_GS_ADD_REMOVE_programs.Publisher0 from v_R_System
inner join v_GS_ADD_REMOVE_programs on v_GS_ADD_REMOVE_programs.ResourceID = v_R_System.ResourceId
where (v_GS_ADD_REMOVE_programs.Publisher0 like '%1C Company%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Ashmanov & Partners%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Baltic Soft%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%CBOSS Corporation%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Center of Financial Technologies%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Cognitive Technologies%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Doc+%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Dr.Web%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Eagle Dynamics%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%ElcomSoft%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Elecard%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Kaspersky Lab%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Mapilab%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Metashape%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Nanosemantics%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Nginx%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Okko%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%PROMT%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Raidix%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Reksoft%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Rhonda Software%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%RusBITech%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Sibers%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%SPB Software%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Telesystems%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Traccar%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%TrueConf%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Winaero%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%AIMP%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Igor Pavlov%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Telegram%'
or v_GS_ADD_REMOVE_programs.Publisher0 like '%Yandex%'
)
select distinct v_R_System.Name0, v_GS_ADD_REMOVE_programs_64.DisplayName0, v_GS_ADD_REMOVE_programs_64.Publisher0 from v_R_System
inner join v_GS_ADD_REMOVE_programs_64 on v_GS_ADD_REMOVE_programs_64.ResourceID = v_R_System.ResourceId
where (v_GS_ADD_REMOVE_programs_64.Publisher0 like '%1C Company%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Ashmanov & Partners%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Baltic Soft%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%CBOSS Corporation%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Center of Financial Technologies%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Cognitive Technologies%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Doc+%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Dr.Web%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Eagle Dynamics%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%ElcomSoft%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Elecard%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Kaspersky Lab%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Mapilab%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Metashape%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Nanosemantics%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Nginx%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Okko%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%PROMT%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Raidix%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Reksoft%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Rhonda Software%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%RusBITech%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Sibers%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%SPB Software%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Telesystems%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Traccar%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%TrueConf%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Winaero%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%AIMP%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Igor Pavlov%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Telegram%'
or v_GS_ADD_REMOVE_programs_64.Publisher0 like '%Yandex%'
)
select DisplayName0, count(*) as Count from v_GS_ADD_REMOVE_PROGRAMS_64 as ARP
where (
ARP.Publisher0 like '%1C Company%'
or ARP.Publisher0 like '%Ashmanov & Partners%'
or ARP.Publisher0 like '%Baltic Soft%'
or ARP.Publisher0 like '%CBOSS Corporation%'
or ARP.Publisher0 like '%Center of Financial Technologies%'
or ARP.Publisher0 like '%Cognitive Technologies%'
or ARP.Publisher0 like '%Doc+%'
or ARP.Publisher0 like '%Dr.Web%'
or ARP.Publisher0 like '%Eagle Dynamics%'
or ARP.Publisher0 like '%ElcomSoft%'
or ARP.Publisher0 like '%Elecard%'
or ARP.Publisher0 like '%Kaspersky Lab%'
or ARP.Publisher0 like '%Mapilab%'
or ARP.Publisher0 like '%Metashape%'
or ARP.Publisher0 like '%Nanosemantics%'
or ARP.Publisher0 like '%Nginx%'
or ARP.Publisher0 like '%Okko%'
or ARP.Publisher0 like '%PROMT%'
or ARP.Publisher0 like '%Raidix%'
or ARP.Publisher0 like '%Reksoft%'
or ARP.Publisher0 like '%Rhonda Software%'
or ARP.Publisher0 like '%RusBITech%'
or ARP.Publisher0 like '%Sibers%'
or ARP.Publisher0 like '%SPB Software%'
or ARP.Publisher0 like '%Telesystems%'
or ARP.Publisher0 like '%Traccar%'
or ARP.Publisher0 like '%TrueConf%'
or ARP.Publisher0 like '%Winaero%'
or ARP.Publisher0 like '%AIMP%'
or ARP.Publisher0 like '%Igor Pavlov%'
or ARP.Publisher0 like '%Telegram%'
or ARP.Publisher0 like '%Yandex%'
)
group by ARP.DisplayName0
select DisplayName0, count(*) as Count from v_GS_ADD_REMOVE_PROGRAMS as ARP
where (
ARP.Publisher0 like '%1C Company%'
or ARP.Publisher0 like '%Ashmanov & Partners%'
or ARP.Publisher0 like '%Baltic Soft%'
or ARP.Publisher0 like '%CBOSS Corporation%'
or ARP.Publisher0 like '%Center of Financial Technologies%'
or ARP.Publisher0 like '%Cognitive Technologies%'
or ARP.Publisher0 like '%Doc+%'
or ARP.Publisher0 like '%Dr.Web%'
or ARP.Publisher0 like '%Eagle Dynamics%'
or ARP.Publisher0 like '%ElcomSoft%'
or ARP.Publisher0 like '%Elecard%'
or ARP.Publisher0 like '%Kaspersky Lab%'
or ARP.Publisher0 like '%Mapilab%'
or ARP.Publisher0 like '%Metashape%'
or ARP.Publisher0 like '%Nanosemantics%'
or ARP.Publisher0 like '%Nginx%'
or ARP.Publisher0 like '%Okko%'
or ARP.Publisher0 like '%PROMT%'
or ARP.Publisher0 like '%Raidix%'
or ARP.Publisher0 like '%Reksoft%'
or ARP.Publisher0 like '%Rhonda Software%'
or ARP.Publisher0 like '%RusBITech%'
or ARP.Publisher0 like '%Sibers%'
or ARP.Publisher0 like '%SPB Software%'
or ARP.Publisher0 like '%Telesystems%'
or ARP.Publisher0 like '%Traccar%'
or ARP.Publisher0 like '%TrueConf%'
or ARP.Publisher0 like '%Winaero%'
or ARP.Publisher0 like '%AIMP%'
or ARP.Publisher0 like '%Igor Pavlov%'
or ARP.Publisher0 like '%Telegram%'
or ARP.Publisher0 like '%Yandex%'
)
group by ARP.DisplayName0
Links:
Software companies of Russia
https://en.wikipedia.org/wiki/Category:Software_companies_of_Russia
Well-known Russian software
https://gettotext.com/well-known-russian-software-and-the-best-alternatives/
Top Russian Software Companies
https://www.goodfirms.co/directory/country/top-software-development-companies/ru
Telegram founder commits to Ukraine user privacy
https://www.bbc.com/news/technology-60561168
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