_ap_ufes{"success":true,"siteUrl":"eskonr.com","urls":{"Home":"http://eskonr.com","Category":"http://eskonr.com/category/net-framework-4-0/","Archive":"http://eskonr.com/2014/11/","Post":"http://eskonr.com/2014/11/sccm-configmgr-2012-software-update-scan-failed-onsearchcomplete-failed-to-end-search-job-error-0xc8000222/","Page":"http://eskonr.com/orchestrator-scorch-guides/","Wpcf7_contact_form":"http://eskonr.com/?post_type=wpcf7_contact_form&p=1610","Safecss":"http://eskonr.com/?safecss=safecss"}}_ap_ufee ESwar KONeti » Last Hardware Scan

ESwar KONeti

All about Configmgr and its Connected Objects….

  • About Author
      View eswar koneti's LinkedIn profile
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 252 other subscribers

  • Archives

  • Meta

  • #Configmgr Tweets

  • Copyright!

    All the blog posts in this website are owned by Eswar Koneti and may not be reused in any mode without prior approval of Eswar Koneti. You may quote one paragraph from the blog posts if you link to the original blog post.
    Happy Reading!

  • Like us on FaceBook

  • Awards


SCCM report Computers with more than 1 partition

Posted by Eswar Koneti on 10th September 2012

Here is nice report to identify computers which are with more than one partition installed on to them excludes external drives.

select CS.Name0 as [Computer name],
       MAX(OU.System_OU_Name0)as [OU],
       CS.UserName0 as [Last loggedon User],
       FCM.Sitecode ,
       OS.Caption0 AS [OS],
       OS.InstallDate0 [OS Installed Date],
       WS.LastHWScan [Recent Reported to sccm]
    
from  v_GS_COMPUTER_SYSTEM CS,
      v_FullCollectionMembership FCM ,
      v_GS_OPERATING_SYSTEM OS,
      v_RA_System_SystemOUName OU,
      v_GS_WORKSTATION_STATUS WS
where  CS.ResourceID=FCM.ResourceID and
CS.ResourceID=OS.ResourceID and
CS.ResourceID=OU.ResourceID and
CS.ResourceID=WS.ResourceID and OU.ResourceID in
(select LD.ResourceID from v_GS_LOGICAL_DISK LD where LD.Description0 not like '%ROM%' and
LD.Description0 not like '%floppy%' and LD.SystemName0 not like 'null'
group by LD.ResourceID having COUNT(ld.name0)>1) and
OS.Caption0 not like '%Server%'
group by CS.Name0,
         CS.UserName0,
         FCM.Sitecode,
         OS.Caption0,
         OS.InstallDate0,
         WS.LastHWScan

     Customize if you require more information.    

Tags: , , , , , , , ,
Posted in SCCM 2007, SCCM Reports, SQL Quiries | 2 Comments »

SCCM(configMgr) report for computer Asset information Serialnumber, Manufacturer,Model,Processor,IP Address,Hardaware Scan

Posted by Eswar Koneti on 22nd December 2011

Long back i posted report  http://eskonr.com/2009/12/sccm-report-for-computers-asset-information-including-sn-number-and-model-name/ to get computer information like computer name,Serial number,Processor,Physical memory,service Pack,computer model etc

In this post ,i have modified the report little bit to add more fields to it that gives you lot more information about the computer . It is  very simple and straigh forward report that just use Where class

what columns do you get after running this report ? Name,Serialnumber, Manufacturer,Model,Processor,Memory (KBytes),Size (MBytes),MAC Adress,IP Adress,AD Site,Last user logged in,Operating System,Service Pack,Creationdate in SMS,Last Hardware Scan

SELECT
A.Name0,
MAX (B.SerialNumber0) AS 'Serialnumber',
A.Manufacturer0,
A.Model0, C.Name0 AS 'Processor',
D.TotalPhysicalMemory0 AS 'Memory (KBytes)',
MAX ( E.Size0 ) AS 'Size (MBytes)',
MAX (F.MACAddress0) AS 'MAC Adress',
MAX (F.IPAddress0) AS 'IP Adress',
G.AD_Site_Name0 AS 'AD Site',
MAX (A.UserName0) AS 'Last user logged in',
H.Caption0 AS 'Operating System',
H.CSDVersion0 AS 'Service Pack',
G.Creation_Date0 AS 'Creationdate in SMS',
I.LastHWScan

FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_R_System G,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I

WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID

GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan

You can also other custom values from SQL Views ,please take a look Creating Custom Reports By Using Configuration Manager 2007 SQL Views http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22052

 

Tags: , , , , , , , , , , , , , , , , , , , , , , ,
Posted in SCCM 2007, SCCM Reports, SQL Quiries | 12 Comments »