|
|
|||||||
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? |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
Thanks Glenn. I'll give the UDF a look. |
||||||||
|
|
|||||||
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? |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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? |
||||||||
|
|
|||||||
Or just read the registry values you are looking for, and then change them if needed. |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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! |
||||||||
|
|
|||||||
Depending on the type of ODB connection it can also be in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI Changing 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. |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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? |
||||||||
|
|
|||||||
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("") Endif When 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. |
||||||||
|
|
|||||||
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? |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
Wow ShaneEP, you are FAST! Jeez... 8) You're awesome. This appears to do exactly what I'm looking for. Thanks a ton! |