Search the Community

hi is there a way to find/display specific certificate that is on client side is there and rdl for the sccm report server ? can i see the certificate in resource explorer ? roni

Good afternoon, Is there a built-in Alert or Report (or one that can be made simply enough) to send an email (or notify in the console) that the Distribution Point certificate is expiring within 30 days? Is this possible? Thanks! Jesse

Hi all, I've tried and failed miserably! to work out how to do this using the Report Builder. Could anyone possibly help with creating an SQL query to create a deployment report which includes the Administrative Category? Example: Application Name, Application Administrative Category, Target Collection, Success, In Progress, Fail, Requirements not met, Unknown. Would really appreciate any assistance. Thanks

Did you know that Enhansoft is giving away a free report set on Software Update Health? Check out the blog about it. https://www.enhansoft.com/blog/software-update-health-dashboard-set-special-microsoft-ignite-giveaway They give away something every month too! https://giveaway.enhansoft.com/ Keep in mind that I do work for them too.

Hello, I'm looking on how to create a custom report based on the default report of 'Compliance 1 - Overall Compliance' however I'm looking to just select the update group and then have predefined collections to report against, something like this: Collection Compliance (%) Not Compliant (%) Compliance Unknown 4 69.23% 5 80.00% 6 95.00% 7 86.96% 8 95.00% 10 95.24% 11 91.30% 12 87.50% 13 82.61% 14 90.91% 15 81.25% 16 76.19% 17 70.00% 18 55.00% 19 77.78% 20 65.00% 21 76.19% 22 82.35% 23 87.50% 24 80.00% 25 85.71% 26 78.57% 27 86.69% 28 69.23% 29 72.73% 30 38.46% 31 30.77% any ideas how I can achieve this? I'm not familiar with SQL reporting unfortunately.. :(

Don’t miss out on a Free report giveaway for ConfigMgr! https://www.enhansoft.com/blog/asset-intelligence-computer-details-is-june-2017s-free-configmgr-report The Asset Intelligence Computer Details report is a brand new report! This report is designed to fill the gap created by ConfigMgr’s built-in AI reports. We heard that you wanted to see both the normalized and true product name software titles in one report, so you’ve got it! If you’re asking why you would need this information the answer is simple. Have you ever tried to determine who has SQL Server 2016 Management Studio (SSMS) only to discover that the normalized name is Microsoft SQL Server 2016 (all components) which not only includes SSMS, but also SQL Server Configuration Manager, SQL Server Data Tools, etc.? It’s impossible to know from the built-in AI reports who has SSMS without writing your own query, so we saved you the trouble! The Asset Intelligence Computer Details report will also display each title’s software category and software family. This information will help you better understand what each product does. For any software items with no date, the result 1980-02-14 will be displayed.

Hello, How can I modify the report Software 06A to return the ACTUAL version? Basically, I want to get an overall view of how many computers have (Flash in this case) version 25.0.0.148 installed or 25.0.0.171 (for example). Can someone help me with this? Or Is there another existing report that will do this for me?

