SQL query for a date range, using last report time

Reporting
1

(imported topic written by SystemAdmin)

I am trying to write a SQL query that will use the ‘Last Report Time’ property and allow me to return objects with “last report time” older than two days of when the quiery was run.

Being green to SQL, this is blowing my mind. Any help would be appreciated.

2

(imported comment written by SystemAdmin)

In the end, I want the query to retun what is currently “offline/ not reporting” and “online/ reporting” to TEM. Is there a way to query against whatever the console uses to change the status of a computer (Black/ grey)?

3

(imported comment written by Lee Wei)

Here is a statement with some sample constructs that you might be able to use.

It assumes that you want to designate computers not reported for 45 minutes to be offline.

Otherwise, change the reference to 45 minutes accordingly.

select
A.Value “Computer Name”,
convert(datetime, substring(B.Value, 6, 20), 103) “Last Report Time”,
datediff(minute, convert(datetime, substring(B.Value, 6, 20), 103), getutcdate()) “Minutes Since Last Reported”,
case when datediff(minute, convert(datetime, substring(B.Value, 6, 20), 103), getutcdate()) > 45
then 'Offline’
else 'Online’
end as "Status"
from bes_column_headings A, bes_column_headings B
where A.Name = 'Computer Name’
and B.Name = 'Last Report Time’
and A.ComputerID = B.ComputerID
order by datediff(minute, convert(datetime, substring(B.Value, 6, 20), 103), getutcdate()) desc

Lee Wei

4

(imported comment written by SystemAdmin)

Thank you Lee. I will give them a shot.