→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
IT_girlpower
Engaged Sweeper
I am new to scripting and very novice when it comes to making custom reports. I was asked to generate a detailed report for each asset in our environment. The details need to contain the following:

Username
Firstseen
Lastseen
IPAddress
Assetname
Assettype
Memory
Processor
IPLocation
OU
Model
Domainrole
OSname

Any help or assistance is appreciated.


1 ACCEPTED SOLUTION
TRC_System_Admi
Engaged Sweeper II
I made this Workstation report last night. Not exactly what you are looking for but it should be enough to get you started.. I'm a novice to....

The below fields will be displayed...

Assetname
IPAddress
Username
Uptime (Days)
Memory (GB)
Max Mem (GB)
Manufacturer
OSname
SP
Windows Key
Processor
Procs
Cores
Mac

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblAssets.Username,
Convert(Decimal(7,1),tblAssets.Uptime / 60 / 60 / 24) As [Uptime (Days)],
Convert(DECIMAL(9,1),tblAssets.Memory / 1024) As [Memory (GB)],
Convert(DECIMAL(3),tblPhysicalMemoryArray.MaxCapacity / 1024 / 1024)
As [Max Mem (GB)],
tblAssetCustom.Manufacturer,
tsysOS.OSname,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblOperatingsystem.SerialNumber As [Windows Key],
tblAssets.Processor,
tblProcessor.NumberOfLogicalProcessors As Procs,
tblProcessor.NumberOfCores As Cores,
tblAssets.Mac
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblPhysicalMemoryArray On tblAssets.AssetID =
tblPhysicalMemoryArray.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.Memory,
tblAssets.AssetName

View solution in original post

4 REPLIES 4
Hemoco
Lansweeper Alumni
Can you please try the following report:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblAssets.Username,
Convert(Decimal(10,5),tblAssets.Uptime / 60 / 60 / 24) As [Uptime (Days)],
Convert(DECIMAL(13,5),tblAssets.Memory / 1024) As [Memory (GB)],
Convert(DECIMAL(7),tblPhysicalMemoryArray.MaxCapacity / 1024 / 1024)
As [Max Mem (GB)],
tblAssetCustom.Manufacturer,
tsysOS.OSname,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblOperatingsystem.SerialNumber As [Windows Key],
tblAssets.Processor,
tblProcessor.NumberOfLogicalProcessors As Procs,
tblProcessor.NumberOfCores As Cores,
tblAssets.Mac
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblPhysicalMemoryArray On tblAssets.AssetID =
tblPhysicalMemoryArray.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.Memory,
tblAssets.AssetName
bwanapc
Engaged Sweeper
The Report look interesting, BUT when I run it I am getting
"Error: Arithmetic overflow error converting numeric to data type numeric."
Any ideas?
IT_girlpower
Engaged Sweeper
Thank you for your help. I appreciate it greatly.
TRC_System_Admi
Engaged Sweeper II
I made this Workstation report last night. Not exactly what you are looking for but it should be enough to get you started.. I'm a novice to....

The below fields will be displayed...

Assetname
IPAddress
Username
Uptime (Days)
Memory (GB)
Max Mem (GB)
Manufacturer
OSname
SP
Windows Key
Processor
Procs
Cores
Mac

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblAssets.Username,
Convert(Decimal(7,1),tblAssets.Uptime / 60 / 60 / 24) As [Uptime (Days)],
Convert(DECIMAL(9,1),tblAssets.Memory / 1024) As [Memory (GB)],
Convert(DECIMAL(3),tblPhysicalMemoryArray.MaxCapacity / 1024 / 1024)
As [Max Mem (GB)],
tblAssetCustom.Manufacturer,
tsysOS.OSname,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblOperatingsystem.SerialNumber As [Windows Key],
tblAssets.Processor,
tblProcessor.NumberOfLogicalProcessors As Procs,
tblProcessor.NumberOfCores As Cores,
tblAssets.Mac
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblPhysicalMemoryArray On tblAssets.AssetID =
tblPhysicalMemoryArray.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.Memory,
tblAssets.AssetName