Convert historic Scripts directory to SQL database (NCM7.4) (PowerShell/SQL)

Version 1

    Hello all,

     

    When we moved to NCM 7.4, we launched Configuration Management, jumped over to Script Management, and didn't see any of our scripts.

    What gives?

     

    What initially wasn't clear to us was that all of our old scripts were on the filesystem and all the new scripts are stored in SQL.

    Support helped us identify this change, along with the fact that our beloved GUI application has been deprecated.

     

    Now that we knew scripts would be executed differently, we began working in NCM 7.4 and all is well.

    But, we wanted to get all of our historic scripts into the new management interface and support informed us the SQL table for the scripts is NCM_SnippetArchive.

     

    After playing around with script execution in NCM 7.4, there didn't appear to be much metadata around scripts.

    So, it appeared we could get away with not only importing our historic scripts, but also making up our own ConfigIDs in the NCM_SnippetArchive table, and that's exactly what we did.

     

    The script below is pretty straightforward.

    You provide the database connection parameters and a common "Comments" you want applied to each script upon import.

     

    You also need to change the Get-ChildItem path to wherever you have all your historic scripts saved.

    We also chose to exclude any file called "result".

     

    *****

    Note 1: Excluding files with "result" in the name was the first approach which wasn't 100% accurate - some text files in our Scripts directory had script results but didn't have "result" in the name of the text file.

    Eventually we made a separate script to iterate through all .txt files, Get-Content in PowerShell, search for regex "\s(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})", and move those files out to a different directory.
    This took care of all the files which had entries like:      "routeredge01.company.com (10.52.65.2)"    - and therefore most likely indicated a text file with results, not the original acl changes or whatever the script was.

     

    Note 2: The old script location on the file system was somewhere in Program Files like this: D:\Program Files (x86)\SolarWinds\Orion\NCM\Scripts

    *****

     

     

    # Main parameters

    $Database                       = 'orioncore'

    $Server                         = 'dbserver.company.com'

    $UserName                       = 'ncm'

    $Password                       = '1FancyPass!'

    $Comments                       = 'Import from old server - 20150731'

     

    # -----------------------------------------------------------

     

    # Base of $id for ConfigID; 4 digits will be added to this for the final $id

    $hexstart ="FFFFFFFF-A000-B000-C000-20150731"

     

    # Grab all scripts as objects

    $colScripts =Get-ChildItem "D:\Scripts\Projects\NCM7.4_ScriptImport\ToImport\*.txt"-Exclude "*result*"

     

    # $i for looping and $id generation

    $i =1

    foreach ($scriptin $colScripts)

        {

        

         # Get name of script

         $name = $script.Name -replace ".txt",""

         $name

     

         # Get Modify timestamp of script file and format for destination insert

         $date = ($script.LastWriteTime).ToString("yyyy-MM-dd HH:mm:ss.000")

         $date

     

         # Finish generation of $id for ConfigID column, make $id's ending in 0001, 0002, ... , 0010, 0011, etc

     

         switch (($i | Measure-Object -Character).Characters)

            {

                1 {$id = $hexstart + "000"+ $i}

                2 {$id = $hexstart + "00"+ $i}

                3 {$id = $hexstart + "0"+ $i}

            }

         $id

        

         # Grab script file in -Raw format (otherwise line breaks don't come across in SQL varchar(max) column

         $scriptcontent =Get-Content -Raw $script.FullName

         $scriptcontent =$scriptcontent -replace"'","''"    # Fixes illegal character issue

         #$scriptcontent

     

         # SQL insert command for each script

         $SqlQuery                       = "INSERT INTO [dbo].[NCM_SnippetArchive]

               ([ConfigID],

                   [ConfigTitle],

                   [DownloadTime],

                   [ModifiedTime],

                   [Config],

                   [Comments])

        VALUES ('$id','$name', '$date', '$date', '$scriptcontent', '$Comments')"

     

       

        $Connection =New-Object System.Data.SQLClient.SQLConnection

        $Connection.ConnectionString = "server='$Server';database='$Database';user='$UserName';password='$Password'"

        $Connection.Open()

        $Command = New-Object System.Data.SQLClient.SQLCommand

        $Command.Connection = $Connection

            $Command.CommandText = $SqlQuery

            $Command.ExecuteReader()

        $Connection.Close()

       

        # Move imported script to a "completed" directory

        Move-Item $script.FullName "D:\Scripts\Projects\NCM7.4_ScriptImport\Imported"

       

     

        $i++   # increment for next script import and $id increment

        }