I have a working SQL query courtesy of Eswar Koneti (https://gallery.technet.microsoft.com/office/SCCM-Configmgr-2012-SSRS-c482cca2/). Due to being new to SQL/SSRS custom report building, I am struggling with trying to limit the scope of these results to a specific collection. What I would like to do is add a statement in the query to limit the results to a specific collection ID(s). I am not looking to make a drop down of collections in the report. Here is what I have so far: SELECT COUNT (Total.Name0) AS 'Total Clients', COUNT (office2003.Name0) AS 'Office 2003', COUNT (Office2007.Name0) AS 'Office 2007' , COUNT (Office2010.Name0) AS 'Office 2010', COUNT (Office2013.Name0) AS 'Office 2013', COUNT (Office2016.Name0) AS 'Office 2016' FROM ( SELECT vr.Name0 FROM v_R_System vr WHERE vr.Client0=1 AND vr.Operating_System_Name_and0 NOT LIKE '%server%') AS Total LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey = arp.machineid WHERE(displayname00 ='Microsoft Office Basic Edition 2003' OR displayname00 ='Microsoft Office Excel 2003' OR displayname00 ='Microsoft Office Outlook 2003' OR displayname00='Microsoft Office Personal Edition 2003' OR displayname00 ='Microsoft Office Professional Edition 2003' OR displayname00='Microsoft Office Small Business Edition 2003' OR displayname00='Microsoft Office Standard Edition 2003') AND client0 = 1) AS office2003 ON Total.Name0 =office2003.Name0 LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey =arp.machineid WHERE(displayname00 LIKE 'Microsoft Office Personal 2007' OR displayname00 LIKE 'Microsoft Office Professional 2007' OR displayname00 LIKE 'Microsoft Office Professional 2007 Trial' OR displayname00 LIKE 'Microsoft Office Professional Hybrid 2007' OR displayname00 LIKE 'Microsoft Office Professional Plus 2007' OR displayname00 LIKE 'Microsoft Office Professional Plus 2007 (Beta)' OR displayname00 LIKE 'Microsoft Office Standard 2007' OR displayname00 LIKE 'Microsoft Office Standard 2007 Trial' OR displayname00 LIKE 'Microsoft Office Ultimate 2007' OR displayname00 LIKE 'Microsoft Office Enterprise 2007' OR displayname00 LIKE 'Microsoft Office Ultimate 2007' OR displayname00 LIKE 'Microsoft Office Ultimate 2007' ) AND sys.client0 = 1) AS Office2007 ON Total.Name0 =office2007.Name0 LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey =arp.machineid WHERE(displayname00 LIKE 'Microsoft Office 2010' OR displayname00 LIKE 'Microsoft Office Professional Plus 2010' OR displayname00 LIKE 'Microsoft Office Standard 2010' OR displayname00 LIKE 'Microsoft Office Professional 2010' OR displayname00 LIKE 'Microsoft Office Home and Student 2010' OR displayname00 LIKE 'Microsoft Office Home and Business 2010' OR displayname00 LIKE 'Microsoft Office Professional Plus 2010 (Beta)' OR displayname00 LIKE 'Microsoft Office Starter 2010 - English') AND sys.client0 = 1) AS office2010 ON Total.Name0 =office2010.Name0 LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey=arp.machineid WHERE(displayname00 LIKE 'Microsoft Office 2013' OR displayname00 LIKE 'Microsoft Office Professional Plus 2013' OR displayname00 LIKE 'Microsoft Office Standard 2013' OR displayname00 LIKE 'Microsoft Office Professional 2013' OR displayname00 LIKE 'Microsoft Office Home and Student 2013' OR displayname00 LIKE 'Microsoft Office Home and Business 2013' OR displayname00 LIKE 'Microsoft Office Professional Plus 2013 (Beta)' OR displayname00 LIKE 'Microsoft Office Starter 2013 - English') AND sys.client0 = 1) AS office2013 ON Total.Name0 =office2013.Name0 LEFT JOIN ( SELECT DISTINCT sys.Name0 , displayname00 AS OfficeEdition , version00 AS OfficeVersion FROM system_disc sys LEFT JOIN add_remove_programs_data arp ON sys.itemkey=arp.machineid WHERE(displayname00 LIKE 'Microsoft Office 2013' OR displayname00 LIKE 'Microsoft Office Professional Plus 2016' OR displayname00 LIKE 'Microsoft Office Standard 2016' OR displayname00 LIKE 'Microsoft Office Professional 2016' OR displayname00 LIKE 'Microsoft Office Home and Student 2016' OR displayname00 LIKE 'Microsoft Office Home and Business 2016' OR displayname00 LIKE 'Microsoft Office Professional Plus 2016 (Beta)' OR displayname00 LIKE 'Microsoft Office Starter 2016 - English') AND sys.client0 = 1) AS office2016 ON Total.Name0 =office2016.Name0 Can someone with some SQL skills please help me? I have come up with a query that will list all the members of a collection based on collection ID, but I can't seem to figure out how to incorporate it.

Hi, the build in report "User device affinity associations per collection" doesn´t list the "OSD Defined" relationships. We are using powershell to import computers and set the UDA relationship with the following command: Invoke-WmiMethod -Namespace root/SMS/site_$($SiteCode) -Class SMS_UserMachineRelationship -Name CreateRelationship -ArgumentList @($WksResourceID, 6, 1, $User) -ComputerName $SiteServer The console shows the relationship and software deployment works well. If we change the parameter "source" from "6" (OSD Defined) to "2" (Administrator Defined) the report lists the relationship correctly. Any ideas? Best regards

Hi Everyone, I wanted to let you know that this month's free report giveaway is Patch Compliance Progression by Collection For each PC within a collection, the Patch Compliance Progression by Collection report will provide you with a count of missing software updates (patches) and the last hardware inventory date. The deployed state report parameter allows you to select the deployed state of the software update. The deployed states are: Yes – The effected software update is deployed within your environment No – The effected software update is NOT deployed within your environment Both The status count parameter allows you to select whether or not expired software updates are included in the status count. In ConfigMgr, software updates that are expired are still listed. When complying with standards such as the Payment Card Industry Data Security Standard (PCI DSS), expired software updates should always be excluded from status counts. Yes – Expired software updates are included within the status count No – Expired software updates are NOT included within status count The classification report parameter enables you to select which software update classification to display within the report. The update classifications, listed in order of severity, are: Critical Updates, Security Updates, Definition Updates, Service Packs, Update Roll-ups, Updates, Tools, and Feature Packs. Click here to read more about update classifications. To learn more about it see our website. http://www.enhansoft.com/resources#current-monthly-report

NOTE: Cross posted to Technet Forums I have used the Dell Client Integration Pack to import warranty data into SCCM. I imported their Warranty report which gives me information on all Dell systems in the database. How can I go about limiting this report to return data only from a specific collection? SELECT v_GS_COMPUTER_SYSTEM.Name0 AS Name, DellWarrantyInformation.Service_Tag AS [serial Number], DellWarrantyInformation.Build, DellWarrantyInformation.Region, DellWarrantyInformation.LOB AS [Line of Business], DellWarrantyInformation.System_Model AS [system Model], CONVERT(varchar, DellWarrantyInformation.Ship_Date, 101) AS [Dell Ship Date], DellWarrantyInformation.Service_Level_Code AS [service Level Code], DellWarrantyInformation.Service_Level_Description AS [service Level Description], DellWarrantyInformation.Provider, CONVERT(varchar, DellWarrantyInformation.Start_Date, 101) AS [Warranty Start Date], CONVERT(varchar, DellWarrantyInformation.End_Date, 101) AS [Warranty End Date], DellWarrantyInformation.Days_Left AS [Warranty Days Left], DellWarrantyInformation.Entitlement_Type, CONVERT(varchar, v_GS_WORKSTATION_STATUS.LastHWScan, 101) AS [Last Hardware Scan], v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 FROM DellWarrantyInformation LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE ON DellWarrantyInformation.Service_Tag = v_GS_SYSTEM_ENCLOSURE.SerialNumber0 LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN v_GS_WORKSTATION_STATUS ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID WHERE (DellWarrantyInformation.Entitlement_Type <> '')

Can someone help me with a report that shows the individual patch sizes for a deployment? I can't seem to find any view that shows size. Thanks.

Hi, I'm looking to create a Report in SCCM 2012 R2. Trying to find a way to display all successfully installed computers for a specific domain, at a set date (ex. 01.01 to 30.03). It needs to exclude all failed task sequences and only show a MAC-address once (preferrably the active one). Also if possible, it would be great to show detail like: Computername, Domainname, MAC-address, Last logged on user, last logon date, Installdate / createdate Conclusion: the idea is to create a PDF of quarterly OS deployed computers. Anyone who could help me out here? Really appreciate the help

I'm trying to generate a custom report in SCCM 2012 R2 but am having a bit of trouble getting things working. The report needs to show all Security Updates that have been Deployed but are still showing as Required on servers. I would like it to have a separate entry for each applicable security update and the server that it is applicable to. When I've been searching for how to do this, a lot of people have said that the report will be so big that it won't be usable, but this is for a vulnerability remediation tracking document and it's exactly the format that we need. For the most part, our environment is patched up to date, so the report should have about 1000 lines. Here is an example of what I'm looking for from a formatting perspective.

Good Morning All, I am no wizard with SQL or even SCCM reporting, I have dabbled but that's about it... I am looking for a report which will tell me which applications, and which programs do not have any deployments? Can anyone help? Thanks John Rickard

I've looked around a bit but have yet to come up with a good query or report to show the number of physical processors on our servers. Can anyone help?

I'm Having a little trouble writing a report. My goal is to report back the following fields: Last Time the Hardware inventory was run, Computer Name, Asset Tag (Bios Serial Number), IP Address, Operating System, OS Service Pack level, time the computer was Last Rebooted, Last Logged in User, Domain Name, OU, and AD Site. I've managed to gather most everything but I'm getting duplicate entries. My report returns over 13000 rows when I only have 6000 clients. Much of this is coming from the IP Address field since if the computer has an IPv6 address it returns another row. Below is my query and I appreciate any help anyone can give since I am very much a beginner in SQL. SELECT DISTINCT v_GS_Workstation_Status.LastHWScan, v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_RA_System_IPAddresses.IP_Addresses0, v_GS_Operating_System.Caption0 as C054, v_GS_Operating_System.Version0, v_GS_Operating_System.LastBootUpTime0, v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0, v_R_System.AD_Site_Name0 FROM v_R_System JOIN v_GS_Workstation_Status on v_R_System.ResourceID = v_GS_Workstation_Status.ResourceID JOIN v_RA_System_IPAddresses on v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID JOIN v_GS_PC_BIOS on v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN v_GS_OPERATING_SYSTEM on v_R_System.ResourceID = v_GS_Operating_System.ResourceID ORDER BY v_GS_Workstation_Status.LastHWScan, v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_RA_System_IPAddresses.IP_Addresses0, v_GS_Operating_System.Caption0, v_GS_Operating_System.Version0, v_GS_Operating_System.LastBootUpTime0, v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0, v_R_System.AD_Site_Name0

hello I want to give you my appologize for my horrible english ( i'm french) I need help for a report on sccm2012 i want to create a report for view the state of application deployement for one user (success, error,requierement not met, unknown) could you help me please ?

I need a report showing a list of SCCM Collection and the number of computers in each Collection within our environment. Does anyone have a report or query like that I can use? I can't find a standard report like that in SCCM 2012 SP1.

Guys, I'm trying to run a report to tell which computers have Dropbox installed on them, so naturally I use the "Computer with a specific product". I run it and the report is generated, but it includes computers that don't have Dropbox anymore or that might have had it installed at some point. I wanted to just get a list of the ones that currently have it installed. Next I tried "Computers with specific software registered in Add Remove Programs" but that returns nothing, just blank. I tried %dropbox% in the title and such but t no avail. Any insight would be greatly appreciated. Thanks, -Nazih

Afternoon Everyone, I am trying to create a report in CM 2012 R2 which will report the OUs which machines are a member of. I want to limit this so that I can select a specific collection and it will report that. I have found various things online and followed quite a few, but I am in no way a SQL master Would it be possible for someone to have a gander at the attached and see what I am going wrong with - I think it is something to do with altering the Getting_the_ous query to encompass the collectionID. When I run it at the moment I can pick a collection and I can then click view report, this runs but doesnt limit it to a collection and hows the everything! Thanks John Getting the OUs SELECT DISTINCT dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_R_System.User_Name0, MAX(dbo.v_RA_System_SystemOUName.System_OU_Name0) AS Expr1, dbo.v_GS_COMPUTER_SYSTEM.Description0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_PC_BIOS.SerialNumber0 FROM dbo.v_GS_COMPUTER_SYSTEM INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_RA_System_SystemOUName ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_RA_System_SystemOUName.ResourceID INNER JOIN dbo.v_GS_PC_BIOS ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_GS_PC_BIOS.ResourceID GROUP BY dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_R_System.User_Name0, dbo.v_GS_COMPUTER_SYSTEM.Description0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_PC_BIOS.SerialNumber0 Getting the collection list Select * from V_collection

I have a collection based on an AD OU of users. I need to report on which computers those users are logging into. I have found a lot of reports that look at computer collections, but non that query user collections. Thanks

Hello, i use the following query to get a license report but i´m not quite happy with the result and don´t know how to edit it. declare @ChannelCode varchar(32) select @ChannelCode = 'All' declare @ProductName nvarchar(255) select ProductName, FamilyName, VersionCode, coll.Name, MAX(VersionSequence) as VersionSequence, MAX(ChannelCode) as ChannelCode, count(distinct ilp.ResourceID) as InventoryCount from dbo.vInventoriedLicensedProduct ilp left join v_FullCollectionMembership fcm on ilp.ResourceID = fcm.ResourceID left join v_Collection coll on fcm.CollectionID = coll.CollectionID where (ilp.ProductName = @ProductName OR @ProductName IS NULL) and (@ChannelCode is NULL or @ChannelCode = N'All' OR ilp.ChannelCode = @ChannelCode) group by ProductName, FamilyName, VersionCode, coll.Name The result is the following: The problem with the result is that every collection is listed in the column Name. But i only want to choose some of them. How can i edit the query to get a result with only three collections ? The best result would be the following, but for this i think i have to modify the whole query. Any experts who can help me ?

Hey Guys, I am in the need of a query , report or script that will generate a list of all of the applications that use a specific version of Java. I've used report Software 01A and as it does give me all of the versions of software I need to know what applications actually have a dependency on these versions of Java. My fellow SCCM admins out there I know this is a unique request, but if anyone has any ideas I'm all ears. Thanks!

Good morning, I have printers set for inventory on my SCCM 2012 environment. I need to run a query on all USB/LPT connected printers in the district. Can someone please help me with a query/report or something that I can run that will give me a list of all printer models that have been collected from all my clients? This will help me in collecting drivers while we go forward with our district-wide windows 7 migrations. Many thanks!