Collection of last reboot? - Page 4

kschroeder · 02-05-2009, 03:06 PM

tnyitray,
I added the sections in Red below to update and add the Primary user.

Code:

 
SELECT T0._ResourceGuid, 
T1.[Name], vc.[User] AS [Primary User], vc.Domain,
T0.[Last Reboot]
FROM (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 'Last Reboot'
FROM [Inv_AeX_Reboot]) T0 
INNER JOIN [Inv_AeX_AC_Identification] T1 ON T1.[_ResourceGuid] = T0.[_ResourceGuid]
LEFT JOIN vComputer vc
ON T0.[_ResourceGuid] = vc.Guid
WHERE DateDiff(day, T0.[Last Reboot], getdate()) > 1
ORDER BY T0.[Last Reboot] ASC

tnyitray · 02-05-2009, 03:07 PM

Thx I will try it out.

Nice to see your still around.

thx again...

tnyitray · 02-05-2009, 03:44 PM

If you have time need one more item:

.[Default Gateway]

which I believe comes from Inv_AeX_AC_TCPIP.

BTW the Primary User and Domain work great.

AltirisAE · 02-08-2009, 12:16 PM

If you look at how vComputer is created, you should be able to use its PU SQL within your report.

SAMI · 04-21-2009, 10:52 AM

Sorry, Where can I find Custom Inventory Configuration utility, please

Thanx
SAMI

AltirisAE · 04-23-2009, 02:24 AM

Hello SAMI, I have attached the builder for your convenience.

SAMI · 04-25-2009, 01:14 AM

Really I appreciate your reply & that's very kind of you,

SAMI

JeffreyJRiggs · 05-27-2009, 11:25 AM

Quote:

Originally Posted by Nick

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.

Soooo this is the current way still?
Im going to give it a try... would like to figure this out...

Thanks
Jeff

JeffreyJRiggs · 05-27-2009, 01:13 PM

Quote:

Originally Posted by Mike

Sure, I don't mind paying a few bucks for good info.

What I'm looking to do is take the custom inventory data that's being pulled from WMI of last bootup time and run a datediff against it.

This would be just like the previous exampls of:

Code:

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]

plus a condition that says something like

Code:

where datediff(dd,lastbootuptime,getdate()) > 30

but it won't work as simple as this because the datediff sees the lastbootuptime as a character value instead of a datetime value, so the CONVERT used above isn't carrying over to the condition.

Thanks for the swift response,

Mike

so this works for me except... i would like to add a column for how many days it has been up...
how do you do that????

help greatly appreciated...

JeffreyJRiggs · 05-27-2009, 01:46 PM

ok so the inventory wasnt importing...

in the lastreboot.ini I had to add the entire path to the nsi... why would that be?
the nsi was getting dropped into the cache directory but the aexcustinv was trying to pull from \express\inventories???

so my ini looks like this..
aexcustinv.exe /in .\LastReboot.xml /o .\ /out "C:\Program Files\Altiris\eXpress\Inventory\LastReboot.nsi"
aexnsinvcollector.exe /hidden /nsctransport /v default /useguid

JeffreyJRiggs · 05-27-2009, 02:56 PM

Quote:

Originally Posted by JeffreyJRiggs

so this works for me except... i would like to add a column for how many days it has been up...
how do you do that????

help greatly appreciated...

ok this works... nicely too.. thanks to our local SQL guru...

SELECT 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',
datediff(day,
Convert(DateTime,
(
CASE
WHEN
LastBootUpTime=' '
THEN
'1/1/1900'
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 )),
GETDATE())
AS 'DAYSUP'
FROM [Inv_AeX_Reboot] T0
INNER JOIN [Inv_AeX_AC_Identification] T1 ON T1.[_ResourceGuid] = T0.[_ResourceGuid]
order by LastBootUpTime ASC