?
Solved

Vbscript : Last Logon/Log off Time

Posted on 2009-07-16
10
Medium Priority
?
686 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:raju1706
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 10

Accepted Solution

by:
Alan_White earned 2000 total points
ID: 24868334

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
 
LVL 1

Author Comment

by:raju1706
ID: 24869687
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
 
LVL 10

Expert Comment

by:Alan_White
ID: 24869882
The time of the "input date" or the system date is?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Alan_White
ID: 24870102
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
 
LVL 1

Author Comment

by:raju1706
ID: 24870405
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
 
LVL 10

Assisted Solution

by:Alan_White
Alan_White earned 2000 total points
ID: 24870782
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
 
LVL 1

Author Comment

by:raju1706
ID: 24870980
Alan Thank You once again. The output is in minutes.
0
 
LVL 1

Author Closing Comment

by:raju1706
ID: 31604169
Thank You appreciate it.
0
 
LVL 10

Expert Comment

by:Alan_White
ID: 24876534
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
 
LVL 1

Author Comment

by:raju1706
ID: 24881722
Alan, Thank You once again.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question