Kaseya Community

Stored Procedure Methods a beginning!

  • kaseya_stored procedure_test.doc
    Hello all.

    First off, I would like to say thankyou to everyone who has offered help to us in the past. You guys and girls are great and we appreciate you and your efforts. This is why we are posting this work for you.



    The topic is: Stored Procedure Methods


    A quick summary is this... Get data out of the "get" section of the Kserver and into the database so that it can be properly and automatically be reported on.

    Known issues:We will not be looking at the Microsoft desktop SQL engine. If you use that engine, this will NOT work for you. This code will require work on your part to enter in the data. You will not be able to just cut and paste this in and expect it to work. Not gonna happen. (Please be careful formating the text, copy/paste methods differ, and you may have trouble in this area. We will provide the information in seperate txt files in order to better serve your copy/paste needs.)

    Requirements:You will have to know your way around a Microsoft SQL server in order to to this. (We are not DBA's. If some of you are, please take this and improve upon it and repost it for all to learn from.)


    __________________________________________________________



    Custom Kaseya SQL Stored Procedure Methods




    One of the biggest roadblocks to robust custom reporting in Kaseya is the data being stored in the directory structure of the ‘GetFiles’ system. This document will give users a way to insert that data into the backend SQL database for a more robust data retrieval option. This case study example will be fully explored using a script called “IEhistory”. The ieHistory script accompanies this document. It is also available on the Kaseya Forum for download.



    At times, a client will need to investigate an employee’s computer use. One of the best tools in discovering what an employee is doing is to copy the browser history. The following script does just that, transferring the data to the ‘GetFiles’ client directory.


    Script Name: Retrieve IE History

    Script Description: This script will run the program called IEHV.exe. It will create a txt file and then run a get. If you would like to email this information to an email address, just add the appropriate information to this script. Please note that this file is TAB delimited for importing into a database with a stored procedure.



    IF Test File

    Parameter 1 : C:tempiehv.exe

    Exists :

    THEN

    Get Variable

    Parameter 1 : 10

    Parameter 2 : c:temp

    Parameter 3 : agentDrv

    OS Type : 1

    Get Variable

    Parameter 1 : 6

    Parameter 2 :

    Parameter 3 : machineName

    OS Type : 1

    Execute Shell Command

    Parameter 1 : #agentDrv#iehv.exe /stab "C:temphistory.txt"

    Parameter 2 : 0

    OS Type : 1

    Get File

    Parameter 1 : #agentDrv#history.txt

    Parameter 2 : #machineName#ieHistoryhistory.txt

    Parameter 3 : 1

    OS Type : 1

    Write Script Log Entry

    Parameter 1 : IE History script has run successfully.

    OS Type : 1

    Pause Script

    Parameter 1 : 60

    OS Type : 1

    Delete File

    Parameter 1 : #agentDrv#history.txt

    OS Type : 1

    ELSE

    Get Variable

    Parameter 1 : 10

    Parameter 2 : c:temp

    Parameter 3 : agentDrv

    OS Type : 0

    Write File

    Parameter 1 : #agentDrv#iehv.exe

    Parameter 2 : VSASharedFilesiehv.exe

    OS Type : 0

    Write File

    Parameter 1 : #agentDrv#readme.txt

    Parameter 2 : VSASharedFilesreadme.txt

    OS Type : 0

    Write File

    Parameter 1 : #agentDrv#iehv.chm

    Parameter 2 : VSASharedFilesiehv.chm

    OS Type : 0

    Get Variable

    Parameter 1 : 6

    Parameter 2 :

    Parameter 3 : machineName

    OS Type : 0

    Execute Shell Command

    Parameter 1 : #agentDrv#iehv.exe /stab "C:temphistory.txt"

    Parameter 2 : 0

    OS Type : 0

    Get File

    Parameter 1 : #agentDrv#history.txt

    Parameter 2 : #machineName#ieHistoryhistory.txt

    Parameter 3 : 1

    OS Type : 0

    Write Script Log Entry

    Parameter 1 : IE History script has run successfully.

    OS Type : 0

    Pause Script

    Parameter 1 : 60

    OS Type : 0

    Delete File

    Parameter 1 : #agentDrv#history.txt

    OS Type : 0

    [/b]



    Now that we have the data, how can we get it into the SQL database for later processing.


    The first step is to insure there is a table in the ksubscribers database to hold the data. The following is the structure and creation script of that table.


    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ieHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[ieHistory]

    GO



    CREATE TABLE [dbo].[ieHistory] (

    [url] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [title] [varchar] (450) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [modifiedDate] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [expirationDate] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [userName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [subFolder] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [computerName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    GO


    No, we are not DBAs. That’s why this script should be improved. However, if you notice that the length of the URL column is very large, it is because some of the Urls in the History file can be very long. (Hint to DBA's..... this is a potential trouble area.)


    The next task we faced was getting the data into this table from the text file. As you notice if you’ve run the above “IEHistory” script, the text generated is in tab-delimited format. Therefore, we can do one of two things. We can use a DT job to grab the text and import it, or we can write a stored procedure to do the job.


    The first option is difficult since we don’t know from where the text will be coming. So we chose to use a stored procedure with parameters to accomplish this job. The create script is below:


    CREATE PROCEDURE s_Data_Import

    @PathFileName varchar(1250),

    @FileType tinyint,

    @computerName varchar(150)

    AS



    /*CREATE TEMP TABLE TO STORE DATA FOR TRANSFER*/

    CREATE TABLE ##TmpProcessing

    (

    [url] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [title] [varchar] (450) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [modifiedDate] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [expirationDate] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [userName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [subFolder] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [computerName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    )



    /****************************************/

    /* Step 1: Build Valid BULK */

    /* INSERT Statement */

    /****************************************/



    DECLARE @SQL varchar(2000)

    IF @FileType = 1

    BEGIN

    /******************************************************/

    /* Valid format: "John","Smith","john@smith.com" */

    /******************************************************/

    SET @SQL = "BULK INSERT ##TmpProcessing FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '""t""', ROWTERMINATOR = 'n' ) "

    END

    ELSE

    BEGIN



    /******************************************************/

    /* Valid format: John,Smith,john@smith.com */

    /******************************************************/

    SET @SQL = "BULK INSERT ##TmpProcessing FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = 't') "

    END



    /****************************************/

    /*Step 2: Execute BULK INSERT statement */

    /****************************************/



    EXEC (@SQL)



    /***********************************************************************/

    /* Step 2.5: Execute BULK INSERT statement */

    /* to clean up the crap */

    /**********************************************************************/



    INSERT ieHistory

    SELECT

    CASE

    WHEN @FileType = 1 THEN SUBSTRING(url,2,DATALENGTH(url)-1)

    WHEN @FileType = 2 THEN Replace(Replace(SUBSTRING(url,1,DATALENGTH(url)),'"',''),' ','')

    ELSE url

    END,

    CASE



    WHEN @FileType = 1 THEN SUBSTRING(title,2,DATALENGTH(title)-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING(title,1,DATALENGTH(title)),'"','')

    ELSE title

    END,

    CASE

    WHEN @FileType = 1 THEN SUBSTRING(modifiedDate,2,DATALENGTH(modifiedDate)-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING(modifiedDate,1,DATALENGTH(modifiedDate)),'"','')

    ELSE modifiedDate

    END,









    CASE

    WHEN @FileType = 1 THEN SUBSTRING(expirationDate,2,DATALENGTH(expirationDate)-1)

    WHEN @FileType = 2 THEN Replace(Replace(SUBSTRING(expirationDate,1,DATALENGTH(expirationDate)),'"',''),' ','')

    ELSE expirationDate

    END,

    CASE

    WHEN @FileType = 1 THEN SUBSTRING(userName,2,DATALENGTH(userName)-1)

    WHEN @FileType = 2 THEN Replace(Replace(SUBSTRING(userName,1,DATALENGTH(userName)),'"',''),' ','')

    ELSE userName

    END,

    CASE

    WHEN @FileType = 1 THEN SUBSTRING(subFolder,2,DATALENGTH(subFolder)-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING(subFolder,1,DATALENGTH(subFolder)),'"','')

    ELSE subFolder

    END,



    CASE

    WHEN @FileType = 1 THEN SUBSTRING(computerName,2,DATALENGTH(computerName)-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING(computerName,1,DATALENGTH(computerName)),'"','')

    ELSE computerName

    END



    FROM ##TmpProcessing

    GO



    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[##TmpProcessing]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[##TmpProcessing]

    GO



    There is nothing special that needs to be done with these scripts except to run each in Query Analyzer to create the objects.


    Now that these objects are in place, we can start the import. While in SQL query analyzer run the following code, replacing the items in braces [] with your information.


    exec dbo.s_Data_Import '[full path to file]', 2, '[computerName]'[/b]


    This procedure, as shown above, will extract the data from the text file, import it into the temporary table and when that process is complete, will transfer the data to the ‘real’ table deleting the temp table.

    Two other things to pay attention to. 1. the table directly reflects the output created in the Kaseya script above, and; 2. the computername column is a variable that is sent with the stored procedure.

    This stored procedure can be used for just about any data import (here we used it for the ieHistory output) with only a few changes. Remember, the two tables (real and temp) must have the same columns as the text file and each other, and the names inside the stored procedure need to be changes as below.


    CASE

    WHEN @FileType = 1 THEN SUBSTRING(subFolder,2,DATALENGTH(subFolder)-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING(subFolder,1,DATALENGTH(subFolder)),'"','')

    ELSE subFolder

    END,

    To

    CASE

    WHEN @FileType = 1 THEN SUBSTRING([yourcolumn][/b],2,DATALENGTH([yourcolumn][/b])-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING([yourcolumn][/b],1,DATALENGTH([yourcolumn][/b])),'"','')

    ELSE subFolder

    END,


    __________________________________________________________________________

    Summary: Using this example, you should be able to get ANY data stored in the GET section of Kaseya into your database.


    Please post your comments, suggestions, and your improvements, or other stored procedures, etc.... to this discussion. I know you all do not want to give away competitive advantage, but this helps everyone to help themselves and to ultimately help the clients. This willserve to ultimately make Kaseya a better, more robust framework as well. Would it not be nice to see Kaseya put some needed tools into the framework, so that we do not have to do it this way...? How cool would that be? They never will, if you do not let them know it is important by putting in your 2 cents here.

    Gamer-X and his trusty programmer Scruffy. ( yes hes scruffy )


    Legacy Forum Name: Stored Procedure Methods a beginning!,
    Legacy Posted By Username: Gamer-X
  • storedProdceureFiles.zip
    These are the stored procedure files.

    Legacy Forum Name: Reports,
    Legacy Posted By Username: Gamer-X
  • Here is the forum thread that discusses using the IE History view component.

    http://www.kaseya.com/kforum/view_topic.php?id=1180&forum_id=43


    Legacy Forum Name: Reports,
    Legacy Posted By Username: Gamer-X
  • now in case you thought we were done.... NOPE. Next we use a script on the kserver to automatically move the data at predetermined times into the database. Unfortunately, we have been putting out fires.... so we will post it as soon as possible.

    Gamer-X


    Legacy Forum Name: Reports,
    Legacy Posted By Username: Gamer-X
  • insertIEHistory.zip
    Here is the vbs file that you run on the local server that you have kaseya installed on. This vbs file will read through the kaseya directory structure, scan the Get folder and pull the history.txt (tab delimited) into the SQL database.

    Gamer-X


    Legacy Forum Name: Reports,
    Legacy Posted By Username: Gamer-X
  • final thoughts on this... for now... This script takes into account that the SQL server and the Kserver are on the same box. If you SQL server is on a seperate box, you would want to map a drive and change the relative path in the scripts.... It should be pretty straight forward. Smile

    This script has been fully tested and works very well on our current deployment.

    This entire process can be modified to pull any data from the get folder on the kserver and import it into any SQL database for storage, reporting, analysis, etc...

    Gamer-X


    Legacy Forum Name: Reports,
    Legacy Posted By Username: Gamer-X
  • Much Mana to Gamer-X - truly the king of Kaseya scripting!

    Legacy Forum Name: Reports,
    Legacy Posted By Username: raybarber
  • ____________________________

    OK, i went through it for a few more minutes and made some changes that worked for me. I hope you dont mind my modifying things. I am running SQL 2005 and some differences may have resulted from that:.

    Their werefour minor issues that I was experiencing with the stored procedure posted and these are the changes I had to make:

    1. I had to add:

    SET
    QUOTED_IDENTIFIER OFF

    go

    to the top of the sp. This allowed for the @SQL = "Bulk...lines to run correctly

    2. I had to modify the Bulk Insert commands to look like so:

    SET
    @SQL = "BULK INSERT ##TmpProcessing FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '\t') "

    Where ( ' \t ' ) is the only change. I only updated the SET where FileType = 2 as thats the format we are dealing with on this particular example.

    3. The biggest issue I was having was with utilizing a Bulk Insert into the temp database when the source history.txt file column sizes didnt match appropriately. The history.txt file contains 6 columns where the temp table created previously has 7. I simply had to modify the create ##Tmp...statement to remove the computername column as such:

    CREATE
    TABLE ##TmpProcessing

    (

    [url] [varchar]
    (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [title] [varchar]
    (450) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [modifiedDate] [varchar]
    (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [expirationDate] [varchar]
    (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [userName] [varchar]
    (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [subFolder] [varchar]
    (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    )

    4. Once I modified our temp table to handle the appropriate # of fields from the source file for the Bulk Insert I had to update the data dump from the tmptable to the workingtable by modifying the Case statement like so:

    INSERT
    ieHistory(url,title,modifiedDate,expirationDate,userName,subFolder,computerName)

    SELECT


    CASE


    WHEN @FileType = 1 THEN SUBSTRING(url,2,DATALENGTH(url)-1)

    WHEN @FileType = 2 THEN Replace(Replace(SUBSTRING(url,1,DATALENGTH(url)),'"',''),' ','')

    ELSE url

    END,

    CASE


    WHEN @FileType = 1 THEN SUBSTRING(title,2,DATALENGTH(title)-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING(title,1,DATALENGTH(title)),'"','')

    ELSE title

    END,

    CASE


    WHEN @FileType = 1 THEN SUBSTRING(modifiedDate,2,DATALENGTH(modifiedDate)-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING(modifiedDate,1,DATALENGTH(modifiedDate)),'"','')

    ELSE modifiedDate

    END,



    CASE


    WHEN @FileType = 1 THEN SUBSTRING(expirationDate,2,DATALENGTH(expirationDate)-1)

    WHEN @FileType = 2 THEN Replace(Replace(SUBSTRING(expirationDate,1,DATALENGTH(expirationDate)),'"',''),' ','')

    ELSE expirationDate

    END,

    CASE


    WHEN @FileType = 1 THEN SUBSTRING(userName,2,DATALENGTH(userName)-1)

    WHEN @FileType = 2 THEN Replace(Replace(SUBSTRING(userName,1,DATALENGTH(userName)),'"',''),' ','')

    ELSE userName

    END,

    CASE


    WHEN @FileType = 1 THEN SUBSTRING(subFolder,2,DATALENGTH(subFolder)-1)

    WHEN @FileType = 2 THEN Replace(SUBSTRING(subFolder,1,DATALENGTH(subFolder)),'"','')

    ELSE subFolder

    END,

    @computername



    FROM
    ##TmpProcessing

    Here I am updating the last column in the ieHistory table that doesnt exist in the temp table (because of the lack of columns from the source file). This also incorporates the third variable to the stored procedure and writes it to the table.

    Thanks for letting me rifle through your code, that process is definitely easier to manage than a bunch of disparate vb files. I will try and work on a global sp that can accept variables to have a dynamic approach in creating tables, bulk insert formats, various types of data sets....essentially, we could recreate all of our data insert scheduled events and condense them down to a seamless process whereby we call a single stored procedureand throw at it whatever variables weneed. thanks again!

    I have yet to go through the vb side of the code that grabs the variables and calls the osql command but thats much smaller in scope. Code looks pretty clean at first look, I dont see why it wouldnt fly, im hoping to have this online (changed from the cumbersome way I was previously doing it) soon and will post more findings I have them.

    Jeremy

    Gamer-X,

    Iknow this is post is several months old but I was just pouring through the information and came across this post.I was hoping you could give me some insight as to how the computername gets into your database if is in fact being passed to the stored procedure as a variable. Are you using a modified or dynamic Bulk Insert format file to accomplish this? Or are you writing the machineID to the history.txt file using the Kaseya agent? Im not aware of a method to do this without going back to the original tab delimited file with more vb on the agent side, or updating the file on the server side by appending the computername prior to Insert.

    Both methods will work just fine, I am just unclear as to how it currently populates the ##TmpProcessing and ieHistory computername databases.

    Any insight would be helpful. We are currently using a similar method that is strictly VB driven and I would like to migrate away from it as utilizing the Bulk Insert method you display here is a nice plus.

    Your efforts are much appreciated!



    Legacy Forum Name: Reports,
    Legacy Posted By Username: jkurth
  • sorry for my late reply.

    That was what I was going to say....

    I bet you are using SQL 2k5 and 2k.

    I am glad that you modified it to your liking. That is what it is here for.

    Let me know if you need anything further.

    Gamer-X


    Legacy Forum Name: Reports,
    Legacy Posted By Username: Gamer-X