In

  • 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

  • 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

  • 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.

  • 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.

  • 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

  • Thank you Phill.

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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