Friday, 22 October 2021

Tuesday, 19 October 2021

SCCM logs location

Written by   on   in ,   with No comments
Srever Based logs

• Location of Configuration Manager Setup/Site Server Installation logs: o Root of drive C

Location of Site Server log files: o installation Path>\Logs

. Location of Site system (remote) log files (that is Site Component Server): a <drive with most free space>\sms\logs

. Location of Management Point log files: Co-located with Site Server: <Installation Path>\SMS CCM\Logs If Configuration Manager client. installed first, the location of the Management

log files: %WINDIR%\CCM\logs

Location of Console log file:

o <Installation Path>\Admin Console AdminUlLog SMSAdmin Ut.log

Out sied of SCCM Logs location

. Windows Update Agent Log files

o%WINDIR%\Windows Update.log

o Get-Windows UpdateLog (Windows 10, Windows Server 2016)

WSUS Server Log files

o Located in %6Program Files%\Update Services LogFiles

o Change.log

o Software Distribution.Log

Policy Platform (Lantern) Log Files o%Program Files\Microsoft Policy Platform PolicyPlatformClient log

• Internet Information Services (IIS) Log files

o %SYSTEMDRIVE%\inetpub\logs\LogFiles\W3SVC1
Share:

Friday, 15 October 2021

SCCM SQL Queries-2

Written by   on   in   with No comments
  1. SQL Query To Retrieve Advanced Clients Assigned Site Code And Client Version
  2. SQL Query To Gather Video Card Memory Information
  3. SQL Query To See When Collections Were Last Updated
  4. SQL Query To Get Members Of A Specified Collection
  5. SQL Query Get User Information From A Specified OU
  6. SQL Query For Windows Media Player Versions
  7. SQL Query Get Machine and User Information From A Specified Collection
  8. SQL Query To Retrieve Clients Last Boot up Date
  9. SQL Query To List Machines With IIS , FTP Or Telnet Installed
  10. SQL Query To Count The Number Of Client Machines With McAfee Virus Scan Installed
  11. SQL Query To The Number Of Client Machines With McAfee Virus Scan Installed
  12. Advertisement Status For Multiple Advertisement IDs
  13. SQL Query To List Roaming Boundary IP Subnets
  14. SQL Query To Locate Full User Names From A Specified Collection
  15. SQL Query To Find Machines In A Specified Collection Having A Specified Application Installed
  16. SQL Query To View Web Reports By Category
  17. SQL Query To Retrieve A Users Full Name
  18. SQL Queries To Locate Client Resources For A Specified Subnet
  19. SQL Query To Count Microsoft Office Versions From Add And Remove Programs
  20. SQL Query To Count Assigned And Installed Resources By Site Code
  21. SQL Query To Count Computer Types For A Specified Collection Name
  22. SQL Query Add And Removed Programs For A Collection
  23. SQL Query To Find Machines With Torrent Downloading Clients Installed
  24. SQL Query To Retrieve Machines In A Specified Collections Last Logged On User Information
  25. SQL Query To Retrieve The Machine Name And Asset Tag For A Specified Serial Number
  26. SQL Query Get Users OU Name
  27. Retrieve All Queries Information
  28. SQL Query To Find And Count Software File And Product Information For A Specified Executable
  29. List Total Number Of Records In The Software File Table
  30. SQL Queries To Retrieve Serial Number Information
  31. SQL Query To Return Add OR Remove Programs Installed On A Specified Machine

 SCCM SQL Queries

1. SQL Query To Retrieve Advanced Clients Assigned Site Code And Client Version

Select
SD.Name0 ‘Machine Name’,
SC.SMS_Assigned_Sites0 ‘Assigned Site’,
SD.Client_Version0 Version
From v_R_System SD
Join v_RA_System_SmsAssignedSites SC on SD.ResourceID = SC.ResourceID
Join v_GS_Operating_System OS on SD.ResourceID = OS.ResourceID
Where SD.Client0 = 1
And SD.Client_Type0 = 1
Order By ‘Machine Name’

2.    SQL Query To Gather Video Card Memory Information

Select    
SD.Name0 ‘Machine Name’,
VC.Name0 ‘Video Card’,
Convert(VarChar, VC.AdapterRam0 / 1024) + ‘ MB’
From v_R_System SD
Join v_Gs_Video_Controller VC on SD.ResourceID = VC.ResourceID
Where VC.Name0 <> ‘ConfigMgr Remote Control Driver’
Order By SD.Name0

3.    SQL Query To See When Collections Were Last Updated

Select
CC.CollectionID,CN.CollectionName, CC.TimeUpdated
From Collection_MemberChg_Notif CC
Join Collections CN on CC.CollectionID = CN.SiteID
Order By CollectionName

4.    SQL Query To Get Members Of A Specified Collection

Select
Members.Name
‘Collection Members:’
From CollectionMembers Members
Join Collections Coll on Members.SiteID = Coll.SiteID
Where Coll.CollectionName = ‘All Systems’

5.    SQL Query Get User Information From A Specified OU

Select Distinct
CS.Name0 ‘Machine Name’,
CS.UserName0 ‘User Name’,
RU.Full_User_Name0 ‘Full Name’,
UOU.User_OU_Name0 ‘Users OU’,
RA.IP_Subnets0  ‘Subnet’
From v_Gs_Computer_System CS
Join v_RA_System_IPSubnets RA on RA.ResourceID = CS.ResourceID
Join v_R_User RU on RU.Unique_User_Name0 = CS.UserName0
Join v_RA_User_UserOUName UOU on UOU.ResourceID = RU.ResourceID
Where UOU.User_OU_Name0 = ‘DomainName.COM/OuName’
Order by  CS.Name0, CS.Username0, RU.Full_User_Name0, RA.IP_Subnets0

6.    SQL Query For Windows Media Player Versions

Select Distinct
v_R_System.Name0,
v_GS_SoftwareFile.FileName,
v_GS_SoftwareFile.FileDescription,
v_GS_SoftwareFile.FileVersion
From v_R_System
Join v_GS_SoftwareFile on v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID
Where FileName = ‘wmplayer.exe’

7.    SQL Query Get Machine and User Information From A Specified Collection

Select
SD.Name0 ‘Machine Name’,
SD.Resource_Domain_OR_Workgr0 ‘Resource Domain’,
SD.User_Name0 ‘Login ID’,
SD.User_Domain0 ‘Account Domain’,
USR.Full_User_Name0 ‘Full Name’,
PCB.SerialNumber0 ‘Serial Number’,
CS.Manufacturer0 Manufacturer,
CS.Model0 Model,
SAS.SMS_Assigned_Sites0 ‘Assigned Site Code’
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
Join v_R_User USR on SD.User_Name0 = USR.User_Name0
Join v_GS_PC_BIOS PCB on SD.ResourceID = PCB.ResourceID
Join v_GS_COMPUTER_SYSTEM CS on SD.ResourceID = CS.ResourceID
Join v_RA_System_SMSAssignedSites SAS on SD.ResourceID = SAS.ResourceID
Where COL.Name = ‘All Systems’

8.    SQL Query To Retrieve Clients Last Boot up Date

Select
SD.Name0 ‘Machine Name’,
SD.User_Name0 ‘Last Logged on User Name’,
Convert(VarChar(10), OS.LastBootUpTime0, 101)  ‘Last Boot Date’
From v_R_System SD
Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID
Order By ‘Machine Name’

9.    SQL Query To List Machines With IIS , FTP Or Telnet Installed

Select
SD.Name0 ‘Machine Name’,
SD.Operating_System_Name_and0 NOS,
SS.Name0 ‘Service Name’,
SS.DisplayName0 ‘Display Name’,
SS.StartMode0 ‘Start Type’,
SS.Started0 Started,
SS.State0 State,
SS.Status0 Status
From System_DISC SD
Join Services_DATA SS
on SS.MachineID = SD.ItemKey
Where SS.Name0 In (‘W3SVC’, ‘MsFtpSvc’, ‘TlntSvr’)
Order By ‘Machine Name’

10.    SQL Query To Count The Number Of Client Machines With McAfee Virus Scan Installed

Select
Count(SD.Name0) Counts,
PF.DisplayName0,
PF.Version0
From v_Add_Remove_Programs PF
Join v_R_System SD on PF.ResourceID = SD.ResourceID
Where PF.DisplayName0 = ‘McAfee VirusScan Enterprise’
Group By PF.DisplayName0, PF.Version0
Order By Counts, PF.Version0

11.    SQL Query To The Number Of Client Machines With McAfee Virus Scan Installed

Select
SD.Name0,
PF.DisplayName0,
PF.Version0
From v_Add_Remove_Programs PF
Join v_R_System SD on PF.ResourceID = SD.ResourceID
Where PF.DisplayName0 = ‘McAfee VirusScan Enterprise’
Group By SD.Name0, PF.DisplayName0, PF.Version0
Order By SD.Name0

12.    Advertisement Status For Multiple Advertisement IDs

Select
OfferName Name,
Recieved,
Failures,
ProgramsStarted Started,
ProgramsFailure Errors,
ProgramsSuccess Success,
OfferID ‘Advertisement ID’
From vOfferStatusSummarizerRoot
Where ScheduleToken = ‘0001128000080008’
And OfferID In (‘XXX12345’, ‘XXX67890’)
Order by OfferName

13.    SQL Query To List Roaming Boundary IP Subnets

Select IPSubnet
From RoamingBoundaryIPSubnet
Where SiteCode = ‘XXX’
Order by SiteCode


14.    SQL Query To Locate Full User Names From A Specified Collection

Select  
SD.Name0 ‘Machine Name’,
SD.User_Name0 ‘Login ID’,
USR.Full_User_Name0 ‘Full Name’
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
Join v_R_User USR on SD.User_Name0 = USR.User_Name0
Where COL.Name = ‘All Systems’

15.    SQL Query To Find Machines In A Specified Collection Having A Specified Application Installed

Select
SD.Name0 ‘Machine Name’,
SD.Resource_Domain_OR_Workgr0 ‘Resource Domain’,
SD.User_Name0 ‘Login ID’,
SD.User_Domain0 ‘Account Domain’,
USR.Full_User_Name0 ‘Full Name’,
ARP.DisplayName0 ‘Display Name’
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection CN on FCM.CollectionID = CN.CollectionID
Join v_R_User USR on SD.User_Name0 = USR.User_Name0
Join v_Add_Remove_Programs ARP on SD.ResourceID = ARP.ResourceID
Where CN.Name = ‘All Systems’
And ARP.DisplayName0 Like ‘%VPN%’

16.    SQL Query To View Web Reports By Category

Select
Name,
Comment,
ReportId,
Category
From v_Report
Where Category = ‘Asset Intelligence’
Order By Name

--Use this SQL Query to locate all of the available Report Categories for use with the query above:

Select Distinct Category
From V_Report
Order By Category

17.    SQL Query To Retrieve A Users Full Name

Select
SD.Name0 ‘Machine Name’,
SD.User_Name0 ‘Logon Name’,
UD.Full_User_Name0 ‘Full Name’
From v_R_System SD
Join v_R_User UD on SD.User_Name0 = UD.User_Name0
Where SD.User_Name0 = ‘LogonId’

18.    SQL Queries To Locate Client Resources For A Specified Subnet

Select Distinct
SD.Name0,
IP.Ip_Subnets0
From v_Ra_System_IpSubnets IP
Join v_R_System SD on IP.ResourceID = SD.ResourceID
Where IP.Ip_Subnets0 = ‘192.168.1.0‘

--Wildcard Search:

Select Distinct
SD.Name0,
IP.Ip_Subnets0
From v_Ra_System_IpSubnets IP
Join v_R_System SD on IP.ResourceID = SD.ResourceID
Where IP.Ip_Subnets0 Like ‘192.168.1.%’
Multiple Searches:
Select Distinct
SD.Name0,
IP.Ip_Subnets0
From v_Ra_System_IpSubnets IP
Join v_R_System SD on IP.ResourceID = SD.ResourceID
Where IP.Ip_Subnets0 In
(‘192.168.1.0‘,
‘192.168.2.0‘)
Order by IP.Ip_Subnets0,SD.Name0

19.    SQL Query To Count Microsoft Office Versions From Add And Remove Programs

Select
Count(ResourceID) Counts,
DisplayName0,
Publisher0,
Version0
From v_Add_Remove_Programs
Where Publisher0 = ‘Microsoft Corporation’
And DisplayName0 Like ‘Microsoft Office%’
Group By DisplayName0, Publisher0, version0
Order By Counts Desc

