Built-in Win10 report

[B_B_]

Hello all,

When I open up my Lansweeper home page, there is a toolbar close to the top of the page, right underneath the Search... box. If I go to Software, Scanned OS, Win 10 (clicking on Win 10), I end up with a Win 10 assets report showing that I have a total of 288 Windows 10 computers. This number is important as this is the report my boss holds as the "gold standard" of how many Win10 computers we have in our facility.

Here is my issue. On the left hand side of the screen, there is usually an "Edit Report" hyperlink. Since this is a prebuilt report, then this link is missing. So, I gave it a shot, using a couple of different queries I found on this forum, trying to recreate this Win 10 report, along with the encryption status, which is what my boss really wants, the encryption status.

Using the code below, I can come up with around 170 Windows 10 computers, along with their encryption status. Therefore I believe I need to figure out how the builtin Windows 10 query was configured so I could add the encryption query onto it. Does anyone have the code, or code that would show the same results set?

Here is what I put together, again using code from this forum:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where (tblEncryptableVolume.ProtectionStatus = 0) Or
(tblEncryptableVolume.ProtectionStatus = 1)
Order By tblAssets.AssetName

Just now I ran both my query (shows 169 Win10 PCs) and the builtin query (shows 288 Win10 PCs). Obviously I am missing something, I just have not figured out what it is just yet.

Thank you for your assistance!

[Caleb]

This is a report we use to pull Windows 10 Encryption information. Maybe it will provide some ideas?

Select Top 10000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblOperatingsystem.Caption As [Operating System],
  tblOperatingsystem.Version As [OS Build],
  Case tblOperatingsystem.Version When '10.0.10240' Then '1507'
    When '10.0.10586' Then '1511' When '10.0.14393' Then '1607'
    When '10.0.15063' Then '1703' When '10.0.16299' Then '1709'
    When '10.0.17017' Then '1803' Else '?' End As Release,
  tblAssets.Username As [Last Logged In User],
  Case When tblAssets.AssetID Not In (Select tblEncryptableVolume.AssetId
    From tblEncryptableVolume) Then 'UNKNOWN'
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
  End As [BitLocker Status],
  tblState.Statename As [Asset State],
  tblAssets.Lastseen As [Last Contact],
  tsysOS.Image As icon
From tblAssets
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Full Outer Join tblEncryptableVolume On tblEncryptableVolume.AssetId =
    tblAssets.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblEncryptableVolume.DriveLetter = 'C:' Or
    tblEncryptableVolume.DriveLetter Is Null Or tblEncryptableVolume.DriveLetter
    = '') And tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  tblAssets.AssetUnique

[fjca]

B.B. wrote:
Hello all,

When I open up my Lansweeper home page, there is a toolbar close to the top of the page, right underneath the Search... box. If I go to Software, Scanned OS, Win 10 (clicking on Win 10), I end up with a Win 10 assets report showing that I have a total of 288 Windows 10 computers. This number is important as this is the report my boss holds as the "gold standard" of how many Win10 computers we have in our facility.

Here is my issue. On the left hand side of the screen, there is usually an "Edit Report" hyperlink. Since this is a prebuilt report, then this link is missing. So, I gave it a shot, using a couple of different queries I found on this forum, trying to recreate this Win 10 report, along with the encryption status, which is what my boss really wants, the encryption status.

Using the code below, I can come up with around 170 Windows 10 computers, along with their encryption status. Therefore I believe I need to figure out how the builtin Windows 10 query was configured so I could add the encryption query onto it. Does anyone have the code, or code that would show the same results set?

Here is what I put together, again using code from this forum:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where (tblEncryptableVolume.ProtectionStatus = 0) Or
(tblEncryptableVolume.ProtectionStatus = 1)
Order By tblAssets.AssetName

Just now I ran both my query (shows 169 Win10 PCs) and the builtin query (shows 288 Win10 PCs). Obviously I am missing something, I just have not figured out what it is just yet.

Thank you for your assistance!

I see two issues:

a) You are searching only for ProtectionStatus=0 or ProtectionStatus=1, that report won't pick ProtectionStatus=2, the "UNKNOW" ones. So, add "tblEncryptableVolume.ProtectionStatus = 2" to your "Where" clause.

b) That report will list ALL your Windows machines, not just the Windows 10 ones. If you only have Win10, then that's OK, if not, you need to add a clause to match the Win10 ones, add "tsysOS.Osname = "Win 10" " to your where clause.

[B_B_]

fjca wrote:

B.B. wrote:
Hello all,

When I open up my Lansweeper home page, there is a toolbar close to the top of the page, right underneath the Search... box. If I go to Software, Scanned OS, Win 10 (clicking on Win 10), I end up with a Win 10 assets report showing that I have a total of 288 Windows 10 computers. This number is important as this is the report my boss holds as the "gold standard" of how many Win10 computers we have in our facility.

Here is my issue. On the left hand side of the screen, there is usually an "Edit Report" hyperlink. Since this is a prebuilt report, then this link is missing. So, I gave it a shot, using a couple of different queries I found on this forum, trying to recreate this Win 10 report, along with the encryption status, which is what my boss really wants, the encryption status.

Using the code below, I can come up with around 170 Windows 10 computers, along with their encryption status. Therefore I believe I need to figure out how the builtin Windows 10 query was configured so I could add the encryption query onto it. Does anyone have the code, or code that would show the same results set?

Here is what I put together, again using code from this forum:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where (tblEncryptableVolume.ProtectionStatus = 0) Or
(tblEncryptableVolume.ProtectionStatus = 1)
Order By tblAssets.AssetName

Just now I ran both my query (shows 169 Win10 PCs) and the builtin query (shows 288 Win10 PCs). Obviously I am missing something, I just have not figured out what it is just yet.

Thank you for your assistance!

I see two issues:

a) You are searching only for ProtectionStatus=0 or ProtectionStatus=1, that report won't pick ProtectionStatus=2, the "UNKNOW" ones. So, add "tblEncryptableVolume.ProtectionStatus = 2" to your "Where" clause.

b) That report will list ALL your Windows machines, not just the Windows 10 ones. If you only have Win10, then that's OK, if not, you need to add a clause to match the Win10 ones, add "tsysOS.Osname = "Win 10" " to your where clause.

Hello fjca,

Thank you so much for replying to me! 🙂 I applied your first fix, part a), and it helped some. Now instead of 168 machines, it picks up 172 machines. So, I still have something off as the built in report shows 289. We are making progress though. Also, the numbers could have something to do with my inability to get the second fix, part b), to work properly.

When I make an attempt to write in the part b), the code moves around on me. I thought maybe I mistyped it the first time, but when I tried it again, the web server changes the code. Most likely I am doing something wrong, as I am still new to SQL coding and making many, many mistakes along the way.

So here is what I am typing into the code window (note this is just a snippet from the bottom of the code):

Where (tblEncryptableVolume.ProtectionStatus = 0) Or
(tblEncryptableVolume.ProtectionStatus = 1) Or
(tblEncryptableVolume.ProtectionStatus = 2) AND
(tsysOS.Osname = 'Win 10')
Order By tblAssets.AssetName

After typing this in, the "Save" button at the top of the screen is still grayed out. So I have to hit the Tab key once at the end of the line to enable the "Save" button. When I do, and the "Save" button becomes available again, then the code changes like so:

Where (tblEncryptableVolume.ProtectionStatus = 0) Or
(tblEncryptableVolume.ProtectionStatus = 1) Or
(tsysOS.OSname = 'Win 10' And tblEncryptableVolume.ProtectionStatus = 2)
Order By tblAssets.AssetName

So I go in and fix it. This time, I get it back to how I had it before, then the second I hit the still enabled "Save" button, the code changes again and reverts back.

Okay, I keep going back to Lansweeper and trying things as I am writing this response. This time, I had to do it like this for it to work:

Where (tblEncryptableVolume.ProtectionStatus = 0) Or
(tblEncryptableVolume.ProtectionStatus = 1) Or
(tblEncryptableVolume.ProtectionStatus = 2) Or
(tsysOS.OSname = 'Windows 10')
Order By tblAssets.AssetName

The AND operator is not allowed after a couple of OR operators? I guess I could see where that could be a logic error, but I'm struggling with the logic of how the system "fixes" the presumed error.

Anyway, you are correct, we are still running a mixed environment of Windows 7 and Windows 10 computers. I did look and the built in query only pulls back Windows 10 machines and it claims there are 289 Windows 10 machines. My query shows both Windows 7 and Windows 10 machines in it, but it still shows almost half of the built in query. So, should I go back to the proverbial 'drawing board' and try my query again?

Thank you!