Solved

Vbscript : Last Logon/Log off Time

Posted on 2009-07-16
10
679 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
  • 5
  • 5
10 Comments
 
LVL 10

Accepted Solution

by:
Alan_White earned 500 total points
Comment Utility

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
Comment Utility
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
Comment Utility
The time of the "input date" or the system date is?
0
 
LVL 10

Expert Comment

by:Alan_White
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Assisted Solution

by:Alan_White
Alan_White earned 500 total points
Comment Utility
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
Comment Utility
Alan Thank You once again. The output is in minutes.
0
 
LVL 1

Author Closing Comment

by:raju1706
Comment Utility
Thank You appreciate it.
0
 
LVL 10

Expert Comment

by:Alan_White
Comment Utility
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
Comment Utility
Alan, Thank You once again.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now