September 9, 2003 at 5:44 pm
Hi All,
Could you please help me to find the right solution. I need to collect and analyse the technical information (such as OS, Disc Space, Modem, Software installed etc) from numbers of PCs, they are not in the same network.
Is it possible to use SQL server to retrieve information and put it to the tables? MSDE installed on each PC.
We have tried to run "msinfo32" on each PC and it gives 100 pages of information and it is really hard to select and analyse it.
How can we do it in more efficient way?
Thankyou
September 9, 2003 at 6:53 pm
If you don't want to shell out for an off-the-shelf auditing package, then msinfo is probably a good option.
You can run it from the command line to produce a report. Then you would need to write a script that extracts the relevant info.
eg:
open file
read in a line
look for the string 'OS Name '
if the string is found return the remainder of the line (which would be 'Microsoft Windows 2000 Professional' for Win2k Prof)
Read in another line
etc ...
It would take a bit of work getting the script right, but once that's done you can generate msinfo32 reports for all the computers and then run the script to parse the report and insert the resulting data into a database.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
September 9, 2003 at 8:31 pm
Phill, thank you very much.
It is what I need.
One question (sorry I'm begginer), should I run extracting script in QA?
Could you please give me an example of the script to from the file and extract particular lines using SQL.
Thankyou.
September 9, 2003 at 10:50 pm
Phill, thank you very much.
It is what I need.
One question (sorry I'm begginer), should I run extracting script in QA?
Could you please give me an example of the script to from the file and extract particular lines using SQL.
Thankyou.
September 10, 2003 at 11:22 am
The script would be VBScript so you could run it from the command line, or set it up in DTS or a Scheduled Job.
I can't lay my hands on an example script at the moment, but I'll dig around and see if I can find something similar that I did a while back.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
September 10, 2003 at 4:39 pm
Thank you Phill.
September 14, 2003 at 5:17 pm
Sorry it took so long to dig up, I couldn't remember where I'd kept this.
Function Main()
Dim oFS ' FileSystemObject
Dim oFile ' File object
Dim oConn ' ADO connection object
Dim sLine ' line read from file
' local variables
Dim sIntSec
Dim sCatalog
Dim sSource
' create ADO connection objects
Set oConn = CreateObject("ADODB.Connection")
' open ADO connection using previously stored connection string
oConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<your Database>;Data Source=<your Server>"
' create FileSystemObject
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oFile = oFS.OpenTextFile("<your filename or global variable>", 1)
While oFile.AtEndOfStream = False
sLine = oFile.ReadLine
' skip lines until we get to the line with the text we want
While Instr(1, sLine, "Integrated Security") = 0
sLine = oFile.ReadLine
Wend
' get the bit of text we're interested in
sIntSec = Right(sLine, Len(sLine) - Instr(1, sLine, "="))
' skip lines until we get to the line with the text we want
While Instr(1, sLine, "Initial Catalog") = 0
sLine = oFile.ReadLine
Wend
' get the bit of text we want
sCatalog = Right(sLine, Len(sLine) - Instr(1, sLine, "="))
' skip lines until we get to the line with the text we want
While Instr(1, sLine, "Data Source") = 0
sLine = oFile.ReadLine
Wend
' get the bit of text we want
sSource = Right(sLine, Len(sLine) - Instr(1, sLine, "="))
' build an SQL string to insert the values into the table
sSQL = "INSERT INTO ServersUsed "
sSQL = sSQL & "VALUES ( '" & sSource & "', '" & sCatalog & "', '" & sIntSec & "' )"
' execute sql string
oConn.Execute sSQL
Wend
' release objects that were created
Set oFile = Nothing
Set oFS = Nothing
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
This script was used to read in connection properties from a file and save the results in the database.
The format of the was like this,
Provider=SQLOLEDB.1
Integrated Security=SSPI
Persist Security Info=False
Initial Catalog=<database name>
Data Source=<server name>
and there were about 100 lines in the database which resulted in about 20 records in the database.
From the start of the While loop at the line which reads 'While oFile.AtEndOfStream = False' the remainder is a series of little while loops to advance through the file till a pre-determined text string is found. After that extract the bit of text that is required and move on.
It's important to note that this just scans forward through the file, so you'll need to determine what text items you want to locate and in which order you need to look for them.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 09/14/2003 5:22:21 PM
--------------------
Colt 45 - the original point and click interface
September 15, 2003 at 5:18 pm
Thank you very much Phill.
I've run this code, but it gave me the error message and pointed on this line "
While InStr(1, sLine, "System Name:") = 0
sLine = oFile.ReadLine
"
What is wrong?
Thanks again for help.
Function Main()
Dim oFS ' FileSystemObject
Dim oFile ' File object
Dim oConn ' ADO connection object
Dim sLine ' line read from file
' local variables
Dim sIntSec
Dim sCatalog
Dim sSource
' create ADO connection objects
Set oConn = CreateObject("ADODB.Connection")
' open ADO connection using previously stored connection string
oConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=system;Data Source=sqldev"
' create FileSystemObject
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oFile = oFS.OpenTextFile("c:\admin_doc\msinfo32.txt", 1)
While oFile.AtEndOfStream = False
sLine = oFile.ReadLine
' skip lines until we get to the line with the text we want
While InStr(1, sLine, "System Name:") = 0
sLine = oFile.ReadLine
Wend
' get the bit of text we're interested in
sIntSec = Right(sLine, Len(sLine) - InStr(1, sLine, "="))
' skip lines until we get to the line with the text we want
While InStr(1, sLine, "Initial Catalog") = 0
sLine = oFile.ReadLine
Wend
' get the bit of text we want
sCatalog = Right(sLine, Len(sLine) - InStr(1, sLine, "="))
' skip lines until we get to the line with the text we want
While InStr(1, sLine, "Data Source") = 0
sLine = oFile.ReadLine
Wend
' get the bit of text we want
sSource = Right(sLine, Len(sLine) - InStr(1, sLine, "="))
' build an SQL string to insert the values into the table
sSQL = "INSERT INTO ServersUsed "
sSQL = sSQL & "VALUES ( '" & sSource & "', '" & sCatalog & "', '" & sIntSec & "' )"
' execute sql string
oConn.Execute sSQL
Wend
' release objects that were created
Set oFile = Nothing
Set oFS = Nothing
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
September 15, 2003 at 7:56 pm
I generated a MSinfo32 report on my system here and it doesn't contain the text "System Name:". Therefore the script fails with an 'input past end of file' error.
Check the text file to make sure you have the correct text string.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
September 15, 2003 at 11:29 pm
This is a part of my msinfo32 and I'd like to extract e.g. "System Name", "OS Name", what I need to do?:
System Information report written at: 09/08/03 11:13:16
System Name: AN-220
[System Summary]
ItemValue
OS NameMicrosoft Windows XP Professional
Version5.1.2600 Service Pack 1 Build 2600
OS ManufacturerMicrosoft Corporation
System NameAN-220
System ManufacturerAcer
System ModelVERITON 3500
System TypeX86-based PC
Thank you.
September 16, 2003 at 2:16 am
Try the script without the ':'.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
September 16, 2003 at 5:05 pm
Hi Phill,
I've used different words like "System" ,"System Name"etc - did not work.
While InStr(1, sLine, "System Name") = 0
sLine = oFile.ReadLine
Wend
When I point to "sLine = oFile.ReadLine" on debug mode it gives me "IsLine=[]" (emty box).
When I've changed line InStr(1, sLine, "System Name") = 1, and point on IsLine, it gave me line with unreadable symbols and stops on line
' execute sql string
oConn.Execute sSQL
with error message : Unclosed quotation mark before the character string "yp1"(unreadble symbols).
What do you think can cause this problem?
Thank you
September 16, 2003 at 6:15 pm
On a computer that doesn't have Windows XP, open the file in Notepad. Maybe the file is in unicode format instead of ANSI.
Another thing to try. After the lines,
While oFile.AtEndOfStream = False
sLine = oFile.ReadLine
Put the following on a new line
MsgBox sLine
This should pop up a message box showing the first line as read from the file.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
September 18, 2003 at 5:09 pm
Hi Phill,
Thank you very, very much!!!!
It is working now.
The problem was in file format, it was in unicode.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply