How to Graph SQL Server Data from a PowerShell Script

  • Has anyone found a way to graph SQL Server data from a PowerShell script?

    This link describes something close to what I hope to do:

    http://blogs.technet.com/b/richard_macdonald/archive/2009/04/28/3231887.aspx

    However, I would like to replace the functionality of his code:

    Now for some data to display:

    # add data to chart

    $Cities = @{London=7556900; Berlin=3429900; Madrid=3213271; Rome=2726539;

    Paris=2188500}

    [void]$Chart.Series.Add("Data")

    $Chart.Series["Data"].Points.DataBindXY($Cities.Keys, $Cities.Values)

    with the output from a SQL Server query similar to this:

    [array]$SQLOutput_STR = sqlcmd.exe -S my_server\my_instance -E -Q "select x, y from my_table;"

    My query should return data like:

    X Y

    - -

    1 9

    3 4

    5 7

    8 3

    0 4

    Instead of creating a bar chart, I would prefer to create a line chart.

    Any ideas?

  • I found this link (http://poshcode.org/1205) which works fine for charting get-process information (despite a couple of typos in the script). But I am not having any luck "trading" sqlcmd.exe output for get-process output. Apparently, get-process returns its data as a PowerShell object, but sqlcmd.exe does not.

    Any ideas on how to return the results of a SQL query as a PowerShell object?

  • shew (10/11/2012)


    Any ideas on how to return the results of a SQL query as a PowerShell object?

    I'm no Powershell Ninja by any means so I don't know the Powershell answer to this but I'm sure this is one. In the meantime, couldn't you just BCP the results out to a file using SQLCMD and open the file in Powershell?

    By the same token, I'm not sure that PowerShell is the answer here. Why not create a spreadsheet that looks at external data through a pass-through view and plot from that?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    Thanks for the feedback.

    couldn't you just BCP the results out to a file using SQLCMD and open the file in Powershell?

    From a PowerShell perspective, I think that character-based (not native-based) BCP output would generate the same type of "text" file as:

    sqlcmd.exe -S ... -E -Q "select X, Y from my_table"

    I'm thinking BCP and sqlcmd are probably a wash.

    By the same token, I'm not sure that PowerShell is the answer here. Why not create a spreadsheet that looks at external data through a pass-through view and plot from that?

    I'm trying to remove all Microsoft Office installation aspects from this project. We are a locked down shop which does not allow Office to be installed on its servers, and I would like to be able to graph data while logged onto remote servers that may be accessible only through a VPN. So, the native graphing ability of PowerShell (i.e., Microsoft Chart Controls for the .NET Framework, which is oddly part of our base install) is attractive.

    I originally thought that sqlcmd.exe was not returning its results in an object, but I was mistaken.

    The http://poshcode.org/1205 link for LibraryChart.ps1 that I cited above is the closest I've found for graphing data from the PowerShell command line. However, it relies on the use of a PowerShell hash table to feed its data to the Microsoft Chart Controls. The PowerShell "get-process" command returns SystemArray output that is compatible with LibraryChart.ps1 and its use of a hash table, but sqlcmd.exe returns a SystemArray that does not appear to be compatible with the script. I'm thinking there is a way to pipe the sqlcmd.exe output to make it more similar to the "get-process" output, but I have not found it yet.

  • You do't need to have MS Office installed on the server to do what I suggest. You would use the spreadsheet from your (or a user's) desktop as if it were a 3rd party app (which it is) to access "External Data" and plot it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (10/12/2012)


    You do't need to have MS Office installed on the server to do what I suggest. You would use the spreadsheet from your (or a user's) desktop as if it were a 3rd party app (which it is) to access "External Data" and plot it.

    Thanks, Jeff.

    I think understood what you meant. In certain circumstances, we already do what you suggest.

    However, we are a hosting facility with scores of VLANs and VPNs, and we do not necessarily allow firewall connections to reach the remote data. I need something that works totally on the remote server, without any dependencies on my local desktop.

  • shew (10/12/2012)


    Jeff Moden (10/12/2012)


    You do't need to have MS Office installed on the server to do what I suggest. You would use the spreadsheet from your (or a user's) desktop as if it were a 3rd party app (which it is) to access "External Data" and plot it.

    Thanks, Jeff.

    I think understood what you meant. In certain circumstances, we already do what you suggest.

    However, we are a hosting facility with scores of VLANs and VPNs, and we do not necessarily allow firewall connections to reach the remote data. I need something that works totally on the remote server, without any dependencies on my local desktop.

    Sorry for the late reply. Maybe SSRS?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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