BootTime != LastSeen

[TomFox]

Dear Lansweeper community,

I have been developing a rather complex compliancy dashboard, to display computers that fail on a number of different compliancy checks, and show these on a dashboard in different colours.

Everything is working perfectly except this line. I want to only show assets where the service has the status "Stopped" AND where the BootTime is not equal to the LastSeen. I have tried all different possibilities but can't get it to work correctly. What's the best way? I also tried saying the Uptime Days must be higher than 1, but this produces the same result, see screenshot.

OR (tblServiceState.State Like 'Stopped' And DateDiff(day, tblAssets.Lastseen - Convert(Decimal,tblAssets.Uptime / 60 / 60 / 24), tblAssets.Lastseen) > 1)


Where I am going wrong here? There should be no entries in this list where the Uptime is 0.




Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysOS.OSname As [OS (Red)],
tblAssets.Version As [Version (Orange)],
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As
[Free GB (Yellow)],
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As [DiskSize GB],
Cast(SubQuery1.Filesize / 1024 / 1024 As numeric) As [PageFile MB],
tblAssets.Memory As [RAM MB],
tblServiceState.State As [Altiris State (Green)],
tblServiceStartMode.StartMode As [Altiris StartMode (Green)],
tblAssets.Lastseen - Convert(Decimal,tblAssets.Uptime / 60 / 60 /
24) As [Boot Time],
DateDiff(day, tblAssets.Lastseen - Convert(Decimal,tblAssets.Uptime / 60 /
60 / 24), tblAssets.Lastseen) As [Uptime days (Blue)],
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblAssets.Lastseen,
Case
When tsysOS.OSname Like 'WIN 7' Then '#ffadad'
When tsysOS.OSname Like 'WIN 2016' Then '#ffadad'
When (tsysOS.OSname Like 'WIN 1%' And tblAssets.Version Not Like '20H2' And
tblAssets.Version Not Like '21H2') Then '#FFD580'
When (tsysOS.OSname Like 'WIN 2%' And tblAssets.Version Not Like '1809' And
tblAssets.Version Not Like '21H2') Then '#FFD580'
When Cast(tblDiskdrives.Freespace / 1024 / 1024 As numeric) < 10000 Then
'#FDFD96'
When tblServiceState.State Like 'Stopped' Then '#d4f4be'
When tblServiceStartMode.StartMode Like 'Disabled' Then '#d4f4be'
When DateDiff(day, tblAssets.Lastseen - Convert(Decimal,tblAssets.Uptime /
60 / 60 / 24), tblAssets.Lastseen) > 90 Then '#87ceeb'
End As backgroundcolor
From tblAssets
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Inner Join tblServiceStartMode On tblServiceStartMode.StartID =
tblServices.StartID
Left Join (Select Top 1000000 tblFileVersions.AssetID,
tblFileVersions.FilePathfull As PatchSearched,
tblFileVersions.Found,
tblFileVersions.Filesize
From tblFileVersions
Where tblFileVersions.FilePathfull Like '%pagefile.sys%') SubQuery1 On
SubQuery1.AssetID = tblAssets.AssetID
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where (tsysOS.OSname Like 'WIN 7' Or Cast(tblDiskdrives.Freespace / 1024 /
1024 As numeric) < 10000 Or (tblServiceState.State Like 'Stopped' And
DateDiff(day, tblAssets.Lastseen - Convert(Decimal,tblAssets.Uptime / 60 /
60 / 24), tblAssets.Lastseen) > 1) Or tblServiceStartMode.StartMode Like
'Disabled' Or DateDiff(day, tblAssets.Lastseen -
Convert(Decimal,tblAssets.Uptime / 60 / 60 / 24), tblAssets.Lastseen) > 90
Or (tsysOS.OSname Like 'WIN 1%' And tblAssets.Version Not Like '20H2' And
tblAssets.Version Not Like '21H2') Or (tsysOS.OSname Like 'WIN 2%' And
tblAssets.Version Not Like '1809' And tblAssets.Version Not Like '21H2'))
And tblDiskdrives.Caption Like 'c:' And tblAssets.Lastseen > GetDate() - 60
And tblServicesUni.Name Like '%AeXNSClient%' And tblState.Statename = 'Active'
Order By tsysIPLocations.IPLocation,
tblAssets.AssetName

[TomFox]

Okay, so my bad. So the erroneous 0 results are matching a different rule, and therefore will always be displayed!

It's correct because there are no green results with 0.

Time for coffee

[TomFox]

Sorry KevinA-REJIS, this gives the same result. Even >10 still gives lots of 0s so this isn't the answer.

I also tried different permutations of (tblAssets.Lastseen - Convert(Decimal,tblAssets.Uptime / 60 / 60 /
24)) <> tblAssets.Lastseen and I also tried Not Like tblAssets.Lastseen but this gave the same result.

So I'm missing something here with data types I guess??? Or Lansweeper's interpretation of brackets?

[KevinA-REJIS]

What happens if you change it to not equal 0?

OR (tblServiceState.State Like 'Stopped' And DateDiff(day, tblAssets.Lastseen - Convert(Decimal,tblAssets.Uptime / 60 / 60 / 24), tblAssets.Lastseen) <> 0)