Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


Grabbing website data and inserting into DB

  		Logged in as: Guest
arrSession:exec spGetSession 2,2,42352
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Grabbing website data and inserting into DB
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: [1]
Login
Message << Older Topic   Newer Topic >>
Grabbing website data and inserting into DB - 1/23/2007 1:52:54 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		I had a previous script on here that grabs computer information from Active Directory and inserts it into an Access DB.  Now my boss wants Warranty information from Dell's website to be done automatically as well. 

I found a script that would pull the information, but how do I get it into the DB?

Here is the script I found....

I would use the code tags, but they suck and hard to read

'===============================================================================
'= Pull the service tag from the bios
'===============================================================================

strComputer = " . "

Set objWMIService = getObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colBIOS = objWMIService.ExecQuery ("Select * from Win32_SystemEnclosure")

For Each objBIOS in colBIOS
'Wscript.Echo "Serial Number of " & strComputer &;_

' " is : " & objBIOS.SerialNumber

strServiceTag = objBIOS.SerialNumber

Next

'===============================================================================
'= Create Internet Explorer Object
'===============================================================================

Set Explorer = WScript.createObject("InternetExplorer.Application", "IE_")

Do While (Explorer.Busy): Wscript.Sleep 250: loop

Explorer.ToolBar = 0 ' hidden
'Explorer.ToolBar = 0 ' visible

Explorer.StatusBar = 1 ' visible

Explorer.Width = 800: Explorer.Height = 600

'Explorer.Left = 100: Explorer.Top = 50

Explorer.left = 225: Explorer.Top = 0
Explorer.Visible = 1 ' visible

Explorer.Navigate "https://support.dell.com/support/topics/global.aspx/support/my_systems_info/en/details?c=us&cs=555&l=en&s=biz&amp;amp~tab=2&ServiceTag="& strServiceTag

Do While (Explorer.Busy): Wscript.Sleep 250: loop

==============================================================
And I want to add it to this.....
=============================================================

Const adOpenStatic = 3

Const adLockOptimistic = 3

Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Workstations" , objConnection, _
adOpenStatic, adLockOptimistic

'================================================================================================
' Retrieve Computer Account Names from Active Directory by Operating System Name
'===============================================================================================

Const ADS_SCOPE_SUBTREE = 2

Set objConnectionAD = CreateObject("ADODB.Connection")

Set objCommandAD = CreateObject("ADODB.Command")
objConnectionAD.Provider = "ADsDSOObject"
objConnectionAD.Open "Active Directory Provider"

Set objCommandAD.ActiveConnection = objConnectionAD
objCommandAD.CommandText = "Select Name from 'LDAP://DC=corp-nj,DC=mrsassociates,dc=com'" _
& "WHERE objectClass='Computer'"
objCommandAD.Properties("Page Size") = 1000
objCommandAD.Properties("Searchscope") = ADS_SCOPE_SUBTREE

Set objRecordSetAD = objCommandAD.Execute
objRecordSetAD.MoveFirst

Do Until objRecordSetAD.EOF
GetPCInfo objRecordsetAD("Name")
objRecordsetAD.MoveNext

Loop




Function GetPCInfo(strComputer)
' ==================================================================================================

' Grabs Computer Name, Manufacturer, Model, and Installed Ram from the ComputerSystem WMI Service

'===================================================================================================

On Error Resume Next

Set objWMIService = GetObject("winmgmts:\\" & strComputer& "\root\cimv2")
If Err.Number <> 0 Then

On Error GoTo 0
Exit Function

End If

On Error Goto 0
objRecordset.AddNew
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")
For Each objItem in colItems
objRecordset("ComputerName") = objItem.Name
objRecordset("Manufacturer") = objItem.Manufacturer
objRecordset("Model") = objItem.Model

objRecordset("Installed Ram") = objItem.TotalPhysicalMemory /1024
objRecordset("CPU Count") = objItem.NumberofProcessors
Next


' =========================================================================

' Queries the Processor WMI Service to grab the Processor Name

' =========================================================================


Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Processor")
For Each objItem in ColItems
objRecordset("CPU Name") = objItem.Name
Next


'==================================================

' Grabs Serial Number

'==================================================


Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_SystemEnclosure")
For Each objItem in colItems
objRecordset("Serial Number") = objItem.SerialNumber

Next

'====================================================================================================

' Queries LogicalDisk WMI Service to retrieve Hard Drive Size and Free Space

'====================================================================================================


Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_LogicalDisk")
For Each objItem In ColItems
objRecordset("Hard Drive Size") = objItem.Size
objRecordset("Hard Drive Free Space") = objItem.FreeSpace
Next


'====================================================================================================

' Queries the BIOS WMI Service to retrieve the BIOS Version

'====================================================================================================


Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_BIOS")
For Each objItem in colItems
objRecordset("BIOS Version") = objItem.Name
Next

'==========================================================================

' Retrieve Operating System and Service Pack Version

'==========================================================================

Set colItems =objWMIService.ExecQuery("SELECT * FROM Win32_OperatingSystem")
For Each objItem in colItems
objRecordset("Operating System") =objItem.Caption
objRecordset("Service Pack") = objItem.ServicePackMajorVersion & "." & _
objItem.ServicePackMinorVersion
Next

'=====================================================================================================

' Queries the PhysicalMemory WMI Service to retrieve the Memory Type

'=====================================================================================================


Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_PhysicalMemory")
For Each objItem in ColItems
objRecordset("RAM Speed") = objItem.Speed
'objRecordset("RAM Capacity") = objItem.Capacity pulls capacity of ram chip, not motherboard capacity

Next


