OK… I’ve been doing some remedial analytics and dashboarding for my company for a while (We’re an IT MSP). Started with Klipfolio and an in-house PSA/ticketing system, and then switched to Connectwise cloud/hosted. We’ve recently gotten SQL reporting access to our Connectwise instance.
I can pull pretty much any metric I want from the ‘live’ data with SQL at this point, however getting ‘time series’ data is far more complicated - having to calculate quantities of items opened after but not closed before a certain date to get them as an ‘opened during period’ aspect…
Am I missing a concept here, or is the right way to have a sort of Collector querying the MSSQL instance and feeding that into a dedicated time series warehouse for each metric? (Qty open tickets on a particular Service Board for instance.) This method seems far easier for trend recording.
I’ve been looking at InfluxDB and Telegraf, but I don’t see a way to QUERY a SQL instance - everything seems geared towards performance data, not running a query against the instance on a periodic basis.
I will also add: My access to this reporting instance is somewhat limited to read only. I haven’t tried it, but I doubt I can write a stored procedure or schedule anything.
Anyone take pity on the newbie/wannabe data analyst here and point me in the right direction and say ‘start walking’. I promise I’ve been trying to RTFM… Any assistance is greatly appreciated.
Also: I have Grafana running on a windows VM in AWS, and have plenty of server space to deploy any type of hosts/services I need.