Collection of last reboot? - Page 2

Nick · 09-28-2005, 12:05 AM

Bah...

I used the tables in my database... Let me rewrite the query to use what Tom was using.

SELECT _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 'Boot'
FROM AeX_OS_LastBootUpTime T0
INNER JOIN [Inv_AeX_AC_Identification] T1 ON T1.[_ResourceGuid] = T0.[_ResourceGuid]

See if that doesn't work for you.

Nick · 10-02-2005, 06:23 PM

I have tweaked this report a little more over the weekend. I've added a defined field for how long since machines have rebooted.

This means you can run the report to show machines that haven't rebooted in the last x amount of days.

This report requires running a custom inventory to collect last reboot time.

tnyitray · 11-10-2005, 11:17 AM

This report seem to be what I'm looking for. Should I use it with JenH custom inventory task to collect the info? There were a few discussed in this thread. And I assume I could use the same steps for creating the actual task, from the steps in custom registry etrust thread.

Nick · 11-10-2005, 01:00 PM

Yes, this report requires a custom inventory be run so that a table will be created and populated for the report to run against. I would suggest having the custom inventory run on each machine at startup so that you get the latest reboot time as quickly as possible.

If you have problems let me know. I've been using this for awhile and it works like a charm.

tnyitray · 11-14-2005, 07:23 PM

Can't seem to get this one working, can you provide me with your custom inventory task. It would real help me out.

Thanks again...

Nick · 11-14-2005, 09:42 PM

Here you go.

tnyitray · 11-15-2005, 01:41 PM

Getting closer but still not there.

I created AeXInvSolnAdm7.ini with:
aexcustinv.exe /in Inventory (Last Reboot).xml /out Inventory (Last Reboot).nsi
aexnsinvcollector.exe /hidden /nsctransport /v default /useguid

Put this and Inventory (Last Reboot).xml into:into the: \\<servername>\nscap\bin\win32\x86\Inventory Solution directory

Created a task and I see that task running on my local machine. But when I run your Last Reboot report I get:

Failed to generate the report. This report is based upon data that is not present in your database. You must load the associated data into your database before you will be able to see any report results.
Really trying to get this working, any more help would be great again! Sorry little new at these custom inventories.

Nick · 11-15-2005, 02:28 PM

Sounds like the report is looking for a wrong table name.

Check the report to see what table is being queried then check your database for the table the custom inventory is creating.

If they are not the same I would change the name of the table in the report.

tnyitray · 11-15-2005, 07:11 PM

I'm using both of your files, the last boot report above and the custom inventory task in past posts above too.

Here the inv:

<item guid="{3a902adb-31b3-41fe-9ead-551818ed2954}" classGuid="{16959b6c-4a21-42fc-bb7c-f8f515df3fc9}">


