You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » ODBC check using Kix Register User       Forum List       Calendar       FAQ
 Page 1 of 1 1
 Topic Options
 #206827 - 2013-02-28 03:04 PM ODBC check using Kix Thrasonic Fresh Scripter Registered: 2011-08-15 Posts: 26 Loc: GA Hi all. We've used a script or two where I work to create an ODBC connection if a specific one didn't exist. Now I'd like to have a Kix script check to see if a specific ODBC connection is present and if it is to let me know in some way - write it to a text file or something? What's possible? Also, it would be ideal if what the script writes has the username, computer name, etc. Is something like this doable? Top
 #206829 - 2013-02-28 04:51 PM Re: ODBC check using Kix [Re: Thrasonic] Glenn Barnas KiX Supporter Registered: 2003-01-28 Posts: 4285 Loc: New Jersey Welcome to KORG!There are several User Defined Functions (UDFs) related to database access that might help. They are posted here individually in the UDF section or can be found as a single library in the UDF Library of the resources section of my web site.Those UDFs might be a good place to start to determine what can be done, or at least possibly provide ideas for more specific questions.Glenn _________________________ Actually I am a Rocket Scientist! Top
 #206830 - 2013-02-28 05:06 PM Re: ODBC check using Kix [Re: Glenn Barnas] Thrasonic Fresh Scripter Registered: 2011-08-15 Posts: 26 Loc: GA Thanks Glenn. I'll give the UDF a look. Top
 #206831 - 2013-02-28 05:20 PM Re: ODBC check using Kix [Re: Thrasonic] Thrasonic Fresh Scripter Registered: 2011-08-15 Posts: 26 Loc: GA I did a search in the UDF for scripts with ODBC in them and it came up with around a dozen. I looked at a couple of them that seemed like they might be relevant and maybe a little bit here and there was helpful. I'm very new to Kix scripting, and scripting in general, and the guy that wrote the ones we use is no longer here. I'm kind of on my own. Can you give me some guidance on how to dive in and try and find what I'm looking for? Top
 #206832 - 2013-02-28 05:57 PM Re: ODBC check using Kix [Re: Thrasonic] ShaneEP MM club member Registered: 2002-11-29 Posts: 2080 Loc: Tulsa, OK If you are simply wanting to detect the presence of an ODBC connection, the registry is a pretty easy method. Code:$odbcName = "MS Access Database" ; SET THIS TO THE NAME OF THE ODBC YOU WANT TO CHECK FOR$logfile = "\\share\path\to\log\ODBC_LOG.txt" ; SET THIS TO THE PATH OF THE LOG FILE YOU WOULD TO WRITE TO If KeyExist("HKCU\Software\ODBC\ODBC.INI\"+$odbcName)$nul = RedirectOutput($logfile) ? @Date+" - "+@Time+" - "+$odbcname+" Found on "+@WkSta+" under user "+@UserID ; CAN MODIFY THIS TO WHAT EVER YOU WOULD LIKE TO BE LOGGED $nul = RedirectOutput("") Endif Top  #206833 - 2013-02-28 09:35 PM Re: ODBC check using Kix [Re: ShaneEP] Thrasonic Fresh Scripter Registered: 2011-08-15 Posts: 26 Loc: GA Thanks ShaneEP. I want to not only detect the presence of a specific ODBC connection but also have at least the computer name and/or username written to a file somewhere when it's found.Picture this: We have around 500 users and there are some that have a specific ODBC connection that we want to change. I'm thinking we can use a Kix script at logon to identify if a PC has this specific ODBC connection and when it finds a PC that does I want to know what PC was just logged into and what user account was used. Is there a way to have Kix write that information to a file? Also, provided there is a way to do that might there also be a way to tell Kix not to write the values if they're already in the file? For instance, if User A logs into PC A and the ODBC connection is found I want it written to the file, but then when User A logs into PC A again the next day or week I don't want it written again.What do you think? Top  #206834 - 2013-02-28 10:00 PM Re: ODBC check using Kix [Re: Thrasonic] Allen KiX Supporter Registered: 2003-04-19 Posts: 4447 Loc: USA Or just read the registry values you are looking for, and then change them if needed. Top  #206835 - 2013-02-28 10:35 PM Re: ODBC check using Kix [Re: Allen] ShaneEP MM club member Registered: 2002-11-29 Posts: 2080 Loc: Tulsa, OK Thrasonic...The sample script I posted above does exactly what you asked for. Just change the first line to the connection you want to check for, and the second line to the log file in which you want the computer/userid to be logged to when found. Top  #206870 - 2013-03-05 04:12 PM Re: ODBC check using Kix [Re: ShaneEP] Thrasonic Fresh Scripter Registered: 2011-08-15 Posts: 26 Loc: GA Ah, thanks ShaneEP. I'm new to this stuff so I didn't even notice that those two lines were what I was looking for. I'll give this is a test shot and see what happens. I'll keep you posted. Thanks! Top  #206871 - 2013-03-05 04:21 PM Re: ODBC check using Kix [Re: Thrasonic] Mart KiX Supporter Registered: 2002-03-27 Posts: 4642 Loc: The Netherlands Depending on the type of ODB connection it can also be in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INIChanging this line Code:If KeyExist("HKCU\Software\ODBC\ODBC.INI\"+$odbcName) to Code:If KeyExist("HKCU\Software\ODBC\ODBC.INI\" + $odbcName) Or KeyExist("HKLM\Software\ODBC\ODBC.INI\" +$odbcName) would cover both location.Creating two If statements that each check their own registry location would be better IMHO because it will create a log entry for one or both ODBC connections. Edited by Mart (2013-03-05 04:26 PM)Edit Reason: Added some code. _________________________ Mart- Chuck Norris once sold ebay to ebay on ebay. Top
 #206873 - 2013-03-05 11:33 PM Re: ODBC check using Kix [Re: Mart] NTDOC Administrator Registered: 2000-07-28 Posts: 11567 Loc: CA However in the time you're "testing" you could simply go ahead and just write it. In fact years ago there was a post that did some type of testing and writing the change/addition was faster than checking and making a decision to write it. If you need it logged though for sure then you'd need to read and write back what was found. Top
 #206890 - 2013-03-08 09:17 PM Re: ODBC check using Kix [Re: NTDOC] Thrasonic Fresh Scripter Registered: 2011-08-15 Posts: 26 Loc: GA Thanks for all the replies and ideas. Okay, here's the script edited for my environment: Code:$odbcName = "tess_live_readonly" ; SET THIS TO THE NAME OF THE ODBC YOU WANT TO CHECK FOR$logfile = "C:\ODBC_LOG.txt" ; SET THIS TO THE PATH OF THE LOG FILE YOU WOULD TO WRITE TO REM $logfile = "\\fileserver\goldshare\ODBC_LOG.txt" ; SET THIS TO THE PATH OF THE LOG FILE YOU WOULD TO WRITE TO If KeyExist("HKCU\Software\ODBC\ODBC.INI\" +$odbcName) Or KeyExist("HKLM\Software\ODBC\ODBC.INI\" + $odbcName)$nul = RedirectOutput($logfile) ? @Date+" - "+@Time+" - "+$odbcname+" Found on "+@WkSta+" under user "+@UserID ; CAN MODIFY THIS TO WHAT EVER YOU WOULD LIKE TO BE LOGGED $nul = RedirectOutput("") Endif I changed the location of the log file to my local C: because it wasn't working pointing to the fileserver share, but the problem is that it doesn't seem to be working going to my C: drive either. I do have an ODBC connection named tess_live_readonly in the HKLM key but a log file isn't being written. I don't see a problem in the script so what might be the issue? Edited by Mart (2013-03-11 09:13 AM)Edit Reason: Please use code tags when posting code. Top  #206891 - 2013-03-08 10:05 PM Re: ODBC check using Kix [Re: Thrasonic] ShaneEP MM club member Registered: 2002-11-29 Posts: 2080 Loc: Tulsa, OK Well for starters....REM is not a kix command. If you want remark a line out, simply add a semi-colon in front of it. Other than that, are you sure you have rights to create a file in the root of C:\. Try the below and see if it creates the file. Code:$odbcName = "tess_live_readonly" ; SET THIS TO THE NAME OF THE ODBC YOU WANT TO CHECK FOR $logfile = "C:\ODBC_LOG.txt" ; SET THIS TO THE PATH OF THE LOG FILE YOU WOULD TO WRITE TO ;$logfile = "\\fileserver\goldshare\ODBC_LOG.txt" ; SET THIS TO THE PATH OF THE LOG FILE YOU WOULD TO WRITE TO If KeyExist("HKCU\Software\ODBC\ODBC.INI\" + $odbcName) Or KeyExist("HKLM\Software\ODBC\ODBC.INI\" +$odbcName) $nul = RedirectOutput($logfile) @Date+" - "+@Time+" - "+$odbcname+" Found on "+@WkSta+" under user "+@UserID ?$nul = RedirectOutput("") Else $nul = RedirectOutput($logfile) @Date+" - "+@Time+" - "+$odbcname+" NOT Found on "+@WkSta+" under user "+@UserID ?$nul = RedirectOutput("") EndifWhen I simply change the REM to a semi-colon and changed the odbc name to check for.it seemed to work just fine for me. Edited by ShaneEP (2013-03-08 10:06 PM) Top
 #206893 - 2013-03-11 05:31 PM Re: ODBC check using Kix [Re: ShaneEP] Thrasonic Fresh Scripter Registered: 2011-08-15 Posts: 26 Loc: GA Okay this works now. I was admittedly being stupid and found that the batch file I'm using to run the kix script was pointing to a copy of the kix script in my %systemroot% folder, which was not the file I was updating. Duh...Now I need to know if there's a way to have it not update the log file with the information if the same information is already in the file.For instance, I ran the kix script and it populated the log file with my computer name and username, among other things. I waited a minute and ran it again and the same information was added to the log file but with a different time stamp. So the information was doubled, just a different time stamp.Is there a way to keep this from happening? Top
 #206894 - 2013-03-11 05:44 PM Re: ODBC check using Kix [Re: Thrasonic] ShaneEP MM club member Registered: 2002-11-29 Posts: 2080 Loc: Tulsa, OK Well there are multiple ways to accomplish that. Two off the top of my head...One would be to either create a registry key, or create some kind of file on the client once it has been checked. The script can then check for the existence of this key or file, and skip the odbc check if it has already been done.Second, you can use one of the file i/o functions to load the log file, and check to see if the username/workstation already exists in the log. This method would depend on how many clients you will be checking as the log file could get big, and reading through it at every login might get slow. Top
 #206895 - 2013-03-11 06:08 PM Re: ODBC check using Kix [Re: ShaneEP] ShaneEP MM club member Registered: 2002-11-29 Posts: 2080 Loc: Tulsa, OK Here is the log file loading method... Code:$odbcName = "tess_live_readonly"$logfile = "C:\ODBC_LOG.txt" If KeyExist("HKCU\Software\ODBC\ODBC.INI\" + $odbcName) OR KeyExist("HKLM\Software\ODBC\ODBC.INI\" +$odbcName) For Each $line in LoadFile($logfile, @CRLF) If InStr($line,@WkSta) AND InStr($line,@UserID) $logged=1 Endif Next If Not$logged $nul = RedirectOutput($logfile) @Date+" - "+@Time+" - "+$odbcname+" Found on "+@WkSta+" under user "+@UserID ?$nul = RedirectOutput("") Endif Endif Function loadfile($file, optional$array, $Uni) DIM$fso,$f,$fs If $uni$uni = $uni-3 Else$uni = -2 Endif If Not $uni$uni = -2 Endif $fso = CreateObject("Scripting.FileSystemObject")$f = $fso.GetFile($file) If @ERROR Exit 2 EndIf $fs =$f.OpenAsTextStream(1,$uni) If Not$array $loadfile =$fs.Read($f.size) Else$loadfile = Split($fs.Read($f.size),$array) Endif Exit @ERROR EndFunction Top  #206896 - 2013-03-11 07:15 PM Re: ODBC check using Kix [Re: ShaneEP] ShaneEP MM club member Registered: 2002-11-29 Posts: 2080 Loc: Tulsa, OK Here is the other method, it creates a registry value when it is logged, so that it only logs if the value doesnt exist. Code:$odbcName = "tess_live_readonly" $logfile = "C:\ODBC_LOG.txt" If ReadValue("HKCU\Software\ODBC\ODBC.INI\" +$odbcName,"Logged")<>1 If KeyExist("HKCU\Software\ODBC\ODBC.INI\" + $odbcName) OR KeyExist("HKLM\Software\ODBC\ODBC.INI\" +$odbcName) $nul = RedirectOutput($logfile) @Date+" - "+@Time+" - "+$odbcname+" Found on "+@WkSta+" under user "+@UserID ?$nul = RedirectOutput("") $nul = WriteValue("HKCU\Software\ODBC\ODBC.INI\" +$odbcName,"Logged",1,"REG_SZ") Endif Endif Top
 #206897 - 2013-03-11 07:48 PM Re: ODBC check using Kix [Re: ShaneEP] Thrasonic Fresh Scripter Registered: 2011-08-15 Posts: 26 Loc: GA Wow ShaneEP, you are FAST! Jeez... 8)You're awesome. This appears to do exactly what I'm looking for. Thanks a ton! Top
 Page 1 of 1 1

Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Arend_, Mart
 Hop to: KiXtart ------   Kixforms   KiXtart FAQ & How to's   Lounge   Basic Scripting   Advanced Scripting   COM Scripting   Suggestions   BetaRepository ------   KiXtart Learning
 Shout Box
 Who's Online 0 registered and 36 anonymous users online.
 Newest Members thequeen, ameliaryan, shalomsalami, dkdssdk, Vroon 17639 Registered Users

Generated in 0.054 seconds in which 0.024 seconds were spent on a total of 13 queries. Zlib compression enabled.

 Search the board with: superb Board Search or try with google: Web kixtart.org