Last Reboot - Free Query (Using Dynamic Date)

Version 2
    Share This:

    Many customers would like to create a Device Group or Report showing devices that have not rebooted for X amount of DAYS. The BCM DB stores this data in a STRING field so the ability to use the  ADD CRITERIAN and select a dynamic date range is not available. However, you can accomplish this by using a FREE QUERY and use the CONVERT function in MS SQL to make the string value a DATE VALUE.

     

    The "Last Reboot" value is stored in the Hardware Inventory node under "Operating System". This table is created on the fly so it has a unique identifier for every instance. In order to find the table name you must first create the query using the stand process and then once the query is complete go to the General Table and modify by selecting FREE QUERY and when prompted to "KEEP" the values select YES.

     

    Here is the criterion used to find device that have not rebooted in more than a week (most use real date first such as 2017/12/01 - YYYY/MM/DD)

    I am also adding the "Last Update Time" is within the last 24 hours or 1 day (Default for GREEN ICON Device)

    I am also going to remove Servers from the query so if I want to assign an Op Rule to reboot these devices, I will not be reboot Servers!

     

    I am also going to Select Typology Type = Client so if I have a Relay running on a Desktop OS I will not be rebooting those..  (Optional)

     

    Here is what my query looks like before I convert to free query:

    Make sure you PREVIEW results to ensure you have all the elements correctly added. Use the drop down selector to "SQL RESULTS" in the preview pane.

     

    Convert to Free Query:

     

    Here is what the FREE QUERY will look like once converted. The items highlighted are the values you must change for YOUR instance and they will not be the same:

    I always copy the SQL to notepad and paste it twice...  once to keep original and second to modify:

     

     

    SELECT DISTINCT  Devices.DeviceName FROM Devices  INNER JOIN  HwInv_ObjType_158 OperatingSystem  ON Devices.DeviceID=OperatingSystem.DeviceID  WHERE ( (OperatingSystem.ATTR_597030 <N'2017/12/01') AND (Devices.LastUpdate >=DATEADD (day, -1, GetUTCDate())) AND (Devices.TopologyType =N'_DB_DEVTYPE_CLIENT_')  AND  Devices.DeviceID NOT IN (SELECT DeviceID FROM Devices WHERE  (Devices.DeviceType =N'server') ) ) ORDER BY Devices.DeviceName ASC

     

     

    The line that needs to be modified is:

    (OperatingSystem.ATTR_597030 <N'2017/12/01')

     

    To:

    (CONVERT(datetime,OperatingSystem.ATTR_597030) < DATEADD(week, -1,GetUTCDate())

     

    That hard part is making sure you get the opening and closing parentheses "(" and ")" set properly.

    You must VERIFY SQL to ensure you did the proper edits:

    Then Save using the Save icon in the black Ribbon next to the verify AFTER you activate the query.

     

    Here is the SQL using MY unique table Identifier (you must change to yours to make it work)!

     

     

    SELECT DISTINCT  Devices.DeviceName FROM Devices  INNER JOIN  HwInv_ObjType_158 OperatingSystem  ON Devices.DeviceID=OperatingSystem.DeviceID

    WHERE ( (Devices.TopologyType =N'_DB_DEVTYPE_CLIENT_') AND ((CONVERT(datetime,OperatingSystem.ATTR_597030) < DATEADD(week, -1,GetUTCDate())))

    AND (Devices.LastUpdate >= DATEADD(day,-1,GetUTCDate()))

    AND  Devices.DeviceID NOT IN (SELECT DeviceID FROM Devices

    WHERE  (Devices.DeviceType =N'Server') ) )

    ORDER BY Devices.DeviceName ASC

     

    This query can populate a Device Group or be used in a Report!