Filter by OS Windows 10

[tcrook]

I have a report that is supposed to list all the windows 10 machines in our environment. We have two new windows 10 machines that are not showing up on that report, but are showing up on the new computers detected. The report (See below) is filtered by tsys.osname 'win 10' I checked the new machines and they have tsys.osname win 10. This is confounding me and causing mgmt to lose faith in my reports. Any Help would be appreciated.


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSname,
tblOperatingsystem.Version,
tblOperatingsystem.InstallDate,
tsysIPLocations.IPLocation,
tblADComputers.Description
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tsysOS.OSname = 'win 10'
Order By tblAssets.Domain,
tblAssets.AssetName

[David_G]

That's correct, most likely, no information for the computers was found from Active Directory. As you are using an inner join to link the database table tblADComputers, the asset also needs to have a record within that database table. Therefore, I would recommend to use a left join to join the tables as seen highlighted below. This will show all your assets based on the criteria and will also show the AD Description, if found.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSname,
tblOperatingsystem.Version,
tblOperatingsystem.InstallDate,
tsysIPLocations.IPLocation,
tblADComputers.Description
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tsysOS.OSname = 'win 10'
Order By tblAssets.Domain,
tblAssets.AssetName

[tcrook]

removing the filter did not help. I was able to pin point the causing factor but still don't have a solution.
the Field tblAdComputers.Description if removed will revel the hidden asset. I cannot tell why but i suspect it in in one of the join statements.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSname,
tblOperatingsystem.Version,
tblOperatingsystem.InstallDate,
tsysIPLocations.IPLocation,
tblADComputers.Description
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tsysOS.OSname = 'win 10'
Order By tblAssets.Domain,
tblAssets.AssetName

[AZHockeyNut]

I agree take the filter off completely and see if they are there, then, when they are there you should be able to "see" why they differ.
I see all mine and I have several variations (pro, enterprise, etc.)

as for the wildcard thing, = should produce an exact match while like should product similar to matches if I recall correctly. I am not a dba though, far from it according to my dba lol.

[RCorbeil]

Take the Win 10 filter off completely and see if the expected records show up. If not, either you have another filter in place that's removing them from the result set or they're not in the database yet. If it's the latter and you know where they are, e.g. their IP addresses, you should be able to set up temporary scanning target(s), scan them to add them to the database, then delete your temporary target(s).

[tcrook]

tried both options and neither returned the "new" windows 10 assets. Is it possible that this is s timing thing and the new assets will appear Monday?

[RCorbeil]

I can't think of a good reason why tsysOS.OSname = 'Win 10' wouldn't work, but if AZHockeyNut's suggestion about using wildcard matching on the field doesn't work out, you could try linking against tblOperatingSystem and applying filters based on tblOperatingSystem.Caption. It shouldn't need to come to that, but it's an option. I've only got a handful of Win10 machines in my inventory, but based on those I'd expect

WHERE tblOperatingSystem.Caption LIKE '%Windows 10%'

or

WHERE tblOperatingSystem.Caption LIKE 'Microsoft Windows 10%'

should work.

[AZHockeyNut]

assuming all you care about is the '10' portion of OS why not change this line....

Where tsysOS.OSname = 'win 10'

to

Where tsysOS.OSname = '%10%'

the % is a wildcard, meaning as long as Ten (10) appears in the value returned for OSname, then it would be returned....
so
windows 10, win 10, win 10 pro, windows 10 pro, 10 windows professional, Linux 10, iOS 10.6, would all be returned (not that some of those entries are legit, they are meant to exaggerate the point.

you could also do

Where tsysOS.OSname = '%win% %10%'

this would make sure win and windows are part of it as well as 10 and 10.1 are...
to simply put Where tsysOS.OSname = 'win 10' means you are looking for an exact match.


and I would use LIKE rather than = if you change the statement. I prefer LIKE when I am not looking for an exact match.

[RCorbeil]

AZHockeyNut wrote:
Where tsysOS.OSname = 'win 10'

to

Where tsysOS.OSname = '%10%'

...

Where tsysOS.OSname = '%win% %10%'

Are your sure about = working with wildcards? As I learned it, = is used for exact matches while LIKE is used with wildcards.

(Just curious. For clarity I'll continue using = and LIKE as I learned them, but it'll be something to keep in mind when debugging code if it is so.)