Collection of last reboot?

JenH · 08-24-2005, 05:55 PM

I'm kind of digging around the tables, but is there a way to create a collection based on the last time a system rebooted? We want a collection of systems that have not rebooted since August 23rd so we can force that collection to reboot.

I have a feeling a custom inventory will need to be run to get this info and create the collection.

Thanks,

Jen

Nick · 08-24-2005, 08:32 PM

I did a custom collection for this in the past.

I used a WMI query for the the last reboot time. The problem is that the time is done in DMTI format (yyyymmddhhmmss) etc. That would need to be converted.

Ray · 08-25-2005, 06:10 AM

Show us the query, Show us the query, Show us the query.

Sorry sounded funny but then again its 5am... P@tch1ng 0wnz m3

Nick · 08-25-2005, 08:57 AM

select LastBootUpTime from Win32_OperatingSystem

Is the wmi if I remember correctly that you would use in a wmi custom inventory setup.

I lent my usb drive to a friend that has the job on it.

JenH · 08-25-2005, 02:06 PM

This is the Custom Inventory XML we have...

Quote:

<?xml version="1.0" encoding="windows-1252"?>
<InventoryClasses>

<InventoryClass name="AeX OS LastBootUpTime" manufacturer='Altiris' description='Last time system rebooted' platform='Win32' version='1.0' mifClass='ALTIRIS|AEXOSLASTBOOT|1.0'>
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="c0" rs:name="LastBootUpTime" rs:number="1" rs:nullable="true" mifAttrId="1">
<s:datatype dt:type="string" dt:maxLength="255"/>
</s:AttributeType>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row
<%foreach wmiobject="o" namespace="root\CIMV2" wql="select * from Win32_OperatingSystem"%>
c0="<%writexml "wmi

.LastBootUpTime"%>"
<%next%>
/>
</rs:data>
</xml>
</InventoryClass>
</InventoryClasses>

How did you convert the date so it would work in a collection the way we want it to?

Nick · 08-25-2005, 02:47 PM

I didn't get that far to be honest Jen.

I think Jim gave me some fancy SQL query that would convert most of it. Let me see if I can dig it up.

JenH · 08-25-2005, 02:57 PM

Nick...thanks...I appreciate it.

One of my collegues used to use "custinvhelper.exe" to build custom inventories. I've heard of it, too, but can't find it. I looked in all of my Manage Fusion archives and I didn't find it there.

Does it still exist and can someone share it?

Thanks!

Jen

Nick · 08-25-2005, 03:05 PM

I think I've got that lying around somewhere. It's probably on my loaned out usb drive.

publius · 08-25-2005, 03:29 PM

Try this SQL query to see if it's what you're looking for:

SELECT T1.[Name] AS 'Name', T0.[_eventTime] AS '_eventTime' FROM [Evt_Reboot] T0 INNER JOIN [Inv_AeX_AC_Identification] T1 ON T0.[_ResourceGuid] = T1.[_ResourceGuid] WHERE T0.[_eventTime] <= '2005-08-23' ORDER BY T0.[_eventTime] ASC

Hope this helps!

Edit: This was done as a report and would have to be converted to a collection.

JenH · 08-25-2005, 05:35 PM

Thanks! However, I'm getting a lot of duplications. I tried changing to SELECT DISTINCT but still got duplicate machines listed.

Nick · 08-25-2005, 09:41 PM

Any idea what populates the Evt_Reboot table? I've seen it before but it never holds any data that I've seen.

Tom.Reyntjens · 08-26-2005, 05:33 AM

Nick,

if I'm not mistaken, Evt_Reboot is populated by PM (since PM 6.0 HF2, agent build 1137).

Jen,

to eliminate duplicates from the query, try the following :

SELECT T1.[Name] AS 'Name',
MAX(T0.[_eventTime]) AS '_eventTime'
FROM [Evt_Reboot] T0
INNER JOIN [Inv_AeX_AC_Identification] T1
ON T0.[_ResourceGuid] = T1.[_ResourceGuid]
WHERE T0.[_eventTime] <= '2005-08-23'
GROUP BY T1.[Name]
ORDER BY T0.[_eventTime] ASC