Set ColItems = objWMIService.ExecQuery("SELECT * FROM Win32_PhysicalMemoryArray")
For Each objItem In colItems
objRecordSet("RAM Capacity") = objItem.MaxCapacity
Next



Dim strInvDateS

Dim strInvTimeS
strInvDateS = Date
strInvTimeS = Time
objRecordSet("Inventory Date") = strInvDateS
objRecordSet("Inventory Time") = strInvTimeS

' =========================================================================================

' Queries NetworkAdapterConfiguration WMI Service to get the IP Address

' Only if TCP/IP is running on target Computer

' =========================================================================================


Set IPConfigSet = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
For Each IPConfig in IPConfigSet
If Not IsNull(IPConfig.IPAddress) Then
For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)
IPAddress = IPConfig.IPAddress(i)
objRecordset("IP Address") = IPAddress
objRecordset("MAC Address") = IPConfig.MACAddress

Next


End If


Next

objRecordset.Update

'=================================================================================
' Closes Recordset and Connection
'=================================================================================

Set colItems = Nothing

Set objWMIService = Nothing
End Function


< Message edited by twilliamsen -- 1/23/2007 1:57:04 AM >
 
 
Post #: 1
 
RE: Grabbing website data and inserting into DB - 1/23/2007 2:42:08 AM   
  dm_4ever


Posts: 2721
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline 		Is the information you want from the web site enclosed in tag's with an id (html code)?

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)
 
 
Post #: 2
 
RE: Grabbing website data and inserting into DB - 1/23/2007 3:26:07 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		Yes.  The output is this:


      

I need the End Date, the latest one preferred

(in reply to dm_4ever)
 
 
Post #: 3
 
RE: Grabbing website data and inserting into DB - 1/23/2007 3:30:34 AM   
  dm_4ever


Posts: 2721
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline 		Is this the HTML code or what you see in IE? Also, you may take a look at this. It allows you to retrieve the HTML code of a page through VBScript.: http://www.microsoft.com/technet/scriptcenter/scripts/default.mspx?mfr=true
What I'm hoping is that the information you want is enclosed in an html tag with an ID or Name so you can get its value once you open the page through the IE object.

< Message edited by dm_4ever -- 1/23/2007 3:34:21 AM >

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)
 
 
Post #: 4
 
RE: Grabbing website data and inserting into DB - 1/23/2007 3:33:55 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		No...  I tried to get it, but it is not easy to read....

If you need the whole page let me know... There maybe something in there that I am missing
here it is in all its glory


      

(in reply to dm_4ever)
 
 
Post #: 5
 
RE: Grabbing website data and inserting into DB - 1/23/2007 3:41:33 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		Here is the whole website.  I just need the latest date out it. 


      

(in reply to twilliamsen)
 
 
Post #: 6
 
RE: Grabbing website data and inserting into DB - 1/23/2007 4:40:37 AM   
  dm_4ever


Posts: 2721
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline 		Well I tried this and seems to pull the dates. Not sure how accurate it is. Maybe ebgreen can take a look at the Regular Expression piece, since this is very new to me(maybe my 4th or 5th attempt creating my own pattern).

In the HTML code it appears the end date information was located with the following tags so I just tried looking for those to get the date. That's where the regular expression comes in.

      

Following worked for me at least with the html page provided:

      

< Message edited by dm_4ever -- 1/23/2007 4:57:21 AM >

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)
 
 
Post #: 7
 
RE: Grabbing website data and inserting into DB - 1/23/2007 5:47:48 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		Would I be able to add that to my inventory script?  Especially with the Option Explicit statement.  Or should I have the script run, then have it run again against the DB with the Serial numbers? 

(in reply to dm_4ever)
 
 
Post #: 8
 
RE: Grabbing website data and inserting into DB - 1/23/2007 6:34:50 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		Found one thing...  How would I add another RegEx expression to look for?

(in reply to twilliamsen)
 
 
Post #: 9
 
RE: Grabbing website data and inserting into DB - 1/23/2007 6:39:07 AM   
  ebgreen


Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online 		I would suggest reading the script that was provided and trying to understand how it works. Then make another function using the provided function as a guide to get the new piece of information.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)
 
 
Post #: 10
 
RE: Grabbing website data and inserting into DB - 1/23/2007 6:49:46 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		Good idea,  Let me take a peek and create another function and see if I can get my brain to melt 

(in reply to ebgreen)
 
 
Post #: 11
 
RE: Grabbing website data and inserting into DB - 1/23/2007 7:07:36 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		Ok figured it out, wrote another function, but how do I take the results and stick them in the db?

do I use key?


(in reply to twilliamsen)
 
 
Post #: 12
 
RE: Grabbing website data and inserting into DB - 1/23/2007 7:21:24 AM   
  dm_4ever


Posts: 2721
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline 		The example I posted returns two dates since there are two End date on the page. You first need to get the latest date and then use that info however you want.

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to twilliamsen)
 
 
Post #: 13
 
RE: Grabbing website data and inserting into DB - 1/23/2007 7:25:37 AM   
  twilliamsen

 

Posts: 208
Score: 0
Joined: 1/18/2007
Status: offline 		call me stupid, but how would I do that?  I haven't used VBScript in 10 years and wasn't very good at it then.  I am a bit rusty and I think I got overpowered with this task.  I don't want you to do it for me, but give me an idea on what am I suppose to use next?

Should this script be running after the inventory is done? or during?  Roll it into one large script or 1 script then at the end, then fire off the warranty script?

Getting the data in there first would be priority, getting it to behave properly is another.

(in reply to dm_4ever)
 
 
Post #: 14