Eswar Koneti's Blog

When you are doing Migration from SCCM 2007 to SCCM 2012,you may want to remove some of the drivers which are no longer needed .

This report list all the drivers which are part of any driver package so you can take action on them before you migrate to SCCM Configmgr 2012.

Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c82cc16a-06b7-49ae-ac11-2f6399047b6a

select
locCI.DisplayName as DriverName,locci.CI_ID,
drivers.DriverClass as DriverClass,
drivers.DriverProvider as ProviderName,
drivers.DriverVersion as DriverVersion,
CIs.IsEnabled as DriverEnabled0
from v_CI_DriversCIs drivers
inner join v_ConfigurationItems as CIs on CIs.CI_ID=drivers.CI_ID
inner join v_LocalizedCIProperties_SiteLoc as locCI on drivers.CI_ID=locCI.CI_ID
where locCI.CI_ID not in (select ci_id from v_DriverContentToPackage)

Status of Specific Advertisement can get from Default available report but what if you want to find the status of specific advertisement with its LastAcceptanceStatusTime greater or less than X days.

Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/30e4dbda-982b-43dc-8665-8dd142925d4f

DECLARE  @olddate datetime
        ,@NullVal datetime
SET @olddate = DATEADD(day,-5, GETUTCDATE())

select adv.AdvertisementName ‘Application’, sys.Name0 ‘System’, LastAcceptanceStateName ‘Acceptance’,
sts.LastAcceptanceStatusTime ‘AcceptTime_GMT’, sts.LastStatusMessageIDName ‘ExecutionStatus’,
sts.LastStateName ‘ExecutionState’,sts.LastStatusTime ‘ExecutionTime_GMT’,
adt.MessageName,sts.LastExecutionResult
from v_ClientAdvertisementStatus sts
join v_AdvertisementStatusInformation adt on adt.MessageID=sts.LastStatusMessageID
join v_Advertisement adv on adv.AdvertisementID=sts.AdvertisementID
join v_R_System sys on sys.ResourceID=sts.ResourceID
where ((sts.LastAcceptanceStatusTime  < @olddate) or (sts.LastStatusTime  < @olddate))
and  adv.AdvertisementName like ‘%Adobe_reader_Eng%’ and sts.LastExecutionResult like ‘1603‘
order by sys.Name0

Change the number of days and Advertisement Name.

Note: Please change the Quotes ,blog converts them as fancy here.

Having multiple sites and checking the status of WSUS synchronization on all servers tedious job and child sites must sync with its parent sites to ensure right compliance levels from clients.

This report will give you the status of WSUS server with Right content version.

When SCCM sync with Microsoft Updates,Content version will get increment to +1 and it passes this information to its child sites to sync with right version.

SELECT 
    US.SiteCode, S.ServerName, S.SiteName, US.ContentVersion, US.SyncTime
FROM
    update_syncstatus US, v_Site S 
    where US.SiteCode=S.SiteCode and US.contentversion <@Version
ORDER BY
    SyncTime

Prompt for @Version :

SELECT      distinct ContentVersion from
    update_syncstatus

long ago have posted report on computers with SCCM report for Last logged on computer name with application installed or not and SCCM report display computers with IPaddress based on Application/software

this report gives you computers with ip address and last logged on username from a given collection :

select A.Name0,c.IPAddress0 as ‘IP ADDRESS’,E.UserName0 as ‘Lastlogged user’
from v_R_System A inner join
v_FullCollectionMembership B on
A.ResourceID=B.ResourceID
Inner join v_GS_NETWORK_ADAPTER_CONFIGUR  C ON
A.ResourceID=C.ResourceID
Inner join v_GS_COMPUTER_SYSTEM E ON A.ResourceID=E.ResourceID
where CollectionID=@COLLID and C.IPEnabled0=’1′ and c.ipaddress0 !=’0.0.0.0′
group by A.Name0,c.IPAddress0 ,E.UserName0

Prompt for collection:

Select CollectionID,Name from v_Collection

 

Note: Please replace the single quotes from your Key board as it might give you syntax error when you do copy/paste.

SELECT
b.Netbios_Name0,
b.User_Name0,
CASE
WHEN a.FileVersion LIKE ’11.%’ THEN ‘Office 2003′
WHEN a.FileVersion LIKE ’12.%’ THEN ‘Office 2007′
WHEN a.FileVersion LIKE ’14.%’ THEN ‘Office 2010′
ELSE ‘Udda Version’ END AS ‘Office Version’,
a.FileName,
a.FileVersion,
a.FilePath

FROM
v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID

