Video Screencast Help

SQL report need to add model number

Created: 29 Aug 2014 • Updated: 10 Nov 2014 | 6 comments
This issue has been solved. See solution.

Can someone add to this to include the PC model?  I'm not a SQL person!

SELECT dbo.Inv_AeX_AC_Identification.[Name][Host Name]
, dbo.Inv_AeX_AC_Identification.[Os Name]
, dbo.Inv_AeX_AC_Identification.[System Type]
, dbo.Inv_AeX_AC_Identification.[Last Logon User]
, dbo.Inv_AeX_AC_TCPIP.[MAC Address]
, dbo.Inv_AeX_AC_TCPIP.[IP Address]
, dbo.Inv_SW_BIOS_Element.[Version][BIOS Version]

FROM
dbo.Inv_AeX_AC_Identification,
dbo.Inv_AeX_AC_TCPIP,
dbo.Inv_SW_BIOS_Element

WHERE
dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_AeX_AC_TCPIP._ResourceGuid
AND dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_SW_BIOS_Element._ResourceGuid

Operating Systems:

Comments 6 CommentsJump to latest comment

Igor Perevozchikov's picture

Try this query

SELECT dbo.Inv_AeX_AC_Identification.[Name][Host Name]
, dbo.Inv_AeX_AC_Identification.[OS Name]
, dbo.Inv_AeX_AC_Identification.[System Type]
, dbo.Inv_AeX_AC_Identification.[Last Logon User]
, dbo.Inv_AeX_AC_TCPIP.[MAC Address]
, dbo.Inv_AeX_AC_TCPIP.[IP Address]
, dbo.Inv_SW_BIOS_Element.[Version][BIOS Version]
, dbo.vHWComputerSystem.[Model]
, dbo.vHWComputerSystem.[Manufacturer]

FROM
dbo.Inv_AeX_AC_Identification,
dbo.Inv_AeX_AC_TCPIP,
dbo.Inv_SW_BIOS_Element,
dbo.vHWComputerSystem

WHERE
dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_AeX_AC_TCPIP._ResourceGuid
AND dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_SW_BIOS_Element._ResourceGuid
AND dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.vHWComputerSystem._ResourceGuid

  1. Don't forget that, first you can find an answer for your question in Knowledge base
  2. If answer solves your question, then please mark as solution to close a thread
SOLUTION
ianatkin's picture

Marked Igor's post here as the answer as poster seems to have abandoned it.

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which assist you most in resolving your problem, and give a thumbs up to useful articles and downloads

WDRAIN1's picture

Updated query without the duplicates... The Inv_AeX_AC_TCPIP table can have multiple records for a computer and list a computer multiple times. this query uses IP and Mac address from the last basic inventory eliminating duplicates

SELECT

C.NAME AS COMPUTER
,C.[OS Name]
,C.[System Type]
,C.[User] [Lat Logon User]
,c.[MAC Address]
,c.[IP Address]
,bios.Version as [Bios Version]
,hw.Model
,hw.Manufacturer

FROM vComputer C
join Inv_SW_BIOS_Element bios on bios._ResourceGuid = c.Guid
join vHWComputerSystem hw on hw._ResourceGuid = c.Guid

sdmayhew's picture

Igor and Ian

Inv_AeX_AC_TCPIP should never be used in any report ! you will create duplicates in your report 

vcomputer does the sorting for you on ip address and takes most current. 

Depending on your asset maturity you can add in vasset.status 

Altiris user since 2001, Asset Management for 25 years

ianatkin's picture

Hi guys,

In this post the user asked to expand an existing query (that uses Inv_AeX_AC_TCPIP) to include model information. Igor here did exactly as the poster asked. This query would already have presented multiple rows for multiple adapters, and the mulitiple adapter information could conceivably be a deliberate requirement for the poster (we have no idea). 

For general clarity, when a user abandons a post I take a look at the responses and mark up the most *useful* of them, judging that the oringinal poster has not returned as they've gleaned the information they need.

Hope that helps you understand where I'm coming from when I mark up abandoned posts like these.

Kind Regards,
Ian./

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which assist you most in resolving your problem, and give a thumbs up to useful articles and downloads