Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SCCM Reports

Posted on 2009-05-05
4
Medium Priority
?
1,351 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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 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