Author Posts

January 1, 2012 at 12:00 am

by Britjamez at 2013-04-11 09:16:02

Looking for some help here ... Here's the code first:

#Set some variables n stuff
$EpochTime=88888888
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100
$GLOBAL:machine = $env:COMPUTERNAME.Tolower()
$MachineName = $machine

#Function for SQL Query
function Stats-Query($SQLServer)
{
invoke-sqlcmd -query "EXEC MYQUERYNAME" -database DATABASENAME -serverinstance $SQLServer
}

#Results
$Results = Stats-Query $MachineName

#Manipulate results
foreach($Result in $Results)
{
$str = $Result
$str2 = -join (($str.length-1)..0 | Foreach-Object { $str[$_] })
$rv=[regex]'_'
$str2 = $rv.Replace($str2,".",1)
$str = -join (($str2.length-1)..0 | Foreach-Object { $str2[$_] })

Write "servers." + $MachineName+ "." + $str + " "+ $Result + " " + $EpochTime)
}

All works fine, except my output. My column names from the sproc are unknown (always change), and the resultset will only have 1 row.
so 30ish columns and 1 row.

What I eventually want, is to get an output of:
servers.MACHINENAME.MODIFIEDCOLUMNNAME COLUMNVALUE 88888888

How do I do this?!?

I think it's my foreach loop that's wrong! I am stumped!

by DonJ at 2013-04-11 22:39:56

You're going to have to share a bit more detail – what is in $results? What's sproc (stored procedure?)?

Can you give a concrete example of what the input might look like, and what the corresponding output should look like?