Vbscript : Last Logon/Log off Time

I have a vbscript to retrieve mailbox information, when I retrieve the information it is in the following format 20090716080200.000312+*** , I need to convert it to MM/DD/YYYY HH:MM:SS format.

Thank You

'
Dim SWBemlocator
Dim objWMIService
Dim colItems
Dim objFSO
Dim objFile
strTitle="Mailbox Report"
strComputer = InputBox("What Exchange server do you want to check?",strTitle,"TANK")
UserName = ""
Password = ""
strLog="MailboxReport.csv"
Set objFSO=CreateObject("Scripting.FileSystemObject")
Set objFile=objFSO.CreateTextFile(strLog,True)
 
'This query will fail with the Exchange WMI Provider
'strQuery="Select ServerName,StorageGroupName,StoreName,MailboxDisplayName,Size,TotalItems from Exchange_Mailbox"
strQuery="Select * from Exchange_Mailbox"
objFile.WriteLine "Server,StorageGroup,MailStore,User,Size(KB),TotalItems,LastLogonTime,LastLogoffTime"
WScript.Echo "Examining " & strComputer
Set SWBemlocator = CreateObject("WbemScripting.SWbemLocator")
Set objWMIService = SWBemlocator.ConnectServer(strComputer,"\root\MicrosoftExchangeV2",UserName,Password)
Set colItems = objWMIService.ExecQuery(strQuery,,48)
 
For Each objItem in colItems
	objFile.writeline objItem.ServerName & "," & objItem.StorageGroupName &_ 
	"," & objItem.StoreName & "," & Chr(34) &objItem.MailboxDisplayName & Chr(34) &_
	"," & objItem.Size & "," & objItem.TotalItems & "," & Chr(34) & objItem.LastLogonTime & Chr(34) & "," & Chr(34) & objItem.LastLogoffTime & Chr(34)
	
	'uncomment if you want to write to the screen
	'WScript.echo objItem.ServerName & "," &objItem.StorageGroupName &_
	'"," & objItem.StoreName & "," & CHR(34) & objItem.MailboxDisplayName & Chr(34) &_
	'"," & objItem.Size & "," & objItem.TotalItems
Next
 
objFile.close
 
WScript.Echo "See " & strLog & " for results."

Open in new window

LVL 1
raju1706Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan_WhiteCommented:

WScript.Echo convertDate("20090716080200.000312")
 
Function convertDate(sInputDate)
 
	sYear  	 = Left(sInputDate,4)
	sMonth 	 = Mid(sInputDate,5,2)
	sDay   	 = Mid(sInputDate,7,2)
	sHour  	 = Mid(sInputDate,9,2)
	sMin  	 = Mid(sInputDate,11,2)
	sSeconds = Mid(sInputDate,13,2)
 
	convertDate = sMonth &"/"& sDay &"/"& sYear &" "& sHour &":"& sMin &":" &sSeconds
 
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
raju1706Author Commented:
Alan Thank You for the script, but the time is offset by GMT. The above time is +6 so I have to -6 based on GMT. So how do I get the correct time of local server ?
0
Alan_WhiteCommented:
The time of the "input date" or the system date is?
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Alan_WhiteCommented:
OK, I think I've figured it out.
I originally thought it was just a matter of adding or subtracting 6 from the sHour variable but that wouldnt work if the hours were close to the next or previous day.
So in the script I posted, I was just putting the date time string out as a string.  Now we need to convert it to an actual date so that we can then add (or subtract) 6 hours from it using the dateadd function.
The problem is that the system locale might cause your input date to be incorrect (UK vs US formats).
Have a look at below and see if it makes sense.
 

WScript.Echo convertDate("20090716200200.000312")
 
Function convertDate(sInputDate)
 
	Dim sYear, sMonth, sDay, sHour, sMin, sSeconds
	Dim sDateString, sDate, sNewDate
 
	Const cGMTOffset = -6
	
	sYear  	 = Left(sInputDate,4)
	sMonth 	 = Mid(sInputDate,5,2)
	sDay   	 = Mid(sInputDate,7,2)
	sHour  	 = Mid(sInputDate,9,2)
	sMin  	 = Mid(sInputDate,11,2)
	sSeconds = Mid(sInputDate,13,2)
 
	'sDateString = sMonth &"/"& sDay &"/"& sYear &" "& sHour &":"& sMin &":" &sSeconds 'US Format
	sDateString = sDay &"/"& sMonth &"/"& sYear &" "& sHour &":"& sMin &":" &sSeconds 'UK Format
	
	sDate = CDate(sDateString)
	sNewDate = DateAdd("h",cGMTOffset,sDate)
	
	convertDate = FormatDateTime(sNewDate,0)
 
End Function

Open in new window

0
raju1706Author Commented:
Thank You Alan, I will award the points but I have one last query, from the server time can we dynamically get the cGMTOffset, as I will be querying servers across the globe I cannot have a constant cGmtOffset.

0
Alan_WhiteCommented:
I had to google that one.
I have found the script below and it seems to work, however my TZ is set to GMT so it's reporting 0.  I therefore dont know exactly what the output is going to look like.  Maybe you can try this script in isolation and tell me what you get?
If it's in minutes, we will need to change the parameter in the dateadd function.

wscript.Echo getTimeOffset
 
Function getTimeOffset
 
	strComputer = "."
	
	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
	
	Set colTime = objWMIService.ExecQuery("Select * From Win32_TimeZone")
	
	For Each objTime in colTime
	    intBias = objTime.Bias
	Next
	
	getTimeOffset = intBias
	
End Function

Open in new window

0
raju1706Author Commented:
Alan Thank You once again. The output is in minutes.
0
raju1706Author Commented:
Thank You appreciate it.
0
Alan_WhiteCommented:
Raju1706:
As the output is in minutes, you need to change the line in the convertDate Function to
sNewDate = DateAdd("n",cGMTOffset,sDate)
Note the "n" instead of "h", this tells it you want to add/subtract minutes instead of hours.
I assume you have figured out how to use all these component functions together?
Cheers,
Alan
0
raju1706Author Commented:
Alan, Thank You once again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.