Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Graph SQL Server Data from a PowerShell Script Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 2:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 389, Visits: 789
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?



Post #1371126
Posted Thursday, October 11, 2012 1:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 389, Visits: 789
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
Posted Thursday, October 11, 2012 7:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1371855
Posted Friday, October 12, 2012 5:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 389, Visits: 789
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
Posted Friday, October 12, 2012 7:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1372101
Posted Friday, October 12, 2012 5:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 389, Visits: 789
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.



Post #1372387
Posted Sunday, October 21, 2012 6:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375222
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse