Here’s some queries to build a detailed dashboard for adobe reader versions, which will give you a count by version (Grouping by major version) and detailed information on computers by version;
Reference; http://smsug.ca/blogs/garth_jones/archive/2012/05/13/adobe.aspx
Also note that each of these reports can be use without the sccm dashboard accelerator
Here are some reports that I’ve created to help our technical support team with the management of users accounts in active directory. This allows them to get the following information about users (also note that this has been tested in a single AD (DFL 2003) domain and Sccm 2007 sp2);
So the first report will give you a count of users by status, Enable, Disable, Password Set to never Expired, Account Expired and Stale Account ( which as exceed their threshold of 90 days). Also, with the ability to get a list of all user accounts with a specific status, ex; all expired Account or All users account with password set to never expired.
And the second report will count users by OU, with the ability to drill down and get the users informations in the selected OU and then get the group membership by Users.
First Report;
Count user accounts in a specific state
All Users in a specific state
Second Report;
User Accouts by OU
All Users in the selected OU
Group Membership for a selected user
So here’s the queries
FirstReport, (contains queries for the two reports)
https://docs.google.com/document/d/19FfHS_BPloTWFIt2z-piWYkinvWbdtm1kdShH-ua1M0/edit
SecondReports, (contains queries for the three reports)
https://docs.google.com/document/d/1BLuTrK7YOUasF9ofe0MNLyNmVWfjSpOx6gVgJvhUF4U/edit
**** user discovery must be enable with all these attributes ****
If you’re trying to list all active directory computers without sccm client, here’s a report that can help you to get this information. First you have to configure a linked server in sql (http://msdn.microsoft.com/en-us/library/windows/desktop/aa772380(v=vs.85).aspx), then create a report with the following query;
***************************************************************
select
AD.cn as ‘Computer Name’,
CASE When AD.operatingSystem is null then ‘*’
Else AD.operatingSystem END as ‘Operating System’,
CASE When AD.operatingSystemServicePack is null then ‘*’
Else AD.operatingSystemServicePack END as ‘Service Pack’,
case
when AD.lastLogontimeStamp is not null then
cast(DATEADD(mi,(cast(AD.lastLogontimeStamp as bigint) / 600000000) – 157258080
+ DATEDIFF(Minute,GetUTCDate(),getdate()),0) as varchar(32))
else ‘*’
end as ‘Lastlogon Timestamp’,
case
when AD.lastLogontimeStamp is not null then
cast(DATEDIFF(D,DATEADD(mi,(cast(AD.lastLogontimeStamp as bigint) / 600000000) – 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0), GETDATE()) as varchar(32))
else ‘*’
end as ‘days’,
case when ad.pwdLastSet is null then ‘*’
when ad.pwdLastSet = ‘0’ then ‘*’
ELSE cast (DATEADD(mi,(cast(ad.pwdLastSet as bigint) / 600000000) – 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) as varchar(32))
end as ‘Password Last Set’,
case when ad.pwdLastSet is null then ‘*’
when ad.pwdLastSet = ‘0’ then ‘*’
ELSE cast(DATEDIFF(D,DATEADD(mi,(cast(ad.pwdLastSet as bigint) / 600000000) – 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0), GETDATE()) as varchar(32))
end as ‘days’
From
openquery (ADSI, — linked server Name
‘SELECT cn,
operatingSystem,
operatingSystemServicePack,
lastLogonTimestamp,
pwdLastSet
FROM ”LDAP://DCNAME”
WHERE objectCategory = ”Computer”’) as AD
Where
AD.cn not in (Select name0 from v_GS_Computer_System as CS)
order by
AD.lastlogontimestamp
***************************************************************
Also, if you want you can modify the where statement and monitor Active Directory stale client;
***************************************************************
DATEDIFF(D,DATEADD(mi,(cast(ad.lastlogontimestamp as bigint) / 600000000) – 157258080 + DATEDIFF(Minute,GetUTCDate(),GetDate()),0),GETDATE()) > 30 — threshold
and DATEDIFF(D,DATEADD(mi,(cast(ad.pwdlastset as bigint) / 600000000) – 157258080 + DATEDIFF(Minute,GetUTCDate(),GetDate()),0),GETDATE()) > 30 — threshold
***************************************************************
based on Garth Jones post;
http://smsug.ca/blogs/garth_jones/archive/2008/12/03/how-to-add-ad-data-to-configmgr-reporting.aspx
reference;
In some of the reports in sccm the result will display a error code for troubleshooting purpose, but often they dont provide any description about this error code. So here’s a example on how you can get more detail on these errors. Note that in this example I will use a report on Software update – client health report.
1- You must download the following file, convert it to csv, replace all the comma by two single quote, import the file as a flat file in your sccm database, then assign the appropriate permission on the newly created table. (reference; http://ittv.net/VideoPlayer/TabId/57/VideoId/1574/SCCM-2007-Client-Health-Monitoring.aspx)
– Download Custom ConfigMgr Error Code;
https://skydrive.live.com/?cid=80514c55d60387d4&id=80514C55D60387D4%211522
2- Create a reports call Software Update client Health Reports;
*****************************************************************
SQL Query (make sure to change ConfigMgr_Error_Descriptions to reflect the newly created table)
Select Distinct
sys.Netbios_name0,
CASE WHEN SYS.Active0 = 1 THEN ‘Yes’ ELSE ‘No’ END as ‘Active’,
DATEDIFF(DAY, LastScanTime, GetDate()) as ‘Last SUP Scan(DAYS)’,
DateDiff(D, OS.LastBootUpTime0, GetDate()) ‘Last Boot (Days)’,
DATEADD(mi,(SYS.lastLogontimeStamp0 / 600000000) – 157258080,0) AS ‘Lastogon TS’,
CASE WHEN LastErrorCode = 0 THEN ‘Successfull’ ELSE CAST(LastErrorCode as varchar(32)) END as ‘Error Code’,
CASE WHEN CU.Description LIKE ” THEN ‘Unknown’
WHEN CU.Description is NULL THEN ‘*’
ELSE CAST (CU.Description as Varchar (64)) END as ‘Description’,
CASE WHEN CU.[Symbolic Name] is Null Then ‘*’ ELSE CAST (CU.[Symbolic Name] as varchar(32)) END as ‘Symbolic Name’,
LastScanPackageLocation as ‘Last Scan Location’,
LastScanPackageVersion as ‘Last Scan Pakage’,
LastWUAVersion as WUAVersion,
st.StateName as ‘Status’
from v_updateScanStatus up
join v_r_system sys on sys.resourceid = up.resourceid
left join ConfigMgr_Error_Descriptions CU on up.LastErrorCode=cu.errorcode
left join v_Gs_Operating_System OS on sys.resourceid = os.ResourceID
left join v_StateNames st on st.TopicType = 501
and st.StateID = (case when (isnull(LastScanState, 0)=0
and Left(isnull(sys.Client_Version0, ‘4.0’),1)<‘4’)
then 7 else isnull(LastScanState, 0) end)
left join v_ClientCollectionMembers ccm on sys.ResourceID = ccm.ResourceID
WHERE ccm.collectionID = @CollID
Prompt
Name = CollID
SQL Query
begin
if (@__filterwildcard = ”)
select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name
else
select CollectionID as CollectionID, Name as CollectionName from v_Collection
WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard
order by Name
end
*****************************************************************
view https://anotherblabla.wordpress.com/2012/03/22/sccm-security-updates-compliance-overview-report-v2/ for some enhancements
Here’s some reports that I use to monitor the level of compliance for security updates and this by collection. Also it’s possibe to drill down and see which security updates is missing.
thanks to Garth Jones and Anoop for these informations, which has been very helpfull when I have created those reports
http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx
1- create a report called Security Updates Detail.
Report Sql Query
*********************************************************
declare @RscID int;
select @RscID=ResourceID from v_R_System where ((Name0 = @MachineName) and (Active0 = 1));
select
catinfo.CategoryInstanceName as Vendor,
catinfo2.CategoryInstanceName as UpdateClassification,
ui.BulletinID as BulletinID,
ui.ArticleID as ArticleID,
ui.Title as Title,
Targeted=(case when ctm.ResourceID is not null then ‘*’ else ” end),
Installed=(case when css.Status=3 then ‘*’ else ” end),
IsRequired=(case when css.Status=2 then ‘*’ else ” end),
ui.CI_UniqueID as UniqueUpdateID,
ui.InfoURL as InformationURL
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_CICategories_All catall on catall.CI_ID=ui.CI_ID
join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName=’Company’
join v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
left join (
select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
group by atc.CI_ID) cdl on cdl.CI_ID=css.CI_ID
where css.ResourceID = @RscID and css.Status=2 and catinfo2.CategoryInstanceName like ‘Security Updates’
order by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID
*********************************************************
Prompt
Name = MachineName
Sql Query
begin
if (@__filterwildcard = ”)
select distinct Name0 from v_R_System WHERE isnull(Obsolete0,0)<>1 order by Name0
else
select distinct Name0 from v_R_System
WHERE Name0 like @__filterwildcard
and isnull(Obsolete0,0)<>1 order by Name0
end
*********************************************************
2- Create a report called Software Updates Compliance Status by Collection and configure the link to the report created in step 1
Report SQL Query
*********************************************************
SELECT
rs.Netbios_name0 as ‘PC Name’,
case
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then (cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ‘ Security Updates Missing’)
else ‘Good Client’
end as ‘Updates Status’,
rs.User_Name0 as ‘Last Logged On User’,
asite.SMS_Assigned_Sites0 as ‘SMS Site’,
rs.Client_Version0 as ‘Client Version’,
ws.lasthwscan as ‘Last Hardware Scan’,
uss.LastScanPackageLocation as ‘Last Scan Location’,
uss.LastScanPackageVersion as ‘Last Scan Pakage’,
st.StateName as ‘Status’,
Convert(VarChar(10), OS.LastBootUpTime0, 101) ‘Last Boot Date’,
DateDiff(D, OS.LastBootUpTime0, GetDate()) ‘Last Boot (Days)’
from v_ClientCollectionMembers ccm
join v_R_System rs on rs.ResourceID = ccm.ResourceID
left outer join v_UpdateComplianceStatus UCS on UCS.ResourceID = ccm.ResourceID
join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’
left join v_RA_System_SMSAssignedSites asite on asite.ResourceID = ccm.ResourceID
left join v_gs_workstation_status ws on ws.resourceid=rs.resourceid
left join v_UpdateScanStatus uss on ws.resourceid = uss.ResourceID
left join v_StateNames st on st.TopicType = 501 and st.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rs.Client_Version0, ‘4.0’),
1)<‘4’) then 7 else isnull(uss.LastScanState, 0) end)
left join v_Gs_Operating_System OS on ws.resourceid = OS.ResourceID
where
and catinfo2.CategoryInstanceName like ‘Security Updates’
Group By
rs.Netbios_name0,
rs.User_Name0 ,
asite.SMS_Assigned_Sites0 ,
rs.Client_Version0,
ws.lasthwscan,
uss.LastScanPackageLocation,
uss.LastScanPackageVersion ,
st.StateName ,
OS.LastBootUpTime0,
*********************************************************
Prompt
Name = CollID
begin
if (@__filterwildcard = ”)
select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name
else
select CollectionID as CollectionID, Name as CollectionName from v_Collection
WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard
order by Name
end
Knowledge Is Power 