SQL View Request

[itkai]

I need to generate an SQL View that contains the following information from the lansweeper database, and I'm hoping that someone who know's the database map better than I do can help.

I need to be able to grab:

Vendor
Model
OS Caption
Identifying Number/Service Tag
MAC Address of the primary network adapter
Last Logged on user

If anyone can be of assistance it would be very helpful.

Thanks,
GaryO

[itkai]

Awesome! Thanks!

[Hemoco]

Please try the following query:

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, tblComputers.Domain, tblComputerSystemProduct.Vendor, tblComputerSystemProduct.Name, tblOperatingsystem.Caption, tblComputerSystemProduct.IdentifyingNumber, tblNetwork.Description As [Network Card], tblNetwork.IPAddress, tblNetwork.MACaddress, tblCPlogoninfo.Username As [Last Logged On], Max(tblCPlogoninfo.logontime) As [Last Logon Date] From tblComputers Inner Join tblNetwork On tblComputers.Computername = tblNetwork.Computername Left Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCPlogoninfo On tblComputers.Computername = tblCPlogoninfo.Computername Inner Join web40ActiveComputers On web40ActiveComputers.Computername = tblComputers.Computername Group By tblComputers.Computername, tblComputers.ComputerUnique, tblComputers.Domain, tblComputerSystemProduct.Vendor, tblComputerSystemProduct.Name, tblOperatingsystem.Caption, tblComputerSystemProduct.IdentifyingNumber, tblNetwork.Description, tblNetwork.IPAddress, tblNetwork.MACaddress, tblCPlogoninfo.Username, tblNetwork.IPEnabled Having tblNetwork.IPEnabled = '1'