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.
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)?
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