Post reply

Powershell/SQL Query to create DB Growth Trend Check and export to Excel with a nice graph

  • August 5, 2015 at 9:52 am

    #303097

    Hi Experts

    I have written a query to get the monthly database growth check. However, wanted to check post creating the report, can I use any script to create a nice trend report which can be easily done using graph to show the trend?

    Also, can I send the graph using db mail also? If yes, please suggest a way around.

    Thanks.

  • August 6, 2015 at 4:37 am

    #1817804

    SQL-DBA-01 (8/5/2015)

    Hi Experts

    I have written a query to get the monthly database growth check. However, wanted to check post creating the report, can I use any script to create a nice trend report which can be easily done using graph to show the trend?

    Also, can I send the graph using db mail also? If yes, please suggest a way around.

    In theory yes its possible, db mail can send emails in both plain text and HTML format in addition it can also send emails with attachments.

    Although I have not done what you are requesting explicitly I have done similar. Three options for you, although its not an exhaustive list.

    1. Generate the HTML code for the graph from within SQL server, formatting will be tricky so you may need to create a CLR Stored procedure to so so. Have the HTML sent as the body of your email sent with db mail.

    This may help http://www.motobit.com/tips/detpg_html-bar-graph-chart/

    2. Generate your graph in excel using a scripting language like powershell or python. Python would be my choice as I am more comfortable with it. This script would most likely be best ran by a SQL Agent job that extracted the data from your database, generated the excel file containing the data \ graph and then emailed the the results as an attachment with db mail.

    This may help: http://xlsxwriter.readthedocs.org/en/latest/example_chart_line.html

    3. Like option 2 ignore excel and generate the graph using python as a image file and email it as an attachment:

    This may help

    http://www.thetechrepo.com/main-articles/465-how-to-create-a-graph-in-python

    MCITP SQL 2005, MCSA SQL 2012

  • August 6, 2015 at 4:46 am

    #1817809

    And to address your point about the easiest way, do you have a SSRS setup? A subscribed report would be far easier that included the graph.

    MCITP SQL 2005, MCSA SQL 2012

  • August 6, 2015 at 4:15 pm

    #1817991

    In our platform, we dont have SSRS. But I will take a look at the links you provided. Thanks Indeed.

    Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply