SQL Report for only Laptop Computers

 

 

If your boss ask to get list of laptops which are managed by SMS or SCCM.what do you do and how do you get that. Right click on computer and go to resource explorer to identify the computer is Laptop or Desktop ?

You can identify if the computer is Laptop or Desktop based on its chassis Types.

Below are listed the Chassis types available to create SCCM collection or reports.

For Laptops Chassis Types : 8 , 9, 10, 11, 12, 14, 18, 21

For Desktop Chassis Type : 3, 4, 5, 6, 7, 15, 16

For server Chassis  Type: 23

 

 

 

 

 

 

 

 

SELECT     dbo.v_R_System.Name0 AS [Computer Name], dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.User_Domain0 AS [Domain Name],
                      dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                      dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM.SystemRole0 AS [System OS Type],
                      dbo.v_GS_SYSTEM.SystemType0 AS [System Type]
FROM         dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                      dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE     (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '8') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '9') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '10') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '11') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '12') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '14') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '18') OR
                      (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = '21')

SCCM report for hardware specs of all desktops and laptops on the domain

 

 

 

---select * from v_GS_SYSTEM_ENCLOSURE

SELECT  distinct
CS.name0 as 'Computer Name',
CS.domain0 as 'Domain',
CS.UserName0 as 'User',
BIOS.SerialNumber0 as 'Bios serial',
SE.SerialNumber0 as 'System Enclosure serial',
CS.Manufacturer0 as 'Manufacturer',
CS.Model0 as 'model',
OS.Caption0 as 'OS',
RAA.SMS_Assigned_Sites0 as 'Site',
RAM.TotalPhysicalMemory0 as 'Total Memory',
sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
CPU.MaxClockSpeed0 as 'Max CPU Speed',
CPU.Name0 as 'CPU Model',
CPU.Is64Bit0 as '64 Bit Compatible'
from 
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID 
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
where
LDisk.DriveType0 =3
group by
CS.Name0,
CS.domain0,
CS.Username0,
BIOS.SerialNumber0,
SE.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
RAA.SMS_Assigned_Sites0,
RAM.TotalPhysicalMemory0,
CPU.MaxClockSpeed0,
CPU.Name0,
CPU.Is64Bit0
ORDER BY CS.name0

SCCM collection to list all the Laptop computers only

SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM,v_R_System
WHERE v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
GROUP BY Caption0,CSDVersion0
ORDER BY Caption0,CSDVersion0

What is Altiris and what are the modules it has inside??

Altiris service-oriented management solutions provide a modular and future-proof approach to managing highly diverse and widely distributed IT infrastructures. They are open solutions that enable lifecycle integration of client, handheld, server, network and other IT assets with audit-ready security and automated operation.

below are the modules it has inside.

• Asset Management Suite
• Backup Exec System Recovery Solution
• Client Management Suite
• Deployment Solution
• ServiceDesk
• Inventory Solution
• IT Management Suite
• Endpoint Virtualization Suite
• SecurityExpressions
• Server Management Suite
• Wise Package Studio
• Workflow

Single system required Patch’s report from SQL

---Single system required Patch’s report from SQL

 

SELECT DISTINCT

SYS.Name0

AS [Machine Name], UCS.Status AS [Patch Status Code],

CASE WHEN UCS.Status = '2' THEN 'Applicable' WHEN UCS.Status = '3' THEN 'Installed' ELSE '' END AS 'Patch Status', UI.BulletinID, UI.ArticleID,

UI.Title

FROM

v_R_System AS SYS LEFT OUTER

JOIN

v_Update_ComplianceStatusAll

AS UCS ON SYS.ResourceID = UCS.ResourceID INNER
JOIN

v_UpdateInfo

AS UI ON UCS.CI_ID = UI.CI_ID

WHERE

(UCS.Status IN ('3', '2')) AND (SYS.Name0 = 'ServerName'
)

state messages for some reason is not being updated correctly Causes Updates Compliance Report Issue

run the below script to get the actual system state. this will help to report back actual compliance of patch level to SCCM Server

 

Option Explicit
On Error Resume Next

Call RefreshServerComplianceState

' WScript.Echo "Finished"

Sub RefreshServerComplianceState()

    ' Initialize the UpdatesStore variable.
    dim newCCMUpdatesStore
    ' Create the COM object.
    set newCCMUpdatesStore = CreateObject ("Microsoft.CCM.UpdatesStore")

    ' Refresh the server compliance state by running the RefreshServerComplianceState method.
    newCCMUpdatesStore.RefreshServerComplianceState
    ' Output success message.
