Page 1 of 1 1
Topic Options
#206827 - 2013-02-28 03:04 PM ODBC check using Kix
Thrasonic Offline
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 Moderator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4224
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! \:D

Top
#206830 - 2013-02-28 05:06 PM Re: ODBC check using Kix [Re: Glenn Barnas]
Thrasonic Offline
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 Offline
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 Moderator Online   content
MM club member
*****

Registered: 2002-11-29
Posts: 2030
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 Offline
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 Moderator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4303
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 Moderator Online   content
MM club member
*****

Registered: 2002-11-29
Posts: 2030
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 Offline
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 Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4615
Loc: The Netherlands
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.


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 Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11548
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 Offline
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 Moderator Online   content
MM club member
*****

Registered: 2002-11-29
Posts: 2030
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("")
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.


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 Offline
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 Moderator Online   content
MM club member
*****

Registered: 2002-11-29
Posts: 2030
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 Moderator Online   content
MM club member
*****

Registered: 2002-11-29
Posts: 2030
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 Moderator Online   content
MM club member
*****

Registered: 2002-11-29
Posts: 2030
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 Offline
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, Chris S., Glenn Barnas, ShaneEP, Arend_, Mart 
Hop to:
Shout Box

Who's Online
2 registered (Allen, ShaneEP) and 158 anonymous users online.
Newest Members
Sjaak, Biybucket, Markus1961, Ian231, kixnewbie12
17480 Registered Users

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

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