Solved

SCCM Reports

Posted on 2009-05-05
4
1,335 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
  • 2
4 Comments
 
LVL 100

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 100

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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