Closed theabraxas closed 5 years ago
I think the Get-ADComputer and Get-ADUser tables should just be really tall with a date generated column (per brief discussion with @pgfm1). The queries can just do totals for unique dates to populate the charts - eg. computers with 'x' for past 30 days by day
Implemented a basic setup script and AD query script to load to the created database.
Using SQLServer
powershell module to interact with the database. Will build out more formal tables and include installation instructions for SQL Express
This has been completed - dbconfig.ps1 sets up all the necessary db integrations. SQL Server Express 2017 needs to be installed prior as well as the management studio in order to get the Invoke-sqlcmd
cmdlet. Future ideas to implement a InfluxDB and Postgres wrapper to support those dbs.
A few things to work out here. I want the documentation to be fully descriptive so that anyone can follow the steps to set up. I believe SQL Express 2017 is the way to go but need to figure out a few things.
1) Best SQL Server setup settings (Native Auth? Windows Auth? etc) 2) Best way to use PowerShell to interact with the database (ADO, SQL cmdlets, .NET/C# directly) 3) Initial design should encompass the bulk AD User object pull, AD Computer object pull, and the ability to view changes over time (daily import? weekly? what makes the most sense?)
Later tests should be written to ensure SQLi and similar attacks aren't viable. The design should also be revisited to ensure that it modular enough for others to easily work the data as well as to easily incorporate more data sources and constructed tables.