Solved

SCCM Reports

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to script out data export 36 63
Access Join Query 12 48
Share and Advanced Sharing permissions 8 57
mySQL Syntax 7 9
My previous article  (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Windows_Server_2008/A_4466-A-beginners-guide-to-installing-SCCM2007-on-Windows-2008-R2-Server.html)detailed one possible method to get SCCM 2007 installed an…
Have you considered what group policies are backwards and forwards compatible? Windows Active Directory servers and clients use group policy templates to deploy sets of policies within your domain. But, there is a catch to deploying policies. The…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 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

12 Experts available now in Live!

Get 1:1 Help Now