Report showing IP Location, and all types of OS.

[jmje]

Hi, I'm looking to generate a site report that includes the following key information :

IP Location (from Asset Groups Page)
Asset Name
OS Type (windows, linux, mac, basically all of them, with the OS Version if possible.)
IP Address
Domain
Address width (32 or 64)
Manufacturer
Model
Serial Number
Last Seen

I can get all the Operating systems, but not the IPLocation, or vice versa. I can't seem to get both. Once I get the IPLocations working right, all I get is windows systems.

I have right clicked the link between tblAssets and tsysOS to "Select all rows from tblAssets", and it doesn't seem to give me linux boxes too.

Last version of code I played with that gives me all assets :

Select Top 1000000 tblAssets.IPAddress,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Asset Type],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Model,
tsysOS.OSname
From tblAssets
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.IPAddress,
tblAssets.AssetName Desc

Last version that I was able to use, that gives me IPLocations :

Select Top 1000000 tsysIPLocations.IPLocation,
tblAssets.AssetID,
tblAssets.AssetName,
tblProcessor.AddressWidth,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Lastseen
From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Order By tsysIPLocations.IPLocation,
tblAssets.AssetName

This would be much easier if I could edit the reports in the dashboard under "IP Locations Overview" widget. Is that possible?


Thanks in advance!

[MikeMc · Accepted solution]

Thanks for the compliment. I still have a lot to learn.

Anyway, here you go:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.IPAddress,
  tblAssets.Domain,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tsysAssetTypes.AssetTypename As [Asset Type],
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tsysIPLocations.IPLocation,
  tblProcessor.AddressWidth
From tblAssets
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join tblProcessor On tblProcessor.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.IPAddress,
  tblAssets.AssetName Desc

[jmje]

That got me more, but it's still missing most of my linux systems.

The code below reports on 7399 assets in my environment. It also includes monitors, printers, routers, phones, and switches. I don't mind having all that in the report, as I have to take it into excel to build the final reports for management anyway, and they're easy to hide with autofilter. I might need to show them in the future by site anyway, so I left it.

What I can't get with the code below, is the IPLocation to work.
When I add IPLocation in, I get 2600 assets. No monitors, printers, routers, phones, switches, etc... basically nothing but windows machines.

I'm amazed by what you can do with SQL Query. Do you know how to add IPLocation and Address width to this?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tsysAssetTypes.AssetTypename As [Asset Type],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.IPAddress,
tblAssets.AssetName Desc

Thanks again!

[MikeMc]

I was curious to see what information Lansweeper actually pulls in when it scans a Linux OS, so I loaded up a test VM and let it do its thing. I altered my original query to pull in Linux assets and some of their information.

[jmje]

Unfortunately, still no non-windows assets.

Appreciate the attempt, though.


If anyone else knows how to do this, I'd appreciate it.
Been doing a lot of this lately :

[MikeMc]

Whoops, my bad. I looked at tsysOS table and it only contains Windows version codes and names. I changed my original query to a left join for that section, so you should be able to see your Linux assets now.

We don't really use Lansweeper for Linux scanning, so I doubt I can be much more help in that area.

[jmje]

Looks great, but for some reason I don't get the linux based systems in this report.
I'll noodle it a bit, and see if I can get them.

If you see a way before I do, great kudos.

Thanks Mike!

[MikeMc]

See if this report works for you.

Select Distinct Top 1000000 tsysIPLocations.IPLocation,
  tblAssets.AssetID,
  tblAssets.AssetName,
  (Case When tblAssets.Assettype = -1 Then tblOperatingsystem.Caption
    Else tblLinuxSystem.OSRelease End) As OSName,
  (Case When tblAssets.Assettype = -1 Then tblAssets.OScode
    Else tblLinuxSystem.KernelRelease End) As OSCode,
  tblAssets.IPAddress,
  tblAssets.Domain,
  (Case
    When tblAssets.Assettype =
    -1 Then Convert(VARCHAR,tblProcessor.AddressWidth) Else 'Unknown'
  End) As AddressWidth,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
  Left Join tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID
  Left Join tblProcessor On tblProcessor.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And (tblAssets.Assettype = -1 Or
    (tblAssets.Assettype = 11 And Exists(Select tblLinuxSystem.AssetID
      From tblLinuxSystem Where tblLinuxSystem.AssetID = tblAssets.AssetID)))
Order By tsysIPLocations.IPLocation,
  tblAssets.IPAddress

[jmje]

Still hoping someone can help me figure this out. I've spent hours on this, and I'm sure it's something simple, but it's beyond me.

Thanks.

[jmje]

Anyone?