SCCM 2012 Query based Collection- Computers with a specific BIOS versions

WQL for Creating CM 12 Dynamic collection for Bios versions.

I have used this for Lenevo Bios versions...

select distinct SMS_R_System.NetbiosName from  SMS_R_System inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET35WW (1.14 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET45WW (1.24 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET44WW (1.23 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET43WW (1.22 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET40WW (1.19 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET39WW (1.18 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET37WW (1.16 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET36WW (1.15 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET35WW (1.14 )" or SMS_G_System_PC_BIOS.SMBIOSBIOSVersion like "GFET28WW (1.07 )"

Corresponding query in  SQL will be..

select SMBIOSBIOSVersion0
FROM dbo.v_GS_PC_BIOS
where SMBIOSBIOSVersion0 Like 'GFET45WW (1.24 )' or SMBIOSBIOSVersion0 Like 'GFET44WW (1.23 )' or SMBIOSBIOSVersion0 Like 'GFET43WW (1.22 )' or SMBIOSBIOSVersion0 Like 'GFET40WW (1.19 )' or 

SMBIOSBIOSVersion0 Like 'GFET39WW (1.18 )' or SMBIOSBIOSVersion0 Like 'GFET37WW (1.16 )' or SMBIOSBIOSVersion0 Like 'GFET36WW (1.15 )' or SMBIOSBIOSVersion0 Like 'GFET35WW (1.14 )' or 

SMBIOSBIOSVersion0 Like 'GFET28WW (1.07 )'
ORDER BY SMBIOSBIOSVersion0 

Import already registered computers or Users in SCCM 2012 using a file to a collection

How to import already registered computers or Users in SCCM 2012 using a file a file to a collection. It’s easy to import a computers from a CSV file with NetBIOS name and MAC address but importing computer which are already registered to console is bit difficult. There are situations when a collection needs to be created and no query seems working in those situation a manually add resource option is left out which is time taking.

To automate/import registered computer to a collection using a file, I followed below steps which seems to be working for me…

Create list of computer which needs to be imported then have them comma separated using below steps and finally add resources to the collection.

The objective is to arrange it in a comma separated way so that it can be imported in a single shot rather doing one by one.

Output should be username01, username01 etc.

Now select the collection Right click à Add resources à Copy computer names (comma separated) under Name String Contains à SearchàSelect allàAddàOK.

Refresh the collection and should be populated with computers shortly.

SCCM 2012 Report for Internet Explorer(IE) versions

If you looking for a reports for IE versions using SCCM. Please try Out of the Box reports, this is easy, accurate and reliable report i have found so far. I have tried custom reports as well.

Here are the steps..

Open SCCM Console Navigate to

Monitoring--> Reporting-->Reports --> Search--> Type "Computers with a specific file"

Double click the report Type "iexplore.exe" under file Name and click View report.

Export as excel and short it with file versions you need.

Thanks

Where do Start Menu\All Programs shortcuts reside on Windows 7 ?

To create a shortcut on Desktop for All users and Start menu /All programs...

Most of us work on this and remember these by hear but its handy when forget... 

For Global / All users Start Menu is located at:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs

All users / Default Desktop is located at:

C:\Users\Public\Desktop

NB:These folders are hidden so make sure to check show hidden file from folder option.

SCCM 2012 - Query based collection for Specific app installed per collection

Query based collection for Specific app installed in a collection. This helps if you need to deploy updated version of application and upgrade existing one...

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_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Name of Application"

SCCM 2012 - Software Registered in add and remove programs excluding software updates

If you need the list of apps installed in add remove program and not the installed updates. This Query will be helpful.


Select A.DisplayName0 as 'Name of Software Installed',A.InstallDate0 as 'Date of Install', A.Version0 as 'Version Number',A.Publisher0 as 'Publisher Name'
FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B
WHERE A.ResourceID = B.ResourceID
AND A.DisplayName0 NOT Like 'Update for Microsoft%'
AND A.DisplayName0 NOT LIKE 'Hotfix for %'
AND A.DisplayName0 NOT LIKE 'Security Update for %'
AND A.DisplayName0 NOT LIKE 'Update for Microsoft %'
AND A.DisplayName0 NOT LIKE 'Update for Office %'
AND A.DisplayName0 NOT LIKE 'Update for Outlook %'
AND A.DisplayName0 NOT LIKE 'Update for Windows %'
AND A.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
AND Name0 = @computer
GROUP BY A.DisplayName0,A.Version0, A.Publisher0,A.InstallDate0
ORDER BY A.DisplayName0

CM 12 - Summary of Computer specific details(Hardware) with IP Address and Subnet

Custom Query for Hardware details with IP Address and Subnet. Please type your collection name to view the reports.


SELECT DISTINCT
                      TOP (100) PERCENT dbo.v_R_System_Valid.Netbios_Name0 AS [Computer Name],
                      dbo.v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], dbo.v_Site.SiteName AS [SMS Site Name],
                      CASE WHEN (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 IS NULL OR
                      v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
                      THEN 'Unknown' ELSE v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 END AS [Top Console User],
                      dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number],
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer,
                      dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
                      dbo.v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
                          (SELECT     SUM(dbo.v_GS_LOGICAL_DISK.Size0) AS Expr1
                            FROM          dbo.v_GS_LOGICAL_DISK INNER JOIN
                                                   dbo.v_FullCollectionMembership ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID
                            WHERE      (dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System_Valid.ResourceID) AND
                                                   (dbo.v_FullCollectionMembership.CollectionID = @CollectionID)) AS [Disk Space (MB)],
                          (SELECT     SUM(v_GS_LOGICAL_DISK_2.FreeSpace0) AS Expr1
                            FROM          dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_2 INNER JOIN
                                                   dbo.v_FullCollectionMembership AS v_FullCollectionMembership_2 ON
                                                   v_FullCollectionMembership_2.ResourceID = v_GS_LOGICAL_DISK_2.ResourceID
                            WHERE      (v_GS_LOGICAL_DISK_2.ResourceID = dbo.v_R_System_Valid.ResourceID) AND
                                                   (v_FullCollectionMembership_2.CollectionID = @CollectionID)) AS [Free Disk Space (MB)],
                      dbo.v_RA_System_IPAddresses.IP_Addresses0, dbo.v_RA_System_IPSubnets.IP_Subnets0
FROM         dbo.v_R_System_Valid INNER JOIN
                      dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID LEFT OUTER JOIN
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE ON
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_PROCESSOR ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_FullCollectionMembership AS v_FullCollectionMembership_1 ON
                      v_FullCollectionMembership_1.ResourceID = dbo.v_R_System_Valid.ResourceID LEFT OUTER JOIN
                      dbo.v_Site ON v_FullCollectionMembership_1.SiteCode = dbo.v_Site.SiteCode INNER JOIN
                      dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_1 ON v_GS_LOGICAL_DISK_1.ResourceID = dbo.v_R_System_Valid.ResourceID AND
                      v_GS_LOGICAL_DISK_1.DeviceID0 = SUBSTRING(dbo.v_GS_OPERATING_SYSTEM.WindowsDirectory0, 1, 2) INNER JOIN
                      dbo.v_RA_System_IPSubnets ON dbo.v_R_System_Valid.ResourceID = dbo.v_RA_System_IPSubnets.ResourceID INNER JOIN
                      dbo.v_RA_System_IPAddresses ON dbo.v_R_System_Valid.ResourceID = dbo.v_RA_System_IPAddresses.ResourceID LEFT OUTER JOIN
                      dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON
                      dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = dbo.v_R_System_Valid.ResourceID

how to create an application for a registry file in SCCM 2012

Use below syntax for packaging a registry file as an application in CM 12.

regedit.exe /s file.reg

Example : regedit.exe /s disable_acrobat_update.reg

use deployment type as script while creating an app in CM 12.

Config Manager 2012 - Custom Reports Infected systems with Computer Name

Endpoint Protection Infected systems with Computer Name

DECLARE @StartDate datetime

DECLARE @EndDate datetime

SET @StartDate = DATEADD(hour, -336, GETDATE())

SET @EndDate = GETDATE()

select

ComputerName as 'Computer Name',

ComputerStatus as 'EP Remediation Status (1=None, 2=Cleaned)',

EngineVersion as 'Client Definition Version',

ProcessName as 'Process Name',

ThreatName as 'Threat Name',

SeverityID as 'Severity (5=Severe, 4=Moderate)',

ActionSuccess as 'Removed Threat',

PathName as 'Path Name',

MIN(DetectionTime) as FirstDetection,

MAX(DetectionTime) as LastDetection,

COUNT(*) as 'Infection Count'

from

(

select

t.ResourceID,

ISNULL((select top 1 Resource_Names0 from v_RA_System_ResourceNames where

ResourceID=s.ResourceID),Netbios_Name0) as ComputerName,


ai.ComputerStatus,

ah.AntivirusSignatureVersion EngineVersion,

t.Process ProcessName,

t.ThreatName ThreatName,

t.SeverityID SeverityID,

t.ActionSuccess ActionSuccess,

t.Path PathName,

t.DetectionTime

from v_GS_Threats t

join v_R_System s on t.ResourceID=s.ResourceID

join v_FullCollectionMembership c on t.ResourceID=c.ResourceID

left join v_ThreatCatalog tc on t.ThreatID=tc.ThreatID

left join v_GS_AntimalwareHealthStatus ah on t.ResourceID = ah.ResourceID

left join v_GS_AntimalwareInfectionStatus ai on t.ResourceID = ai.ResourceID

where

--c.CollectionID=@CollectionID and

DATEADD(day, 0, DATEDIFF(day, 0, t.DetectionTime)) between @StartDate and @EndDate

) as Infections

group by ResourceID, ComputerName, ComputerStatus,EngineVersion, ProcessName, SeverityID, ActionSuccess,

Threatname, PathName

order by FirstDetection

Software installed for all machines in a collection- SCCM

Select DISTINCT SYS.Netbios_Name0,SYS.Resource_Domain_OR_Workgr0,SP.CompanyName, SP.ProductName, SP.ProductVersion

FROM v_GS_SoftwareProduct SP

JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID

JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

WHERE fcm.CollectionID = '<CollectionID>'

ORDER By SYS.Netbios_Name0, SP.CompanyName, SP.ProductName, SP.ProductVersion