<name>Inventory (Last Reboot)</name>
<alias />
<productGuid>{cd39b720-f871-11d2-8643-00104b74a9df}</productGuid>
<itemAttributes>Normal</itemAttributes>
<itemLocalizations>
<culture name="">
<description>Run machine inventory on a daily schedule.</description>
<name>Inventory (Last Reboot)</name>
</culture>
<culture name="en">
<description>Run machine inventory on a daily schedule.</description>
</culture>
</itemLocalizations>
<enabled>True</enabled>
<packageGuid>{00000000-0000-0000-0000-000000000000}</packageGuid>
<programGuid>{4ee285a1-1a36-4516-a719-97b0b63a703b}</programGuid>
<displayName />
<displayDescription />
<priority>2</priority>
<sourceSite>Unnamed</sourceSite>
<validPeriod validDatesAreUTC="false" />
<clientSchedule allowIndependentExecution="true" allowScheduleRetry="true">
<Schedule IndependentExecution="true">
<Trigger Type="6" Duration="0" Interval="0" KillAtEnd="0" Disabled="0" Description="Run at system startup">
<BeginDate>2000-01-01 02:00:00</BeginDate>
</Trigger>
<Trigger Type="-1" Description="" />
</Schedule>
</clientSchedule>
<statusEvents enabled="true" />
<originalNS>w2k3-vm2.Nick.Altiris</originalNS>
<originatorType>NSInternal</originatorType>
<download useClientDefaults="true" option="always" multicast="Enable" minDownloadSpeed="1" minExecuteSpeed="1" requiresUserActivation="false" notifyWhenAvailable="false" removeAfterRun="false" immediately="true" notifyBeforeExecution="false" maxExecutionDeferral="0" />
<RecurringSchedule>False</RecurringSchedule>
<wakeOnLAN>False</wakeOnLAN>
<tickle>False</tickle>
<recovery />
<NewString>test</NewString>
<parentFolderGuid>06ff6295-3ade-4703-9a22-43a5928b818c</parentFolderGuid>
<security owner="@APPLICATION_ID" inherit="True">
<aces>
<ace trustee="@APPLICATION_ID">
<permissionGrants>
<permissionGrant guid="{ac296df1-eb40-4592-899f-25d5c07d45f6}" />
<permissionGrant guid="{819dae1e-b1a5-4643-81a1-26ef95feb8a8}" />
<permissionGrant guid="{0b094f39-caa2-468f-af84-4670a5c8493f}" />
<permissionGrant guid="{983a2d22-7a82-4db0-a707-52c7d6b1441e}" />
<permissionGrant guid="{eca6254f-5017-4730-9b3f-5add230829b7}" />
<permissionGrant guid="{726b1c09-7108-450d-ae24-5f8e93135ed6}" />
<permissionGrant guid="{4ddc04c3-f0a5-4e88-84aa-c44c8c5ebcc4}" />
<permissionGrant guid="{24feda4a-9025-401f-befd-cc9c9e99f047}" />
</permissionGrants>
</ace>
</aces>
</security>
<itemReferences>
<itemReference guid="{b726c255-a66c-4c00-9b74-3ae68ae63c98}" hint="inventoryadvertisement uses inventoryprogram" type="RelatedChild" />
<itemReference guid="{07accebc-bf86-4bf2-8a48-59a657ecb8e1}" hint="policyappliestocollection" type="RelatedChild" />
<itemReference guid="{7bf5378b-6306-4fea-91f9-59f4488cd60f}" hint="delegatemessagestoitems" type="SingletonChild" />
<itemReference guid="{4ee285a1-1a36-4516-a719-97b0b63a703b}" hint="swdadvertisement uses swdprogram" type="RelatedChild" />
<itemReference guid="{ce1a070d-19f0-4825-8f8b-e6db7fd3fd2f}" hint="policyappliestocollection" type="RelatedChild" />
</itemReferences>
</item>

And here the report I'm using:

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]
WHERE CAST(substring(T0.[LastBootUpTime], 1,8) AS DateTime) < getdate() - %DateParameter%
ORDER BY T0.[Last Reboot] ASC

Finally here the ini:
aexcustinv.exe /in Inventory (Last Reboot).xml /out Inventory (Last Reboot).nsi
aexnsinvcollector.exe /hidden /nsctransport /v default /useguid

Nick · 11-15-2005, 09:13 PM

Can you post the contents of the Inventory (Last Reboot).nsi from one of your systems the custom inventory ran on?

tnyitray · 11-15-2005, 10:46 PM

I searched for *.nsi on my lab workstation and the server and can't find any *.nsi files. Even stopped "Enable Notification Server Processing" but didn't find any *.nsi file in the queue, there was one nse2a.tmp file I believe but that about all I could find.

Nick · 11-15-2005, 11:41 PM

Ok, I did a test of your command line:
aexcustinv.exe /in Inventory (Last Reboot).xml /out Inventory (Last Reboot).nsi

And it errored out. I had to change it to
aexcustinv.exe /in LastReboot.xml /out LastReboot.nsi

Once you run that you should see a LastReboot.nsi show up in the c:\Program Files\Altiris\Express\Inventory directory.

See if that doesn't help you out.

Tom.Reyntjens · 11-16-2005, 10:16 AM

