Help with report

[ashley_420]

Hi,

I am very bad at writing SQL queries, I need help to generate a report with following information:-

Asset Name
Asset Type
OS
Manufacturer
Model
Total Memory(in GB
Processor
Disk with capacity in GB
State
Serial Number
Last user
First Seen
NIC
IP ADDRESS
Monitor

Any help is highly appreciated.

Also, I would appreciate any documentation to write effective queries to generate reports.

Thanks

[ashley_420]

Excellent!! Could not have asked more. Thanks a lot RC62N

[RCorbeil · Accepted solution]

Start with your basics. The core of what you're looking for is in tblAssets.

SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
Ceiling(tblAssets.Memory / 1024) As [Memory (GB)],
tblAssets.Processor,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.IPAddress

FROM
tblAssets

Add the make/model/serial details by linking to tblAssetCustom.

SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Ceiling(tblAssets.Memory / 1024) As [Memory (GB)],
tblAssets.Processor,
tblAssetCustom.SerialNumber,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.IPAddress

FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblAssets.AssetID

Pick up the description of the state by linking that against tblState.

SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Ceiling(tblAssets.Memory / 1024) As [Memory (GB)],
tblAssets.Processor,
tblState.StateName,
tblAssetCustom.SerialNumber,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.IPAddress

FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblAssets.AssetID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State

Assuming you're only interested in the active devices, add a condition to filter for only those.

WHERE
tblAssetCustom.State = 1

Pick up the description of the asset type by linking tblAssets against tSysAssetTypes.

SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tSysAssetTypes.AssetTypeName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Ceiling(tblAssets.Memory / 1024) As [Memory (GB)],
tblAssets.Processor,
tblState.StateName,
tblAssetCustom.SerialNumber,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.IPAddress

FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblAssets.AssetID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State
INNER JOIN tSysAssetTypes ON tSysAssetTypes.AssetType = tblAssets.AssetType

WHERE
tblAssetCustom.State = 1

Link tblAssets against tSysOS for basic operating system information.

SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tSysAssetTypes.AssetTypeName,
tSysOS.OSName,
tSysOS.Image AS Icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Ceiling(tblAssets.Memory / 1024) As [Memory (GB)],
tblAssets.Processor,
tblState.StateName,
tblAssetCustom.SerialNumber,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.IPAddress

FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblAssets.AssetID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State
INNER JOIN tSysAssetTypes ON tSysAssetTypes.AssetType = tblAssets.AssetType
INNER JOIN tSysOS ON tSysOS.OSCode = tblAssets.OSCode

WHERE
tblAssetCustom.State = 1

For the hard drive, monitor and NIC, things get a little more interesting because any given computer can potentially have more than one of each.

First, the hard drive. Assuming you're only interested in the primary, we'll filter for that. The drive details are found in tblFloppy.

SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tSysAssetTypes.AssetTypeName,
tSysOS.OSName,
tSysOS.Image AS Icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Ceiling(tblAssets.Memory / 1024) As [Memory (GB)],
tblAssets.Processor,
Ceiling(tblFloppy.Size / 1000 / 1000 / 1000) AS [Hard drive (GB)],
tblState.StateName,
tblAssetCustom.SerialNumber,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.IPAddress

FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblAssets.AssetID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State
INNER JOIN tSysAssetTypes ON tSysAssetTypes.AssetType = tblAssets.AssetType
INNER JOIN tSysOS ON tSysOS.OSCode = tblAssets.OSCode
INNER JOIN tblFloppy ON tblFloppy.AssetID = tblAssets.AssetID

WHERE
tblAssetCustom.State = 1
AND tblFloppy.Name = '\\.\PHYSICALDRIVE0'

That covers the basics of what you're looking for. Adding the monitor and NIC can result in multiple result records per computer (e.g. if a computer has two monitors and two NICs, that's four rows). Personally, because of this, I have separate reports so that I don't get multiple output of the basics. But we're looking at what you asked for, so let's go for the multiples.

Monitor info is recorded in tblMonitor.

SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tSysAssetTypes.AssetTypeName,
tSysOS.OSName,
tSysOS.Image AS Icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Ceiling(tblAssets.Memory / 1024) As [Memory (GB)],
tblAssets.Processor,
Ceiling(tblFloppy.Size / 1000 / 1000 / 1000) AS [Hard drive (GB)],
tblState.StateName,
tblAssetCustom.SerialNumber,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.IPAddress,
tblMonitor.MonitorManufacturer,
tblMonitor.MonitorModel,
tblMonitor.SerialNumber AS MonitorSerial

FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblAssets.AssetID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State
INNER JOIN tSysAssetTypes ON tSysAssetTypes.AssetType = tblAssets.AssetType
INNER JOIN tSysOS ON tSysOS.OSCode = tblAssets.OSCode
INNER JOIN tblFloppy ON tblFloppy.AssetID = tblAssets.AssetID
INNER JOIN tblMonitor ON tblMonitor.AssetID = tblAssets.AssetID

WHERE
tblAssetCustom.State = 1
AND tblFloppy.Name = '\\.\PHYSICALDRIVE0'

And finally, the NIC. Details are recorded in tblNetwork.

SELECT TOP 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tSysAssetTypes.AssetTypeName,
tSysOS.OSName,
tSysOS.Image AS Icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Ceiling(tblAssets.Memory / 1024) As [Memory (GB)],
tblAssets.Processor,
Ceiling(tblFloppy.Size / 1000 / 1000 / 1000) AS [Hard drive (GB)],
tblState.StateName,
tblAssetCustom.SerialNumber,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.IPAddress,
tblNetwork.Description AS NIC,
tblNetwork.IPAddress AS [NIC IP],
tblNetwork.IPSubnet,
tblNetwork.MACAddress,
tblMonitor.MonitorManufacturer,
tblMonitor.MonitorModel,
tblMonitor.SerialNumber AS MonitorSerial

FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblAssets.AssetID
INNER JOIN tblState ON tblState.State = tblAssetCustom.State
INNER JOIN tSysAssetTypes ON tSysAssetTypes.AssetType = tblAssets.AssetType
INNER JOIN tSysOS ON tSysOS.OSCode = tblAssets.OSCode
INNER JOIN tblFloppy ON tblFloppy.AssetID = tblAssets.AssetID
INNER JOIN tblMonitor ON tblMonitor.AssetID = tblAssets.AssetID
INNER JOIN tblNetwork ON tblNetwork.AssetID = tblAssets.AssetID

WHERE
tblAssetCustom.State = 1
AND tblFloppy.Name = '\\.\PHYSICALDRIVE0'
AND tblNetwork.IPEnabled = 1