Solved

SCCM Reports

Posted on 2009-05-05
4
1,331 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now