Building "Basic" Report User Assets

[mhammond]

I am building a basic report for my manager. It has to have the following data: Computer Type, Location, Domain, IP Address, Serial Number, Operating System, Make, Model, Processor, Memory/RAM, Hard Drive Size, AntiVirus, Warranty Expiration Date, Uptime.

Below is the code:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
TsysChassisTypes.ChassisName As [Computer Type],
tsysIPLocations.IPLocation As Location,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Serialnumber As [Serial Number],
tsysOS.OSname,
tblSystemEnclosure.Manufacturer As [PC Make],
tblAssetCustom.Model As [PC Model],
tblAssets.Processor,
tblAssets.Memory,
tblAntivirus.DisplayName,
tblDiskdrives.Volumename As [Hard Drive Name],
tblDiskdrives.Size As [Hard Drive Size],
tblAssetCustom.Warrantydate,
tblAssets.Uptime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where TsysChassisTypes.ChassisName Not Like 'Other' And
tblAntivirus.DisplayName Not Like 'Windows%' And
tblDiskdrives.Volumename Is Not Null And tblDiskdrives.Volumename Not Like
'Audio%' And tblDiskdrives.Volumename Not Like '%Recovery' And
tblDiskdrives.Volumename Not Like '%USB%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

- - - - - - - -
As you can see, I've been able to meet the criteria of the report specs in its basic form. However, I have a couple of hitches that need ironed out and hopefully someone could assist.

So, in order:

1. For "Computer Type", we have Surface Tablets that show up as laptops. I would like to figure out if either A- there's a way to tell LANSweeper that all Surfaces are tablets without messing with report coding (it would just show up as a "Tablet" under Computer Type), or B- somehow code the report that whenever it sees an asset as "SUR" that it automatically generates the Computer Type as a Tablet

2. For "Hard Drive Size", my manager is only looking for the size and does not want the Volume Name included. I would like to be able to hide that column in the report automatically if possible. Leaving it in will allow me to better filter out unnecessary 'volumes' like USB drives, Backup Drives, flash disks and Recovery partitions. I'm only trying to report on the main/boot drive and not anything else. Granted, there may be some manual labor involved to filter out the oddball named volumes, but it will help reduce duplicate asset entries in the report. Additionally, if possible, I would like to display the 'byte size' as GB or MB (and I can rename the column title as needed) and I don't know how to go about inserting that conversion into the table.

3. For "Anti-Virus", I think ultimately I would like to have each detected A/V-related software title displayed on the same line. So, for example, For PCs with Symantec Endpoing Protection and Malwarebytes Anti-Malware Agent installed will also have Windows Defender installed but disabled. Originally filtering out Windows Defender, I inadvertently filtered out entries that ONLY have Windows Defender (which those machines would flag for needing SEP and MBAM). If there would be a way to place all detected A/V software on one line, that would greatly reduce the duplicate entries of assets.

4. For "UpTime", this would be a similar request as the "Hard Drive Size". I don't know how to make the conversion from 'seconds' to show days/hours/minutes/etc. I need to make that entry easier on the eyes to read. Unless I have people reading this report able to know that 3,000,000 seconds is approximately 35 days.

[mhammond]

Thank you!

-_- For a "simple" report, this project became more of a headache than I anticipated! I really do appreciate all the help!

That was what she was looking for! 🙂

[RCorbeil]

You can reformat the date/time in your report/query:

Convert(VarChar(10), tblAssetCustom.WarrantyDate, 121)

120: yyyy-mm-dd hh:mi:ss
121: yyyy-mm-dd hh:mi:ss.mmm
126: yyyy-mm-ddThh:mi:ss.mmm
127: yyyy-mm-ddThh:mi:ss.mmmZ

See W3Schools/Microsoft for a list of the various formats.

[mhammond]

Ok, so update....

Everything is NEARLY perfect, except my director, in an effort to interface with TopDesk, needs to have the warranty date show YYYY-MM-DD, instead of it showing as DD-MM-YYYY.

I know you can quickly change it on the fly by just changing the logged-in user's view to show the different date format; but she wants it so that it shows YYYY-MM-DD regardless of user who logs in.

I found some basic sql scripts on 'forcing' the format, but I'm unsure how to apply them, if they're even correct.
Date Format in SQL

How would I apply this (or something similar) to my Warranty Date field, if it's even possible?

[mhammond]

See? total n00b! duh! .... guess I gotta stop drinking when I'm coding LOL

Thank you, you fixed it! *whew* ... thought I was going nuts!

[RCorbeil]

The Stuff() is a substitute for your link to tblAntivirus. And I should have made the Stuff() link against tblAssets rather than tblAntivirus. Doing the latter produces too many rows of output, defeating the purpose of summarizing.

Try this:

Select Top 1000000
  tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Description,
  Case
    When tblAssetCustom.Model Like '%surface%'
    Then 'Tablet'
    Else TsysChassisTypes.ChassisName
  End As [computer type],
  tsysIPLocations.IPLocation As Location,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssetCustom.Serialnumber As [Serial Number],
  tsysOS.OSname,
  tblSystemEnclosure.Manufacturer As [PC Make],
  tblAssetCustom.Model As [PC Model],
  tblAssets.Processor,
  tblAssets.Memory,
  Stuff( ( Select ', ' + Cast(av2.DisplayName As varchar(60))
           From tblAntivirus av2
           Where tblAssets.AssetID = av2.AssetID
             AND av2.onAccessScanningEnabled = 1  --enabled
           For Xml Path('')), 1, 2, '')  As [Anti-Virus/Malware],
  tblDiskdrives.Volumename As [Hard Drive Name],
  Cast(tblDiskdrives.Size / Power(10, 9) As numeric) As [HD Size (GB)],
  tblAssetCustom.Warrantydate,
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
    Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
    Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As uptime
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where
  tblAssetCustom.Serialnumber Not Like 'VMWare%'
  And tblDiskdrives.Caption = 'c:'
  And tblAssetCustom.State = 1
Order By
  tblAssets.AssetName

[mhammond]

Apologies for the delayed response - got hit with a wave of Windows upgrades that took forever to complete.

- - - - - - -
@Brandon: I applied your coding and had to adjust it slightly. The SQL sequence to change SUR to Tablet, it changed all the other "types" to model names/numbers, so I had to change it to "Else TsysChassisTypes.ChassisName", which wasn't a big deal

So THANK YOU LOTS, it corrected a TON of the report.

- -
@RC62N: I'm trying to apply the logic for multiple AV into the report, but it seems that no matter where I place it, I either get the error "Invalid syntax at 1" or the report saves and it doesn't appear to manipulate the data. At the risk of sounding like (or completely being) a total n00b, where do I place that code?


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
Case
When tblAssetCustom.Model Like '%surface%' Then 'Tablet'
Else TsysChassisTypes.ChassisName
End As [computer type],
tsysIPLocations.IPLocation As Location,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Serialnumber As [Serial Number],
tsysOS.OSname,
tblSystemEnclosure.Manufacturer As [PC Make],
tblAssetCustom.Model As [PC Model],
tblAssets.Processor,
tblAssets.Memory,
tblAntivirus.DisplayName As [Anti-Virus/Malware],
Stuff((Select ', ' + Cast(av2.DisplayName As varchar(60))
From tblAntivirus av2
Where tblAntivirus.AssetID = av2.AssetID And av2.onAccessScanningEnabled =
1 For Xml Path('')), 1, 2, '') Antivirus,
tblDiskdrives.Volumename As [Hard Drive Name],
Cast(tblDiskdrives.Size / Power(10, 9) As numeric) As [HD Size (GB)],
tblAssetCustom.Warrantydate,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As uptime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetCustom.Serialnumber Not Like 'VMWare%' And tblDiskdrives.Caption =
'c:' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[RCorbeil]

You can use the logic from the multiple monitors report shared a few years back to combine multiple AV into a single column.

  Stuff( ( Select ', ' + Cast(av2.DisplayName As varchar(60))
           From tblAntivirus av2
           Where tblAntivirus.AssetID = av2.AssetID
		     AND av2.onAccessScanningEnabled = 1  --enabled
           For Xml Path('')), 1, 2, '') Antivirus,

Even if you don't want to list all installed & active antivirus products, you might consider adding the onAccessScanningEnabled=1 filter to your current report. It's all well and good that something is installed, but if it's not active it might as well not be present and you would want to know that.

Also, you may want to adjust the hard drive size depending on whether you want to report GB (advertised capacity) or GiB (Windows-reported capacity).

  Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As [Hard Drive Size],
  Cast(tblDiskdrives.Size / Power(2, 30) As numeric) As [HD GiB],
  Cast(tblDiskdrives.Size / Power(1024, 3) As numeric) As [HD GiB (alt)],
  Cast(tblDiskdrives.Size / Power(10, 9) As numeric) As [HD GB],

2^30 = 1024^3 = 1024 * 1024 * 1024. It's all the same, just how you prefer to write it.

[brandon_jones]

Try this report and see if it works for you:

1.I added logic to the SQL query that will list the computer type as a tablet when the model contains surface.

2.Converted size to GB. I also added logic that will only show the size of the c: drive, since most of the time that is the boot drive.

3. I don't think that it will be possible to list all the anti virus software installed on a single computer on the same line. It might be possible but I don't really know that much about SQL.

4. Uptime converted to Days hours and minutes.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
Case
When tblAssetCustom.Model Like '%surface%' Then 'Tablet'
Else tblAssetCustom.Model
End As [computer type],
tsysIPLocations.IPLocation As Location,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Serialnumber As [Serial Number],
tsysOS.OSname,
tblSystemEnclosure.Manufacturer As [PC Make],
tblAssetCustom.Model As [PC Model],
tblAssets.Processor,
tblAssets.Memory,
tblAntivirus.DisplayName,
tblDiskdrives.Volumename As [Hard Drive Name],
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As [Hard Drive Size],
tblAssetCustom.Warrantydate,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As uptime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAntivirus.DisplayName Not Like 'Windows%' And tblDiskdrives.Caption =
'c:' And tblAssetCustom.State = 1
Order By tblAssets.AssetName