Sign in
 
Home2012Previous VersionsLibraryForumsGallery
System Center TechCenter >
> 
> 
Custom SCCM Report - SQL Query question...

Answered Custom SCCM Report - SQL Query question...

  • Sunday, January 16, 2011 4:33 AM
     
     
    Sign In to Vote
    0

    Greetings!

    I've been successful in creating functional simple Reports in SCCM 2007 R1 (getting items from single views i.e. TCP/IP info).

    I'm now looking to create more complex Reports, such as one that lists the following info when a computer name is input:    

    NetBios Name and Domain (or server FDQN)

    Operating System version

    Service Pack

    Install date

    Last boot up time

    Server Model

    Description field of the NIC 

    IP Addr

    Subnet Mask

    Default IP Gateway

    DNSServerSearchOrder

    WINSPrimaryServer

    WINSSecondaryServer

    System ROM info – “SMBIOS BIOS Version” + “Release Date”

    Server serial # - “Serial Number”

    SCSI controller "Name" and firmware - WMI “HPQ\HPSA_ArrayController”

    HP Proliant Support Pack Version – HP Insight Server Agents >> “File version”

     

    Obviously, these bits are in various views w/in the DB.

     

    I'm not clear on how to use JOINs, FROMs, etc (inner, outer, multiples, etc) to get these pieces of info and stitch together a single report.

     

    Thanks in advance!

     

    Cheers!

     

    Hilde

     

    Addendum:

    Here is the SQL statement I'm working on (the Report runs with success but returns no data)

    SELECT
    a.ResourceID as [ResourceID],
    a.Name0 as [System Name],
    a.Caption0 as [Operating System],
    a.CSDVersion0 as [Service Pack],
    a.InstallDate0 as [OS Install Date],
    a.LastBootUpTime0 as [Last Boot Up],
    b.Version0 as [BIOS Version],
    b.ReleaseDate0 as [BIOS Date],
    c.IPAddress0 as [IP Address],
    c.IPSubnet0 as [Subnet Mask],
    c.DefaultIPGateway0 as [Default Gateway],
    c.DNSServerSearchOrder0 as [DNS Servers],
    c.Description0 as [NIC Caption],
    c.WINSPrimaryServer0 as [PriWINS],
    c.WINSSecondaryServer0 as [SecWINS]
    FROM
    v_GS_OPERATING_SYSTEM a,
    v_GS_PC_BIOS b,
    v_GS_NETWORK_ADAPTER_CONFIGUR c
    WHERE
    a.Name0 LIKE @variable

     

All Replies

  • Sunday, January 16, 2011 7:23 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi,

    Here is a similar report that might help you on the way - http://blog.coretech.dk/confmgr07/config-mgr-inventory-and-reporting/creating-configuration-manager-2007-reports-part-iv-using-prompt/

    Kent Agerlund | My blogs: http://blog.coretech.dk/author/kea/ and http://scug.dk/ | Twitter @Agerlund | Linkedin: /kentagerlund
     
  • Sunday, January 16, 2011 4:33 PM
     
     
    Sign In to Vote
    0

    Thanks Kent for teaching me to fish rather than throwing me one!

    Using your blog posts and some other web search results, I was able to use SQL Mgmt Studio (for SQL 2005, though) and some trial/error to create the following SQL Statement which produces the Report I was looking for.  I feel much more informed about SQL JOINs, SQL Mgmt Studio, etc.

    SQL Statement:

    SELECT  dbo.v_GS_SYSTEM.Name0 AS [System Name],
     dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
     dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack],
            dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS [Original OS Install Date],
     dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Boot Time],
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.Description0 AS [NIC Description],
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 AS [IP Address],
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.IPSubnet0 AS [Subnet Mask],  
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0 AS [Gateway],
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.DNSServerSearchOrder0 AS [DNS Servers],
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.WINSPrimaryServer0 AS [Pri WINS],
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.WINSSecondaryServer0 AS [Sec WINS],
            dbo.v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [System ROM Version], dbo.v_GS_PC_BIOS.ReleaseDate0 AS [System ROM Date]
    FROM    dbo.v_GS_NETWORK_ADAPTER_CONFIGUR INNER JOIN
            dbo.v_GS_OPERATING_SYSTEM ON
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
            dbo.v_GS_PC_BIOS ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_GS_PC_BIOS.ResourceID INNER JOIN
            dbo.v_GS_SYSTEM ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_GS_SYSTEM.ResourceID
    where  v_GS_SYSTEM.Name0 LIKE @variable and v_GS_NETWORK_ADAPTER_CONFIGUR.IPaddress0 is Not Null

    I'm not able to post an edited screen-shot of the Rept, but by looking at the SQL Statement, you can see the alias names I gave the columns, as well as the order of them.  SUPERB!!

    What's next??  :)

    Hilde

     

     

     
  • Monday, January 17, 2011 3:36 AM
     
     
    Sign In to Vote
    0

    Ok, I'm back.  Trying to add in the HP Proliant Support Pack version (via the Foundations Agent version which is in Add/Remove Progs) with no luck.  Anyone got any insight.

    SELECT  dbo.v_GS_SYSTEM.Name0 AS [System Name],
     dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
     dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack],
            dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS [Original OS Install Date],
     dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Boot Time],
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.Description0 AS [NIC Description],
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 AS [IP Address],
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.IPSubnet0 AS [Subnet Mask],  
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0 AS [Gateway],
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.DNSServerSearchOrder0 AS [DNS Servers],
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.WINSPrimaryServer0 AS [Pri WINS],
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.WINSSecondaryServer0 AS [Sec WINS],
     dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
            dbo.v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [System ROM Version],
     dbo.v_GS_PC_BIOS.ReleaseDate0 AS [System ROM Date],
     

    dbo.v_GS_ADD_REMOVE_PROGRAMS.Version0 AS [PSP Version]

    FROM    dbo.v_GS_NETWORK_ADAPTER_CONFIGUR
     INNER JOIN
            dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
     INNER JOIN
            dbo.v_GS_PC_BIOS ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_GS_PC_BIOS.ResourceID
     INNER JOIN
            dbo.v_GS_SYSTEM ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_GS_SYSTEM.ResourceID
     INNER JOIN
     dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
     INNER JOIN
     

    dbo.v_GS_ADD_REMOVE_PROGRAMS on dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID

    where  v_GS_SYSTEM.Name0 LIKE @variable and v_GS_NETWORK_ADAPTER_CONFIGUR.IPaddress0 is Not Null and

    dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'HP Insight Management Agents'

    Any advice on how to add in a few of the Add/Remove Progs?

    Also, going to attempt to add some things to the MOFs for the HP-specific WMI stuff and then grab it w/ this report:

    HP Array Controller Root\HPQ\HPSA_ArrayController.elementName
    HP Array ROM Root\HPQ\HPSA_FIRMWARE.VersionString
    HP ILO ROM Root\HPQ\HP_MPFirmware.VersionString
    HP Drive ROM Root\HPQ\HPSA_DiskDriveFirmware.VersionString

    Intrigued...

     

    Hilde

     
  • Friday, April 01, 2011 8:25 PM
     
     
    Sign In to Vote
    0

    Thanks for posting this - it is exactly what I need. (not the HP part, but the post just before that, with G/W, dns, etc. It baffles me that there weren't more 'built-in' network/asset reports like this already in SCCM 2007. Admittedly, I also don't completely understand 'inner' vs. outer joins and so forth, but that's for another SQL class. :-)

    woah! What a minute - I don't even HAVE these columns to choose from:

            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.DNSServerSearchOrder0 AS [DNS Servers],
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.WINSPrimaryServer0 AS [Pri WINS],
            dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.WINSSecondaryServer0 AS [Sec WINS],

    What gives? Is this a "mof extension" that I have to do, in order to see these as 'selectable' columns in my report SQL interface in SCCM?

    I'm rather confused, as I [thought] these [would be] normal columns that would be part of  the v_GS_NETWORK_ADAPTER_CONFIGUR view???

    Also, what's the diff between v_GS vs. v_HS? They seem to have a lot of the same stuff.

    Thanks ahead of time!

     

     
  • Friday, April 01, 2011 8:48 PM
     
     
    Sign In to Vote
    0

    never mind. I found it: set the report value to TRUE for those values in the sms_def.mof file.

    Thanks!

     
  • Wednesday, July 27, 2011 2:09 AM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Also, what's the diff between v_GS vs. v_HS? They seem to have a lot of the same stuff.

     

    GS = Current Data

    HS = Historical Data

    http://www.enhansoft.com/
     
  • Wednesday, July 27, 2011 2:23 AM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Any advice on how to add in a few of the Add/Remove Progs?

     


    Yes put bracket around the ARP section and use “OR” between each application.

                    NAC.IPaddress0 is Not Null

                    and (ARP.DisplayName0 = 'HP Insight Management Agents' or ARP.DisplayName0 = 'another app' )

    http://www.enhansoft.com/
     
  • Tuesday, July 10, 2012 6:30 AM
     
     
    Sign In to Vote
    0

    Hello

    Iam trying to create a custom SQL query to retrieve monitor details like make, model type and serial number using sccm 2007 R3. I tried lot of options but Iam not able to figure out how to show them in web reports. Can any one help me in this.

    Thanks in advance

    Vamshi

     
  • Thursday, July 12, 2012 4:15 AM
     
     
    Sign In to Vote
    0

    if you want to retrieve monitor details, you can try this;

    http://bowden-it.net/wordpress/?p=45

    http://bowden-it.net/wordpress/?p=48

    Simon Brouillard

     
  • Monday, July 16, 2012 12:07 PM
     
     
    Sign In to Vote
    0

    Thanks for your reply sbrouillard. I tried the above steps but iam not able to pull any information for the serial number Iam able to get the monitor make details. Is there any other way for doing this.

    Sorry for the delay in the reply.

    Thanks,

    Vamshi