'    wscript.echo "Ran RefreshServerComplianceState."

End Sub

SCCM: Content Download to Cache Issues – 2008 / 2008 r2 / IIS 7 and IIS 7.5 and Webdav Issues

Issue

The issue that was reported to me was that the content was not downloading. My datatransferservice.log, my CAS.log, and ContentTransferManager.log all looked good. The client found a local DP to download the content from. However when I looked at my cache folder, I saw that only a couple of MB of data was downloaded and it never increased in size.

 

BITS was the culprit here !!!

 

In order to see what jobs are currently downloading, type in:

bitsadmin /list /allusers

This will give you output similar to the following:

BITSADMIN version 3.0 [ 7.5.7600 ]
BITS administration utility.
(C) Copyright 2000-2006 Microsoft Corp.

BITSAdmin is deprecated and is not guaranteed to be available in future versions of Windows.
Administrative tools for the BITS service are now provided by BITS PowerShell cmdlets.

{19A1D938-E1E9-437F-882E-1BFAABB707CB} ‘CCMDTS Job’ ERROR 146 / 3805 4752558 / UNKNOWN
Listed 1 job(s).

Notice how in this picture I have the following line:

{19A1D938-E1E9-437F-882E-1BFAABB707CB} ‘CCMDTS Job’ ERROR 146 / 3805 4752558 / UNKNOWN

This is no good. This basically means there’s an error somewhere in the transfer job.

Before we get into the next step of the solution, you must first understand what an SCCM distribution point is. An SCCM DP is simply a glorified web server. If you look at the Datatransferservice.log file on any of your SCCM clients, you’ll see a lot of URLs that look like the following:

http://DOMAIN:80/SMS_DP_SMSPKGF$/CEN00119/System32/Redist/MS/System/msvcrt.dll

What your client is basically doing is grabbing the files from these URLs and storing them into your local cache directory underneath the package ID.

So back to BITS. Since we saw an error in our bitsadmin /list /allusers, we need to find out exactly what that error is. The following command will show just that:

bitsadmin /info {19A1D938-E1E9-437F-882E-1BFAABB707CB} /verbose > c:bits2.txt

So what this command is doing is giving us the information about the failed BITS job that we saw before. The verbose command gives us the status of each file in the job. We then pipe this out to a file. Inside of that file, we see the following:

BITSADMIN version 3.0 [ 7.5.7600 ]
BITS administration utility.
(C) Copyright 2000-2006 Microsoft Corp.

BITSAdmin is deprecated and is not guaranteed to be available in future versions of Windows.
Administrative tools for the BITS service are now provided by BITS PowerShell cmdlets.

GUID: {19A1D938-E1E9-437F-882E-1BFAABB707CB} DISPLAY: ‘CCMDTS Job’
TYPE: DOWNLOAD STATE: ERROR OWNER: NT AUTHORITYSYSTEM
PRIORITY: LOW FILES: 146 / 3805 BYTES: 4752558 / UNKNOWN
CREATION TIME: 5/12/2010 11:31:04 AM MODIFICATION TIME: 5/12/2010 11:33:18 AM
COMPLETION TIME: UNKNOWN ACL FLAGS:
NOTIFY INTERFACE: REGISTERED NOTIFICATION FLAGS: 11
RETRY DELAY: 60 NO PROGRESS TIMEOUT: 2419200 ERROR COUNT: 147
PROXY USAGE: NO_PROXY PROXY LIST: NULL PROXY BYPASS LIST: NULL
ERROR FILE:    http://DOMAIN:80/SMS_DP_SMSPKGF$/CEN00119/Program Files/Hummingbird/Connectivity/9.00/HostExplorer/SDK/Samples/OHIO/Visual C++ Samples/HEOhioSample/MyTabCtrl.cpp -> C:Windowssystem32CCMCacheCEN00119.1.SystemProgram Files/Hummingbird/Connectivity/9.00/HostExplorer/SDK/Samples/OHIO/Visual C++ Samples/HEOhioSample/MyTabCtrl.cpp
ERROR CODE:    0×80190194 – HTTP status 404: The requested URL does not exist on the server.

ERROR CONTEXT: 0×00000005 – The error occurred while the remote file was being processed.

DESCRIPTION:
JOB FILES:

 

also check for Client side BITS Policy settings