WHERE FileName = ‘outlook.exe’
AND (
c.System_OU_Name0 LIKE  ‘groupinfra.com/Corporate/Landlord SE/SE/Computers%’
OR
c.System_OU_Name0 LIKE  ‘groupinfra.com/Corporate/Landlord SE/DK/Computers%’
OR
c.System_OU_Name0 LIKE  ‘groupinfra.com/Corporate/Landlord SE/FI/Computers%’
OR
c.System_OU_Name0 LIKE  ‘groupinfra.com/Corporate/Landlord SE/NO/Computers%’
)
GROUP BY
b.Netbios_Name0,
b.User_Name0,
a.FileName,
a.FileVersion,
a.FilePath

ORDER BY
b.Netbios_Name0

had a requirement to see how many computer falls within the specific subnet.

select a.name0,b.IP_Subnets0 from v_R_System a,dbo.v_RA_System_IPSubnets b
where a.ResourceID=b.ResourceID and b.IP_Subnets0 in (’192.168.10.0′)
group by a.name0,b.IP_Subnets0

You can include other subnet details if you need.

Long back i posted report  http://eskonr.com/2009/12/sccm-report-for-computers-asset-information-including-sn-number-and-model-name/ to get computer information like computer name,Serial number,Processor,Physical memory,service Pack,computer model etc

In this post ,i have modified the report little bit to add more fields to it that gives you lot more information about the computer . It is  very simple and straigh forward report that just use Where class

what columns do you get after running this report ? Name,Serialnumber, Manufacturer,Model,Processor,Memory (KBytes),Size (MBytes),MAC Adress,IP Adress,AD Site,Last user logged in,Operating System,Service Pack,Creationdate in SMS,Last Hardware Scan

SELECT
A.Name0,
MAX (B.SerialNumber0) AS ‘Serialnumber’,
A.Manufacturer0,
A.Model0, C.Name0 AS ‘Processor’,
D.TotalPhysicalMemory0 AS ‘Memory (KBytes)’,
MAX ( E.Size0 ) AS ‘Size (MBytes)’,
MAX (F.MACAddress0) AS ‘MAC Adress’,
MAX (F.IPAddress0) AS ‘IP Adress’,
G.AD_Site_Name0 AS ‘AD Site’,
MAX (A.UserName0) AS ‘Last user logged in’,
H.Caption0 AS ‘Operating System’,
H.CSDVersion0 AS ‘Service Pack’,
G.Creation_Date0 AS ‘Creationdate in SMS’,
I.LastHWScan

FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_R_System G,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I

WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID

GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan

You can also other custom values from SQL Views ,please take a look Creating Custom Reports By Using Configuration Manager 2007 SQL Views http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22052

select 
	* 
from 
	dbo.v_Collection Col
Where
	Col.CollectionID not in (select CollectionID from dbo.v_Advertisement)

SELECT v_R_System.Netbios_Name0 AS Name, v_R_System.Client0,Min(A.AgentTime) as ‘Time Stamp’,
v_R_System.Operating_System_Name_and0 AS [AD Operating System]
FROM v_R_System INNER JOIN
v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID =v_R_System.ResourceID
inner join v_AgentDiscoveries A ON A.ResourceId=dbo.v_R_System.ResourceID
WHERE (v_FullCollectionMembership.CollectionID = @CollectionID) and A.AgentName like ‘Heartbeat Discovery’

group by Netbios_Name0,Client0,AgentTime,
Operating_System_Name_and0
order by Netbios_Name0 desc

Prompt for collection :

SELECT CollectionID, Name FROM v_Collection

This is continuation to the post avilable here on http://eskonr.com/2010/03/monthly-patch-statistics-reports-to-show-up-to-the-management-in-a-simplified-manner/

Report posted on the above link doesnt give you the required information what it gives in SMS 2003 since some of the columns in have been changed i.e product has blank value from v_GS_patchstausEX view etc in sccm 2007.

Below is the modified report that works in SCCM environment.

1) Patch Management summary:

select summ.ID,summ.QNumbers as ‘Q Number’,
COUNT(distinct ps.ResourceID) as ‘Requested’,
COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End)  as ‘Installed’,
ROUND(100.0*COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End)
/count(distinct ps.ResourceID),2) as ‘Success %’
from v_GS_PatchStatusEx ps
join v_ApplicableUpdatesSummaryEx summ on
   ps.UpdateID=summ.UpdateID
 where (summ.ID=’MS11-075′) and
         (summ.Type=’Microsoft Update’)
group by summ.ID,summ.QNumbers

order by summ.ID

PS: Please correct the quotes used in this query ,if you do copy paste,might give you error like “Incorrect Syntax error ” so type the quotes again by removing the exisinting ones.

I will post other quiries in the mean time what is avilable in SMS 2003 environment.

Attached is MOF file which you can directly import into your configmgr 2007 environment rather copy /paste.Patch_complilance_report.MOF (remove the txt extension,you will get MOF file).