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.

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'


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:

(how long did the transaction take to run)
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

Sunday, December 2, 2018

Using a Powershell wrapper to securely authenticate to Neo4J to execute CYPHER using Bolt.

I've been busily developing some of my automation leveraging Neo4j with our CommitCRM and Check_MK (Nagios) monitoring platform.

I wanted to automate my process for updating the graph database and generating tickets.  In order to do this securely I wanted to execute CYPHER scripts with windows task scheduler.

To make this easier I've built a powershell wrapper to run my scripts.  This consists of defining a neo4j datasource with the server location, user and password:

ServerURL bolt://server.fqdn.or.ip:7687
DSPW         01000000d08c9ddf0115d1118c7a00c04fc297e...
DSUser        neo4j

Download/install the Neo4j.Driver nuget package

Check out this post by Glenn Sarti for more information.

Configure your datasource:

First you will be prompted to locate the Neo4j.Driver.

Next supply the datasource name

Now the URL to your Neo4j database

Then the user/password

Now the script will attempt to connect using the provided information, and if successful will store the information in your registry under HKCU\Software\neo4j-wrapper\Datasource\your-datasource-name.

set-n4credentials.ps1 can be used to store multiple named datasources and will store each server URL, user, and password combination within a seperate reg key

Retrieve the datasource

Now you can use the execute-cypher-query.ps1 to securely retrieve the credentials from the registry and run your CYPHER code within powershell.

If the server is accessible and the credentials are correct, it will run whatever cypher code you run in the $query variable.  For example I've included a simple query to count the number of nodes in the graph database and return the results.

and here's the output:

In Summary

This should provide a secure way to run CYPHER scripts natively from powershell using the BOLT protocol. to allow for authentication without putting clear-text passwords within your powershell scripts.
Here's the example scripts in github
In a future version I'd like to use paramters to provide external .cypher scripts to run to truly use this as a wrapper.  I'd also like to store datasources that I reference fromWITHIN my cypher scripts.  (for instance apoc.load.json where I am required to provide API key or user credentials within the URL or as a header).

Friday, November 9, 2018

My journey building a connected enterprise with CommitCRM using a Neo4j Graph database


Last September I attended GraphConnect 2018 in New York.  If you are not familiar with what graph databases or Neo4j is here's an excellent primer.

The first session I attended immediately got my wheels spinning.  The example use case  was a customer service ticketing/support platform and bring together data that is normally stuck in silos of many different systems (HR, CRM, ticketing, asset management, etc).  Hey, I've got that problem!

We have been using CommitCRM for several years.  It is serving as our CRM, and PSA tool to provide IT services and support for our clients.

But the challenge has always long been, how can we bring holistic views and queries together with CommitCRM and from other systems, but we don't have a native integration into CommitCRM?

Here's a few systems that are very relevant, but don't currently integrate into CommitCRM:
  • Active Directory (both ours, and our clients') users, contacts, groups, computer objects
  • Office365 / Azure (CSP subscriptions, mailboxes, and users)
  • VMware (VMs and hosts)
  • Check_MK (a Nagios monitoring platform we use)
  • Avast Managed Workplace RMM tool (formerly AVG Level Platforms) 
  • DNS
Enter Neo4j Graph database.  This data platform solves several problems for us.  It allows us to ingest data from several platforms.  It's wicked fast, and allows us to ask complex relationship questions about our data to help decision support.  This essentially becomes a hyper-flexible reporting database that lets us ask questions about our clients' environments.

Another benefit to this platform, is you instantly have a visualization tool you can use to explore your environment.

I will be posting more about my experience integrating CommitCRM and our other systems using Neo4j to created a connected enterprise