20.    SQL Query To Count Assigned And Installed Resources By Site Code

Select
SC.SiteCode ‘Site Code’,
(Select Count(ResourceId) From v_RA_System_SMSAssignedSites
Where SMS_Assigned_Sites0 = ‘XXX’) ‘Assigned’,
(Select Count(ResourceID) From v_RA_System_SMSInstalledSites
Where SMS_Installed_Sites0 = ‘XXX’)’Installed’
From v_Site SC
Order by SC.SiteCode

21.    SQL Query To Count Computer Types For A Specified Collection Name

Select
CN.Name ‘Collection Name’,
Case SE.ChassisTypes0
When 1 Then ‘Other’
When 2 Then ‘Unknown’
When 3 Then ‘Desktop’
When 4 Then ‘Low Profile Desktop’
When 5 Then ‘PizzaBox’
When 6 Then ‘Mini-Tower’
When 7 Then ‘Tower’
When 8 Then ‘Portable’
When 9 Then ‘Laptop’
When 10 Then ‘Notebook’
When 11 Then ‘Handheld Device’
When 12 Then ‘Docking Station’
When 13 Then ‘All-In-One’
When 14 Then ‘Sub-Notebook’
When 15 Then ‘Space Saving’
When 16 Then ‘Lunch Box’
When 17 Then ‘Main System Chassis’
When 18 Then ‘Expansion Chassis’
When 19 Then ‘Sub-Chassis’
When 20 Then ‘Bus Expansion Chassis’
When 21 Then ‘Peripheral Chassis’
When 22 Then ‘Storage Chassis’
When 23 Then ‘Rack-Mount Chassis’
When 24 Then ‘Sealed PC’
Else ‘Unknown’
End ‘Chassis Type’,
Count(*) ‘Chassis Count’
From v_Collection CN
Join v_FullCollectionMembership CM on CN.CollectionID = CM.CollectionID
Join v_R_System SD on CM.ResourceID = SD.ResourceID
Join v_Gs_System_Enclosure SE on SD.ResourceID = SE.ResourceID
Where CN.Name = ‘All Systems’
Group By CN.Name, SE.ChassisTypes0
Order By CN.Name

22.    SQL Query Add And Removed Programs For A Collection

Select
SD.Name0 ‘Machine Name’,
PF.DisplayName0 ‘Display Name’,
PF.ProdID0 ‘Product ID’,
PF.Publisher0 Publisher,
PF.Version0 Version
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
Join v_Add_Remove_Programs PF on SD.ResourceID = PF.ResourceID
Where COL.Name = ‘All Systems’
Group By SD.Name0, COL.CollectionID, COL.Name, PF.DisplayName0,
PF.ProdID0, PF.Publisher0, PF.Version0
Order By SD.Name0

23.    SQL Query To Find Machines With Torrent Downloading Clients Installed

Select
SD.Name0,
SF.FileName
From v_Gs_SoftwareFile SF
Join v_R_System SD on SD.ResourceId = SF.ResourceId
Where SF.FileName Like ‘%Azureus%’
Or SF.FileName Like ‘%BitComet%’
Or SF.FileName Like ‘%BitLord%’
Or SF.FileName Like ‘%BitPump%’
Or SF.FileName Like ‘%BitTornado%’
Or SF.FileName Like ‘%BitTorrent%’
Or SF.FileName Like ‘%Shareaza%’
Or SF.FileName Like ‘%Utorrent%’
Order By SD.Name0, SF.FileName

--Below is a SQL query to find applications that are not only used to download Torrent files but other files as well using P2P technology.
--Other File Sharing Applications SQL Query:

Select
SD.Name0,
SF.FileName
From v_Gs_SoftwareFile SF
Join v_R_System SD on SD.ResourceId = SF.ResourceId
Where SF.FileName Like ‘%BearShare%’
Or SF.FileName Like ‘%eDonkey%’
Or SF.FileName Like ‘%Emule%’
Or SF.FileName Like ‘%Kazaa%’
Or SF.FileName Like ‘%LimeWire%’
Or SF.FileName Like ‘%Morpheus%’
Order By SD.Name0, SF.FileName