If you want to create a collection, however, all you need is Guids, and this query should do :
SELECT DISTINCT T1.[_ResourceGuid] AS 'Name'
FROM [Evt_Reboot] T0
INNER JOIN [Inv_AeX_AC_Identification] T1
ON T0.[_ResourceGuid] = T1.[_ResourceGuid]
WHERE T0.[_eventTime] <= '2005-08-23'

Nick · 08-26-2005, 08:58 AM

VERY nice piece of information Tom. I'm going to go into my lab and start watching this.

I'm going to feel REAL sheepish when I see this table populating. I spent a good amount of time trying to figure out how to find another solution.

Thanks again!

JAustgen · 08-26-2005, 09:49 AM

Quote:

Originally Posted by Nick

VERY nice piece of information Tom. I'm going to go into my lab and start watching this.

I'm going to feel REAL sheepish when I see this table populating. I spent a good amount of time trying to figure out how to find another solution.

Thanks again!

Not to mention my valuable SQL scripting expertise...

Nick · 08-26-2005, 09:51 AM

Tom,

I was looking at the table and thinking about it for a bit. Since the Evt_Reboot table is populated using Patch Management, it would be seem (to me at least) that this would only report on machines that have an installed patch requiring the machine to reboot.

If a report is needed to show all machines that have not rebooted since "x" date wouldn't using this table exclude systems that didn't require a reboot due to a patch?

I hope that isn't too convoluted.

Thanks,

Nick

JAustgen · 08-26-2005, 09:59 AM

Here's the SQL query I wrote to convert Nick's convulted date/time return from the WMI inventory. The query assumes you're dumping the WMI return into a table in the Altiris database called 'Aex_Reboot.'

declare @date varchar(80)

PRINT '"DATE LAST REBOOTED"'

declare date_cursor CURSOR FOR
select substring(lastreboot, 1, 8)
FROM aex_reboot

OPEN date_cursor

FETCH NEXT FROM date_cursor
INTO @date

WHILE @@FETCH_STATUS = 0
BEGIN
select convert(datetime, @date) as 'Date Last Rebooted'

--Get the next date
FETCH NEXT FROM date_cursor
INTO @date

END

CLOSE date_cursor
DEALLOCATE date_cursor

Tom.Reyntjens · 08-26-2005, 11:00 AM

It's not too convoluted for me. It seems quite reasonable, and it corresponds to what I can see in my database. There are only reboot events in the table for those systems that I patched with altiris, and not for others (with software update agent 1137 or higher), that definitely have rebooted since they received the new software agent.

Nick · 08-26-2005, 11:06 AM

I've just come across some information that I'll be posting as a KB Article. In short it lets you use a Windows 2003 Resource Kit executable to create an ini file. The executable then populates the ini with the reboot date and time.

Should have it available shortly. I'll post in this thread when I'm done.

Nick · 08-26-2005, 11:23 AM

KB Article with information on a way to get this posted up HERE

Nick · 08-26-2005, 11:49 AM

Quote:

Originally Posted by JenH

Nick...thanks...I appreciate it.

One of my collegues used to use "custinvhelper.exe" to build custom inventories. I've heard of it, too, but can't find it. I looked in all of my Manage Fusion archives and I didn't find it there.

Does it still exist and can someone share it?

Thanks!

Jen

Check this one out Jen. You can get it HERE
password: shared

AltirisAE · 08-26-2005, 01:08 PM

I created this for the 5.5 world, so please remember that NS 6 provides custom schedule flexibility, which will allow you to run the CustInv upon startup, instead of leaving it to run everyday.

Tom.Reyntjens · 09-27-2005, 05:56 PM

The method proposed by Scott has the disadvantage that it requires an extra service to be installed on each box. In certain environments with stringent QA requirements this could be a major problem (and also the idea of having a service just to run one task at startup and then just remain sitting there, is not really elegant, it seems to me at least).

An alternative would be to use WMI to query for the last boot time as Jen's custom inventory does, and schedule to inventory task at system startup (this requires NS 6.0 of course). Two problems remain with the WMI query.

(1) the date format : the last reboot time is reported by WMI in a format that doesn't allow date/time calculations in SQL queries. Moreover, for some reason, inventory solution reports the date in different formats for different OSes (at least the format is different for Windows 2000 [yyyy-mm-ddTHH-MM-SS] and WinDOWS 2003 [yyyymmddHHMMSS.mmmmmmsUUU]. This can be fixed using a query to convert the date format.
SELECT _ResourceGuid,
CASE
WHEN LASTBootUpTime='' THEN NULL
WHEN SUBSTRING (LastBootUpTime,11,1) = 'T' THEN CONVERT (Datetime, LastBootUpTime, 126)
ELSE CONVERT(Datetime, SUBSTRING(LastBootUpTime,1,4) + '-' + SUBSTRING(LastBootUpTime,5,2) + '-' + SUBSTRING(LastBootUpTime,7,2) + 'T' + SUBSTRING(LastBootUpTime,9,2) + ':' + SUBSTRING(LastBootUpTime,11,2) + ':' + SUBSTRING(LastBootUpTime,13,2), 126)
END AS 'Boot'
FROM AeX_OS_LastBootUpTime
(2) another problem with the WMI query is that on Windows 2000 machines, it doesn't always return correct results because Windows 2000 WMI doesn't handle time zones correctly for LastBootUpTime which can lead to a deviation of several hours (unless a hotfix is installed see http://support.microsoft.com/default...b;en-us;825666). Either the hotfix has to be installed, or we should find an alternative query. A possible alternative is the creation date of a system process to is always available when Windows is running. SMSS.exe is such a process
Querying for the creation date of that process can be done using the following WMI custinv :
<%foreach wmiobject="o" namespace="ROOT\CIMV2" wql="Select * from Win32_Process where name='smss.exe'"%>
<z:row
c0="<%writexml "wmi:o.CreationDate"%>"
/>
<%next%>
Of course, this doesn't give you the exact boot time (it will be typically 30s - 1m off the mark), but for most purposes it will do. But the hotfix is even better of course.

Nick · 09-27-2005, 07:17 PM

Tom,

Would you mind if I took the information you provided and made a KB Article out of it?

JenH · 09-27-2005, 07:51 PM

Tom,

Your query works great to convert the time, however, that's the only column that appears. Do I need to link this query somehow with my LastBootUpTime Report or can you modify it to list the machine name as well?

I can see it even expanding where we'd want to see the Primary User but only for the current month. That way I can generate an e-mail list reminding clients to reboot after a set period of time.

Thank again for your help.

Jen

Nick · 09-27-2005, 11:58 PM

I played around with Tom's SQL query Jen and got at least a start for you.

SELECT T0._ResourceGuid,
T1.[Name],
CASE
WHEN LastBootUpTime=' '
THEN NULL
WHEN SUBSTRING (LastBootUpTime,11,1) = 'T'
THEN CONVERT (Datetime, LastBootUpTime, 126)
ELSE CONVERT(Datetime, SUBSTRING(LastBootUpTime,1,4) + '-' + SUBSTRING(LastBootUpTime,5,2) + '-' +
SUBSTRING(LastBootUpTime,7,2) + 'T' + SUBSTRING(LastBootUpTime,9,2) + ':' + SUBSTRING(LastBootUpTime,11,2) + ':' +
SUBSTRING(LastBootupTime,13,2), 126)
END AS 'Last Reboot'
FROM [Inv_AeX_Reboot] T0
INNER JOIN [Inv_AeX_AC_Identification] T1 ON T1.[_ResourceGuid] = T0.[_ResourceGuid]