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, 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