Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Question Unanswered: Local Administrator Report (VB > SQL)

    I wanted to create a VB login script to look at a person's "local administrator" group on a workstation and take the printed information and send it to a SQL database, from there I could create a simple select statement with an ASP page and pull the data as needed.

    I know I will probably receive a lecture on how to learn VB from the ground up but unfortunately I will never consider myself a programmer because my mind does not logically work that way, hence way I am on the systems side.

    I caught a code snippet from the link below but I don't have a clue on how to print the necessary information except for the following DOS commands.

    -----
    echo %computername% >> C:\test.txt
    net localgroup Administrators >> C:\test.txt
    -----

    So in short I want pull the contents of the local administrators group during login and push it into a database and overwrite the records each time someone logs in. Any help and patience would simply be wonderful...:-)

    VB to SQL
    http://www.dbforums.com/visual-basic...-vbscript.html

    VB to a Text File
    Local Admin audit on workstations

    -lo

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Unfortunately this all falls squarely on the systems side of the fence. Scripting (wmi, ad, powershell, etc) is increasingly critical for network engineers and administrators, but I digress...

    What is your overall goal here? What question are you trying to answer and why? There is a good chance you can make use of off-the-shelf products (free and otherwise) if basic monitoring and reporting is what you're after.

    Also, if you have a domain then this is information you could get from an AD query against the target machines, on demand...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2010
    Posts
    3
    Unfortunately, with today's economy there is less and less of a budget for IT and tools. So we need to look at building a very basic login script to look at a user's local administrator group on their workstation and place the contents of that group into a database. I commented out the first script I came across, I'm just confused on how to pull the data from the workstation and import it into the SQL table.

    Yes we run an active directory domain and we would tie this login script to each user's GPO to gather the necessary data so the help desk can see who has local administrator rights on their workstation.

    I ran the following and it does sort of what I need, but it gives too much data, it would be cleaner in VB and writing the data to a SQL database.

    ---
    REM #test.bat
    echo %computername% >> C:\test.txt
    net localgroup Administrators >> C:\test.tx
    ----


    Function WriteToDatabase(sUserName)
    'I see the variable
    Dim mConnection

    'The connection to the database
    Set mConnection = CreateObject("ADODB.Connection")
    mConnection.Open "Provider=SQLOLEDB.1;Data Source=myServerName;Initial Catalog=myDatabaseName","myUserName","myPassword"

    'This is where i get confused:\ If i can extract the data with a previous script how do i import into into the table?
    mConnection.Execute "INSERT INTO tbl_user_log (user_name, login_date) VALUES ('" & sUserName" & ", GetUTCDate())"

    Set mConnection = Nothing

    End Function

    'This will write the username and current GMT date/time to a database table called tbl_user_log, with the fields user_name (VARCHAR) and login_date (DATETIME).

    'If you want to retrieve values, use a recordset object such as:

    Set mRecordset = CreateObject("ADODB.Recordset")
    mRecordset = "your SQL command here", mConnection

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    As you have noticed, it is very messy to try to extract meaningful data using the method you are currently pursuing...

    I HIGHLY recommend scripting against ad/winnt as opposed to trying to push around text files.

    For example, if you're physically executing the script on the target machine of interest, you can do something like this to get a collection of users:

    ' Do this so you can get the name of the computer
    set wShell= CreateObject("WScript.Shell")
    localComputer = wShell.ExpandEnvironmentStrings("%ComputerName %")

    ' Ask for a collection of users in the administrators group
    Set groupOfAdmins = GetObject("WinNT://" & localComputer & "/Administrators")

    Then you can rip through the group and cherry pick just the stuff you want to put in to your sql database:

    For Each adminGroupMember In groupOfAdmins.Members
    ' Build your sql command here
    Next


    At this point, "adminGroupMember" is an ad user and you can access all of the properties listed in the second half of this page:

    IADsUser Interface (Windows)


    I think you'll find this much less painful than trying to work with the text output of a NET command.

    If you're dead set on continuing down the current path, then we get in to string parsing. I'm pretty sure you don't want to go there...

    **Note I didn't explicitly test this, I just happened to have some snippets laying around. After all, I'm a programmer so most of this scripting stuff is pretty foreign to me. I'd have to ask my systems/network admin contacts for anything more complex, they do this kind of stuff all day long...
    Last edited by Teddy; 03-21-10 at 16:43.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2010
    Posts
    3
    You have stepped me in the right direction and I appreciate all of the help! I will post my progress and questions as I come across them!!

    Thanks Again..
    Last edited by -lodogg-; 03-23-10 at 23:16.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •