Solved

SCCM Reports

Posted on 2009-05-05
4
1,343 Views
Last Modified: 2013-11-21
Hey All,

I've been tasked with creating new reports for software updates via SCCM.  The main issue management has with the canned reports is that you have to drill down through the reports to get the information you need (I know manager right?).  I've also tried creating the reports in Crystal Reports, but can't get the joins right for the views.

Question 1
Is it possible to create the reports such as below to have all of the results in one report without drilling down (can you share the sql that will accomplish this)?
   NonCompliant
       Update
           Computer
   Compliant
        Update
            Computer


I know this will be a very long report.

Question 2
Has anyone successfully created the reports using Crystal Reports for software updates reports you can share the columns to join the views on to get similar results?

Any help or point in the right direction is greatly appreciated!
Thanks!
0
Comment
Question by:Uglib
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 24310735
What database?

Crystal can do it but without knowledge of the database, I can't suggest the SQL

mlmcc
0
 
LVL 1

Author Comment

by:Uglib
ID: 24314015
The database is the SCCM database.  There are many views in that database and we are having issues figuring out what views to use to create these reports.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24314617
Do you know what the underlying database engine is?

Will need to find an SCCM expert

mlmcc
0
 
LVL 1

Accepted Solution

by:
drgonzo1967 earned 500 total points
ID: 24368585
Hey Uglib.

I was slightly unclear if you wanted drill downs from your question, but the following code should get you started.  The SQL below will return all Computer Names, Vendors, UpdateClassifications, KB, BID, Title, and weather its Targeted, Installed, or IsRequired.  There is also some additional columns but you will see that soon enough.  Note this is ordered by sys.name0, and css.status.  So the results are going to to be sorted like this:  IsRequired than Installed, per machine.  I know its not exactly what you asked for, but I though this was was a little more intuitive to look at.  Now you can search per machine and not have to jump to different spots in the VERY large report.  You can change it if need be.   =)   Let me know if this is on track or not.  I'll be happy to mod the code if im way off.  Hope it helps!

-gonzo
select 
			SYS.name0 as [Computer Name],
			catinfo.CategoryInstanceName as Vendor,
			catinfo2.CategoryInstanceName as UpdateClassification,
            ui.BulletinID as BulletinID,
            ui.ArticleID as ArticleID,
            LEFT(ui.Title,40) 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),
            Deadline=cdl.Deadline,
            ui.CI_UniqueID as UniqueUpdateID,
			ui.InfoURL as InformationURL
			--ip.IP_Addresses0 as [IP Address] 
from v_UpdateComplianceStatus css
join v_r_system sys on css.resourceid = sys.resourceid
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 = '137'
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
order by sys.name0,css.status

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question