Apart from Nick's remarks, you should also check your query. I've noticed you're querying AeX_Evt_Reboot. If you are using Jen's custom inventory, you should use AeX_OS_LastBootUpTime instead. That's the table that gets populated by the custom inventory.

tnyitray · 11-16-2005, 09:04 PM

Ok getting still closer again, made the changes and now I see LastReboot.nsi in the Program Files\Altiris\eXpress\Inventory

Something is still wrong I guess since this is the only nsi I see there. All the rest have *.bak. Also can't find this information when trying to do a report. Not really sure where to look, I tried creating a new simple report with computer then ? I don't see any last reboot fields.

Here it is open in WORD:
<item guid="{3a902adb-31b3-41fe-9ead-551818ed2954}" classGuid="{16959b6c-4a21-42fc-bb7c-f8f515df3fc9}">





<name>LastReboot</name>

<alias />

<productGuid>{cd39b720-f871-11d2-8643-00104b74a9df}</productGuid>

<itemAttributes>Normal</itemAttributes>

<itemLocalizations>

<culture name="">

<description>Run machine inventory on a daily schedule.</description>

<name>LastReboot</name>

</culture>

<culture name="en">

<description>Run machine inventory on a daily schedule.</description>

</culture>

</itemLocalizations>

<enabled>True</enabled>

<packageGuid>{00000000-0000-0000-0000-000000000000}</packageGuid>

<programGuid>{4ee285a1-1a36-4516-a719-97b0b63a703b}</programGuid>

<displayName />

<displayDescription />

<priority>2</priority>

<sourceSite>Unnamed</sourceSite>

<validPeriod validDatesAreUTC="false" />

<clientSchedule allowIndependentExecution="true" allowScheduleRetry="true">

<Schedule IndependentExecution="true">

<Trigger Type="6" Duration="0" Interval="0" KillAtEnd="0" Disabled="0" Description="Run at system startup">

<BeginDate>2000-01-01 02:00:00</BeginDate>

</Trigger>

<Trigger Type="-1" Description="" />

</Schedule>

</clientSchedule>

<statusEvents enabled="true" />

<originalNS>w2k3-vm2.Nick.Altiris</originalNS>

<originatorType>NSInternal</originatorType>

<download useClientDefaults="true" option="always" multicast="Enable" minDownloadSpeed="1" minExecuteSpeed="1" requiresUserActivation="false" notifyWhenAvailable="false" removeAfterRun="false" immediately="true" notifyBeforeExecution="false" maxExecutionDeferral="0" />

<RecurringSchedule>False</RecurringSchedule>

<wakeOnLAN>False</wakeOnLAN>

<tickle>False</tickle>

<recovery />

<NewString>test</NewString>

<parentFolderGuid>06ff6295-3ade-4703-9a22-43a5928b818c</parentFolderGuid>

<security owner="@APPLICATION_ID" inherit="True">

<aces>

<ace trustee="@APPLICATION_ID">

<permissionGrants>

<permissionGrant guid="{ac296df1-eb40-4592-899f-25d5c07d45f6}" />

<permissionGrant guid="{819dae1e-b1a5-4643-81a1-26ef95feb8a8}" />

<permissionGrant guid="{0b094f39-caa2-468f-af84-4670a5c8493f}" />

<permissionGrant guid="{983a2d22-7a82-4db0-a707-52c7d6b1441e}" />

<permissionGrant guid="{eca6254f-5017-4730-9b3f-5add230829b7}" />

<permissionGrant guid="{726b1c09-7108-450d-ae24-5f8e93135ed6}" />

<permissionGrant guid="{4ddc04c3-f0a5-4e88-84aa-c44c8c5ebcc4}" />

<permissionGrant guid="{24feda4a-9025-401f-befd-cc9c9e99f047}" />

</permissionGrants>

</ace>

</aces>

</security>

<itemReferences>

<itemReference guid="{b726c255-a66c-4c00-9b74-3ae68ae63c98}" hint="inventoryadvertisement uses inventoryprogram" type="RelatedChild" />