24.    SQL Query To Retrieve Machines In A Specified Collections Last Logged On User Information

Select
C.CollectionName,
M.Name,
S.User_Name0,
U.Full_User_Name0
From Collections C
Join CollectionMembers M on C.SiteID = M.SiteID
Join System_Disc S on M.Name = S.Name0
Join User_DISC U on S.User_Name0 = U.User_Name0
Where CollectionName = ‘All Systems’

25.    SQL Query To Retrieve The Machine Name And Asset Tag For A Specified Serial Number
Select    

SD.Name0 ‘Machine Name’,
SE.SerialNumber00 ‘Serial Number’,
SE.SMBIOSAssetTag00 ‘Asset Tag’
From System_Disc SD
Join System_Enclosure_Data SE on SE.MachineID = SD.ItemKey
Where SE.SerialNumber00 = ‘MUR0FTIYM’

26.    SQL Query Get Users OU Name

Select Distinct
S.Name0 ‘Machine Name’,
S.User_Name0 ‘Logon ID’,
U.Full_User_Name0 ‘Full Name’,
O.User_Ou_Name0 ‘Organizational Unit’
From v_R_System S
Join v_R_User U on S.User_Name0 = U.User_Name0
Full Join v_Ra_User_UserOuName O on U.Unique_User_Name0 = O.User_Ou_Name0
Where O.User_Ou_Name0 Is Not Null

27.    Retrieve All Queries Information

Select
QueryKey ‘Query ID’,
Name ‘Query Name’,
Comments ‘Query Comments’,
Architecture ‘Query Object Type’,
CollectionID ‘Limit To Collection ID’
From Queries

28.    SQL Query To Find And Count Software File And Product Information For A Specified Executable

Select
SF.FileId ‘File ID’,
SF.ProductId ‘Product ID’,
SF.FileName ‘File Name’,
SF.FileDescription ‘File Description’,
SF.FileSize ‘File Size’,
SF.FileVersion ‘File Version’,
SP.ProductName ‘Product Name’,
SP.ProductVersion ‘Product Version’,
SP.CompanyName ‘Company Name’,
Count(SF.FileName) ‘Installed Count’
From System_DISC SD
Join vSMS_G_System_SoftwareFile SF on SF.ClientId = SD.ItemKey
Join vSMS_G_System_SoftwareProduct SP on SP.ClientId = SD.ItemKey  
Where SF.ProductId = SP.ProductId
And SF.FileName = ‘FileName.Exe’
Group by SF.FileId,SF.ProductId,SF.FileName,SF.FileDescription,
SF.FileSize,SF.FileVersion,SP.ProductName,SP.ProductVersion,SP.CompanyName
Order By SF.FileId ASC

29.    List Total Number Of Records In The Software File Table

Select Count(*) as ‘Total Number Of Records’
From SoftwareFile

30.    SQL Queries To Retrieve Serial Number Information

Select
SD.Name0 ‘Machine Name’,
SB.SerialNumber0 ‘Serial Number’
From v_R_System SD
Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID
Order By SD.Name0

--The Serial Number For A Specified Machine

Select
SD.Name0 ‘Machine Name’,
SB.SerialNumber0 ‘Serial Number’
From v_R_System SD
Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID
Where SD.Name0 = ‘Machine_Name’
Order By SD.Name0

--The Machine Name For A Specified Serial Number

Select
SD.Name0 ‘Machine Name’,
SB.SerialNumber0 ‘Serial Number’
From v_R_System SD
Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID
Where SB.SerialNumber0 = ‘Serial_Number’
Order By SD.Name0

31.    SQL Query To Return Add OR Remove Programs Installed On A Specified Machine

Select
P.DisplayName0,
P.ProdId0,
P.Publisher0,
P.Version0
From v_Add_Remove_Programs P
Join v_R_System S on P.ResourceId = S.ResourceId
Where Name0 = ‘MachineName’
 

Share:

Wednesday, 13 October 2021

WQL query to create collections based on subnets Boundaries

Written by   on   in ,   with No comments
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_Boundary on SMS_Boundary.Value = SMS_R_System.IPSubnets where SMS_Boundary.DisplayName = "BoundaryDescription"
Share: