Computers Left on Overnight

[rhodges]

I have created a report that will show me computers that are on for over 24 hours. The report shows me everything that I need with the exception that computers not scanned that morning show up. I have setup a company wide scan to happen at 2a every morning. If the computers are not on at 2a every morning, I do not want them to show up on this report. I understand that the uptime is calculated based on when the computer was last seen and if the computer is not scanned it will remain in the up state for the report. I would be so grateful if someone could update me report to remove any computer that was not scanned the morning that the report runs. Thanks

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Username,
tblAssets.Domain,
tblAssets.IPAddress,
Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24) As UptimeDays,
tblAssets.Lastseen,
SubString(SubString(tblADusers.OU, CharIndex(',', tblADusers.OU) - 250, 250),
4, 250) As Department,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.email
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.AssetName Not Like '(%first3letters of server systems)' And tblAssets.AssetName Not Like
'(%first 3 letters of hand scan systems)' And tblAssets.AssetName Not Like '(%first 3 letters of phone servers)' And
tblAssets.AssetName Not Like '(specific pc name)' And tblAssets.AssetName
Not Like '(scanner pcs%)' And tblAssets.AssetName Not Like '(specific pc name)' And
Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24) > 1 And
tblAssets.Lastseen > GetDate() - 1 And SubString(tblADusers.OU, 4,
15) Not Like 'OU=' And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole = 1 And tblAssetCustom.Manufacturer Not
Like '(Virtual Desktops%)'
Order By UptimeDays Desc

[Susan_A]

Lastseen and GetDate() are both datetime values, meaning they return both a date and a time. Lastseen is the last time the asset was successfully scanned by Lansweeper. GetDate() is a SQL function and returns the datetime of when your query is run.

If you're running your report at 07/28/2015 20:00:00 for instance, the snippet below means: show me the assets with a Lastseen value that is greater than (i.e. more recent than) 07/28/2015 20:00:00 minus 1 day. 07/28/2015 20:00:00 minus 1 day is 07/27/2015 20:00:00, so the snippet below will return assets whose Lastseen is more recent than 07/27/2015 20:00:00. This includes assets that were scanned at 07/27/2015 21:00:00, at 07/28/2015 06:00:00 etc.

tblAssets.Lastseen > GetDate() - 1

This not what you're trying to do however, from what I'm understanding. You're trying to list assets that were last successfully scanned on the same day as GetDate(). In more technical terms, this means you're trying to list assets where the date part of Lastseen is the same as the date part of GetDate(), which is 07/28/2015 in the example above. In order to add a filter like this, you need to remove the time from Lastseen and GetDate(), so you're only left with the date. This is what my SQL snippet does, it isolates the date from Lastseen and GetDate() and specifies that only assets where both date values are the same should be listed.

A more technical explanation of what I did:

• Use DateDiff to get a count of the number days between date "0" and Lastseen. "0" is interpreted by SQL as January 1st, 1900. More info on DateDiff can be found in Microsoft's knowledge base.
  
• Put DateAdd, which returns a date, around the DateDiff function to add the day count to "0" again. This in essence returns Lastseen, but without the time part.
  
• Do the same for GetDate().
  
• Specify that only records where the DateAdd result for Lastseen and the DateAdd result for GetDate() are the same should be listed.

There are other, less complicated ways of isolating the dates, but this is the first one that came to mind that's sure to work in SQL Compact as well. In recent SQL Server releases, you could simply use Convert to change datetimes into dates. This is all standard SQL syntax, not specific to Lansweeper.

[rhodges]

Susan.A,

Thanks for your assistance with this request. Unfortunately, this is like a foreign language to me. Can you provide an explanation on how the change works?

As I understand it:
tblAssets.Lastseen > GetDate() - 1 = Return results where the lastseen date of the asset if greater than 24 hours.

What I believe is happening:
DateAdd(dd, 0, DateDiff(dd, 0, tblAssets.Lastseen)) = DateAdd(dd, 0, DateDiff(dd, 0, GetDate())) = Return results where the DateAdd of Lastseen equals the Getdate????

How I'm reading this is:
DateAdd(a)(datepart,number,date)= DateAdd(b)(datepart,number,date) where
DateAdd(a) = (day, 0, DateDiff(day, 0, tblAssets.Lastseen)) and
DateAdd(b) = (day, 0, DateDiff(day, 0, GetDate()))

The variable in DateAdd(a) called tblAssets.Lastseen = Date the asset was last seen
The variable in DateAdd(b) called GetDate = Current Date at time of query

Thanks again for your assisting me with this and educating me as well.

[Susan_A · Accepted solution]

Replace this...

tblAssets.Lastseen > GetDate() - 1

... with this to only list machines that were scanned today:

DateAdd(dd, 0, DateDiff(dd, 0, tblAssets.Lastseen)) = DateAdd(dd, 0, DateDiff(dd, 0, GetDate()))

Unless you have scanning servers in other time zones, your Lastseen values should not be greater than today, as this would mean machines were scanned "in the future".

[rhodges]

I often confuse the matter by providing too much information. Let me clarify my question.

Does anyone know how to filter a report so that only computers that have Lastseen date greater than or equal to the day that the report is ran show up on the report?



I might even be doing this entirely wrong from the start.
The goal is to provide the information below, but only if the computer was up and running all night.

Needed information:
AssetName–Username–Domain–IPAddress–Uptime Days–Lastseen–Department–Firstname–Lastname-Email

Thanks.