<itemReference guid="{07accebc-bf86-4bf2-8a48-59a657ecb8e1}" hint="policyappliestocollection" type="RelatedChild" />

<itemReference guid="{7bf5378b-6306-4fea-91f9-59f4488cd60f}" hint="delegatemessagestoitems" type="SingletonChild" />

<itemReference guid="{4ee285a1-1a36-4516-a719-97b0b63a703b}" hint="swdadvertisement uses swdprogram" type="RelatedChild" />

<itemReference guid="{ce1a070d-19f0-4825-8f8b-e6db7fd3fd2f}" hint="policyappliestocollection" type="RelatedChild" />

</itemReferences>

</item>

Nick · 11-16-2005, 10:43 PM

Ok,

Lets just do it this way.

I have attached a zip file that has all the necessary files for the custom inventory and report.

LastReboot archive should contain the following files.
AeXInvSolnAdm4.ini
Last Reboot Report.xml
LastReboot.xml
Creating Reboot Custom Inventory.doc

Copy AeXInvSolnAdm4.ini to the:
\\servername\NSCap\Bin\Win32\X86\Inventory Solution directory.
Import the Last Reboot Report.xml for a ready made report using this custom inventory data.
Copy LastReboot.xml to the \\servername\NSCap\Bin\Win32\X86\Inventory Solution directory.

Create a custom inventory task to use the:
AeXInvSolnAdm4.ini
LastReboot.xml

This custom inventory will create the Inv_AeX_Reboot table in your database.

tnyitray · 11-17-2005, 02:29 PM

That worked!

BTW please edit above..
AeXInvSolnAdm5.ini change 5 to 4

Thanks again Nick! Now I can start reporting to management who hasn't rebooted in 90 days! lol

Nick · 11-17-2005, 04:11 PM

Good deal. Glad to see it worked for you and edited as noted.

tnyitray · 11-29-2005, 12:20 PM

Report has been working fine for weeks now, so I started to open my collection to a bigger group of workstations. I must have picked up a station who didn't report back good information or something. I now have this error when trying to show a report:

Sql error in query. Error: System.Data.SqlClient.SqlError: Syntax error converting datetime from character string.Sql CommandText: -- MAX ROWCOUNT SET ROWCOUNT 50000 -- 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] WHERE CAST(substring(T0.[LastBootUpTime], 1,8) AS DateTime) < getdate() - 1 ORDER BY T0.[Last Reboot] ASC

Can you fix this??

Thanks Tim...

Nick · 11-29-2005, 07:59 PM

I would have to hazard a guess and say that one of the times returned isn't formatted in a way that the query will seperate it.

Did the systems that you started including have operating systems older than Windows 2000 by chance?

tnyitray · 11-29-2005, 08:09 PM

Yes, some NT4 and W2K, any way of excluding these?

BTW this report has been great, management just loves this one. Great for showing who isn't following directions.

Nick · 11-29-2005, 09:26 PM

I would say that it is the NT4 machines that are not returning a format that is correct.

You should be able to create a collection for NT4 systems and then create that as an exclusion to the report but me personally.... I would love to figure out what is throwing the error and fix it so the report is all inclusive.

Any chance you can look through the table and find one of your NT4 systems and show what the raw date/time looks like?

Ideally do that and then put an XP and 2K date/time with it. This will help to determine what will need to be altered if this is the problem.

tnyitray · 11-29-2005, 09:46 PM

"Any chance you can look through the table and find one of your NT4 systems and show what the raw date/time looks like?"

Not sure how to do this... but I will if you provide me with instructions.

Nick · 11-29-2005, 11:07 PM

I'll see about putting together a query that will return that information tomorrow and put it here.

tnyitray · 12-02-2005, 09:55 PM

You must be busy, so I'll check back again.

Nick · 12-03-2005, 12:04 AM

I have been actually. I got started on creating a vmware nt4 installation so I could check the data myself and got caught up with some last minute closing snafu's on my house on the coast.

Monday is looking like a busy day as well but I'll try to get the NT4 installed and start running some tests. Keep bumping the thread so I don't get side tracked.