Monday, January 28, 2019

Using Powershell to execute cypher scripts with secure credentials and logging results/errors.



This is a continuation of my 1st draft: Using a Powershell wrapper to securely authenticate to Neo4J to execute CYPHER using Bolt.

PROBLEM #1: I was running several .cypher scripts as a scheduled task on Windows using cypher-shell to execute them.  This was fine, however my .cypher files had to provide plain-text to authenticate to various REST-API sites I was using to feed my Neo4j database.  So I wrote the credential ps wrapper (previous post).

PROBLEM #2: As I made changes to my scripts, I would inevitably write some syntax errors into my cypher scripts, and unknowingly break my import process.  But often, just break it a little.  Unless I manually ran each bit of code in the Neo4j Browser, I didn't have an easy way to verify the results (or lack-thereof) of my cypher script modifications.

MY WORK-AROUND: A full cypher execution method that would also log the results (and some statistics meta-data), and show me syntax errors (exceptions) from the cypher.

HOW IT WORKS:
First you supply your Neo4j database destination & credentials using set-n4jcredentials.ps1.  Then supply any additional (API, web credentials) using set-customcredentials.ps1. These store credentials (in the registry) with secure-string for the sensitive data, and attach them to a logical datasource name.  (when requested on the command-line, your .cypher will have a search/replace of your text for the "actual" credential information retrieved from the secure-string stored in the registry before it is submitted to the neo4j engine. 

Then execute your cypher by running get-cypher-results.ps1:

.\get-cypher-results.ps1 -Datasource 'N4jDataSource' -cypherscript 'C:\path-to-my-script\myscript.cypher' -logging 'N4jDataSource'


Results:


The get-cypher-results.ps1 will segment your script into transactions (a semicolon followed by a linefeed)

You can also give "sections" of your code a label by using the keyword section at the beginning of comments in the cypher script:

// section Main import routine to create (:Asset) nodes

...
Each transaction will be run and the metadata results will be (optionally) recorded in a log entry (per transaction).  The logging is done (of course) as a neo4j graph using the label 
(:Cypherlogentry) The following counter items will be recorded as properties:

ConstraintsAdded
ConstraintsRemoved
IndexesAdded
IndexesRemoved
LabelsAdded
LabelsRemoved
NodesDeleted
Notifications
Plan
Profile
PropertiesSet
RelationshipsCreated
RelationshipsDeleted
ResultAvailableAfter
(how long did the transaction take to run)
StatementType
Version (of the target Neo4j server)
date (epoch when the transaction ran)
linenumber (of where this transaction begins in the script)
script (full path and filename of the .cypher script)
section (named section of code)
server: fqdn or IP and port of the neo4j server
source: name of the computer the powershell script was executed from
error: (any exception error thrown by the neo4j engine will be recorded here)

All transactions from a single .cypher script will be bookended by a "BEGIN SCRIPT" and "END SCRIPT" section marker, with the END SCRIPT logging a "ResultAvailableAfter" that is a sum of all the transactions within the script.

All entries for a particular script execution will be tied together with a relationship: 
-[:PART_OF_SCRIPT_EXECUTION]- The wrapper will complete the execution and supply some example cypher queries to return the error logging for that execution.



This gave me a method to quickly run batches of .cypher code against a neo4j database, and determine if I generated any exceptions, and log metadata to track trends for code sections.


All the scripts referenced in this post are available at github.com/pdrangeid/n4j-pswrapper






No comments:

Post a Comment

Have a comment? Would love to hear it!