How to Graph SQL Server Data from a PowerShell Script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to Graph SQL Server Data from a...

How to Graph SQL Server Data from a PowerShell Script

Rate Topic

Display Mode

Topic Options

Author

Message

shew

Posted Wednesday, October 10, 2012 2:56 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:51 PM
Points: 364, Visits: 683

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:

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

Any ideas?

Post #1371126

shew

Posted Thursday, October 11, 2012 1:37 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:51 PM
Points: 364, Visits: 683

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?

Post #1371755

Jeff Moden

Posted Thursday, October 11, 2012 7:42 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1371855

shew

Posted Friday, October 12, 2012 5:46 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:51 PM
Points: 364, Visits: 683

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.

Post #1372042

Jeff Moden

Posted Friday, October 12, 2012 7:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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

Post #1372101

shew

Posted Friday, October 12, 2012 5:35 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:51 PM
Points: 364, Visits: 683

Jeff Moden (10/12/2012)

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.

Post #1372387

Jeff Moden

Posted Sunday, October 21, 2012 6:18 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

shew (10/12/2012)

Jeff Moden (10/12/2012)

Sorry for the late reply. Maybe SSRS?

--Jeff Moden

Post #1375222

« Prev Topic | Next Topic »

Permissions