community.spiceworks.com
Open in
urlscan Pro
2606:4700:4400::6812:2a2d
Public Scan
URL:
https://community.spiceworks.com/t/how-to-grab-data-inside-a-dataset-in-powershell/967305
Submission: On August 27 via manual from US — Scanned from US
Submission: On August 27 via manual from US — Scanned from US
Form analysis 1 forms found in the DOM
POST /login
<form id="hidden-login-form" method="post" action="/login" style="display: none;">
<input name="username" type="text" id="signin_username">
<input name="password" type="password" id="signin_password">
<input name="redirect" type="hidden">
<input type="submit" id="signin-button" value="Log In">
</form>Text Content
Skip to main content
* News & Insights
* News & Insights Home
--------------------------------------------------------------------------------
* Artificial Intelligence
* Innovation
* IT Careers & Skills
* Cloud
* Cybersecurity
* Future of Work
* All Categories
* Community
* Community Home
--------------------------------------------------------------------------------
* Cloud
* Collaboration
* Hardware
* Networking
* Programming
* Security
* Software
* Storage
* Vendors
* Virtualization
* Windows
* All Categories
* Research
* State of IT 2024 Report
* IT Spend Report
* Customer Experience Report
* IT Tools
* Cloud Help Desk
* Remote Support
* Inventory Online
* Contracts
* Connectivity Dashboard
* All IT Tools
* Newsletters
* Events
* Reviews
Log In | Join
*
* My Feed
* Unanswered Questions
* My Posts
* Community Guidelines
* Events
* More
Categories
* Cloud Computing & SaaS
* Collaboration
* Data Storage, Backup & Recovery
* Hardware
* Networking
* Programming & Development
* Security
* Software
* Vendors
* Virtualization
* Windows
* All categories
Tags
* active-directory-gpo
* dns
* firewalls
* microsoft-office-365
* powershell
* printers-copiers-scanners-faxes
* sap
* vmware
* water-cooler
* windows-server
* All tags
* Spiceworks Community
* Programming & Development
HOW TO GRAB DATA INSIDE A DATASET IN POWERSHELL?
Programming & Development
powershellquestion
You have selected 0 posts.
select all
cancel selecting
Feb 20
1 / 24
Feb 20
Feb 22
spiceuser-lny6vPoblano
Feb 20
Here’s my PowerShells script.
#variable initialization
$currentTime = Get-Date -Format 'g'
# Define the list of SQL Server instances
$sqlDatabases = @('DB14')
# Calculate the date of the previous night
$previousNight = (Get-Date).AddDays(-1).ToString('yyyyMMdd')
function QueryFailedJobs($servers) {
# Loop through each SQL Server instance
foreach ($sqlServer in $servers) {
# Build the connection string
$connectionString = "Server=$sqlServer;Integrated Security=True;"
# Create a new SQL connection
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
try {
# Open the SQL connection
$connection.Open()
# Create a SQL command to query failed jobs
$commandText = "
SELECT j.name AS 'Job Name',
h.run_date AS 'Run Date',
h.run_time AS 'Run Time',
h.step_id AS 'Step ID',
h.step_name AS 'Step Name',
h.message AS 'Message'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE h.run_status = 0 -- 0 indicates failure
AND h.run_date = $previousNight
ORDER BY h.run_date DESC, h.run_time DESC
"
$command = $connection.CreateCommand()
$command.CommandText = $commandText
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$failedJob = $dataset.Tables[0] | Format-Table -AutoSize
Write-Output 'Job Failed: ' $dataset.Tables[0] | Format-Table -AutoSize
Write-Output 'Number of Rows: ' $failedJob
if ($failedJob -gt 0) {
[PSCustomObject]@{
Server = $sqlServer
JobName = $failedJob.JobName
RunDate = $failedJob.run_date.ToString('MM/dd/yy') + ' ' + $failedJob.run_time.ToString('hh:mm tt')
StepName = $failedJob.step_name
Message = $failedJob.message
}
}
} catch {
$ErrorMessage = $_.Exception.Message
$errMsgException = 'There was a problem connecting to and/or querying ' + $sqlServer + ". Specific error:`n`n" + $ErrorMessage + "`n`n Timestamp: " + $currentTime
#capturing the error message
[PSCustomObject]@{
ErrorMsg = $errMsgException
}
} finally {
# Close the SQL connection
$connection.Close()
}
}
}
$qryResult = QueryFailedJobs($sqlDatabases)
Write-Output 'Total Failed Jobs: ' $qryResult
My problem seems to be in this area of the code.
if ($failedJob -gt 0) {
[PSCustomObject]@{
Server = $sqlServer
JobName = $failedJob.JobName
RunDate = $failedJob.run_date.ToString('MM/dd/yy') + ' ' + $failedJob.run_time.ToString('hh:mm tt')
StepName = $failedJob.step_name
Message = $failedJob.message
}
}
And here’s the output including all the Write-OutPut commands.
1d4aa049-2f5b-4ad9-9859-ccacc30745a1-result.png800×252 79.3 KB
The Write-Output 'Job Failed: ’ shows that it’s able to query the data in the
database.
Best Answer by ich.ni.san in post #19
> I think this part may be easy. To my understanding, $($row.message) is the
> only one that should have dataset information. My code was based off of the
> posted code, specifically where data from the dataset was being put into the
> variables. I did not pay attention to the SQL. But now, looking at the …
6
1.9k views 11 spice ups
12
7
4
read 4 min
NeallyPure Capsaicin
Feb 20
Unrelated to your question, since you are using a function, “$previousNight” is
not defined in your function, that’s a problem as a function has a different
scope. you either have to feed it into the function, or declare it inside the
function.
learn.microsoft.com
ABOUT SCOPES - POWERSHELL
Explains the concept of scope in PowerShell and shows how to set and change the
scope of elements.
7b8fed6a-6de5-4da4-9176-41d575ea1198-2024-02-20_10-36-05.png694×512 31.9 KB
1
NeallyPure Capsaicin
Feb 20
spiceuser-lny6v:
>
Maybe try to remove the “| Format-Table -AutoSize”
Format table breaks objects.
It’s fine in the ‘write-output’ cmdlet, but if you want to further process it do
not use format-table.
2
spiceuser-lny6vPoblano
Feb 20
Thank you for that tip, much appreciated! I’ve moved into the function now.
So, I’ve made this small change.
$failedJob = $dataset.Tables[0] | Format-Table -AutoSize
Write-Output 'Job Failed: ' $dataset.Tables[0] | Format-Table -AutoSize
Write-Output 'Number of Rows: ' $failedJob.Rows.Count
if ($failedJob.Rows.Count -gt 0) {
[PSCustomObject]@{
Server = $sqlServer
JobName = $failedJob.JobName
RunDate = $failedJob.run_date.ToString('MM/dd/yy') + ' ' + $failedJob.run_time.ToString('hh:mm tt')
StepName = $failedJob.step_name
Message = $failedJob.message
}
}
And the result is:
ad2c0aa4-9c28-4fc8-8b1d-63058bc83d34-result2.png800×100 37.2 KB
NeallyPure Capsaicin
Feb 20
spiceuser-lny6v:
>
Maybe I’m missing it but I don’t see the change.
ich.ni.sanGhost Chili
Feb 20
Neally:
> spiceuser-lny6v:
>
> >
>
> Maybe I’m missing it but I don’t see the change.
I think in the if statement?
if ($failedJob.Rows.Count -gt 0) vs. if ($failedJob -gt 0)
From the screenshot, it looks as if it may have ran correctly. The “Number of
Rows” probably needs the same change.
Write-Output 'Number of Rows: ' $failedJob.Rows.Count
NeallyPure Capsaicin
Feb 20
the problem is ‘format-table’ probably breaks the object so the if statement
does not do what he wants
$failedJob.Rows.Count
does that actually return what it should?
spiceuser-lny6vPoblano
Feb 20
This is the changed.
798f89cf-1f85-4f6b-8940-8c375ed122ee-changed.png715×319 37 KB
I used tried both with and without the .rows.count and with similar result.
spiceuser-lny6vPoblano
Feb 20
And if I removed the ‘format-table’, I got the correct number of rows returned
but still getting an error.
4591fe59-3812-4c31-a5bb-adb9eae1e207-result3.png758×284 25.7 KB
And I believed that error is coming from within the “if” statement.
NeallyPure Capsaicin
Feb 20
spiceuser-lny6v:
> And I believed that error is coming from within the “if” statement.
no it looks like it comes from your try/catch and in this case it’s form the
catch statement.
catch {
$ErrorMessage = $_.Exception.Message
$errMsgException = 'There was a problem connecting to and/or querying ' + $sqlServer + ". Specific error:`n`n" + $ErrorMessage + "`n`n Timestamp: " + $currentTime
#capturing the error message
[PSCustomObject]@{
ErrorMsg = $errMsgException
}
spiceuser-lny6vPoblano
Feb 20
It is the “if” statement that causes the error. I commented out the whole
if-statement block and the error went away.
spiceuser-lny6vPoblano
Feb 20
This is the main issue, I think.
[PSCustomObject]@{
Server = $sqlServer
JobName = $failedJob.JobName
RunDate = $failedJob.run_date.ToString('MM/dd/yy') + ' ' + $failedJob.run_time.ToString('hh:mm tt')
StepName = $failedJob.step_name
Message = $failedJob.message
}
The $failedJob does not seem to hold the value for some reason, I think.
NeallyPure Capsaicin
Feb 20
spiceuser-lny6v:
> It is the “if” statement that causes the error. I commented out the whole
> if-statement block and the error went away.
but the error is from the catch statement, not the If, so IDK why it would do
that.
unless something in the IF statement causes the TRy to catch it
try to remove the dang format -table
$failedJob = $dataset.Tables[0] | Format-Table -AutoSize
# vs
$failedJob = $dataset.Tables[0]
spiceuser-lny6vPoblano
Feb 20
This is the result of removing the format -table. And yes, it does seem that
something in the if-statement the throws the error that’s why it got caught in
the catch block.
4591fe59-3812-4c31-a5bb-adb9eae1e207-result3.png758×284 25.7 KB
spiceuser-lny6vPoblano
Feb 20
I tried commenting out some code.
$failedJob = $dataset.Tables[0]
Write-Output 'Job Failed: ' $dataset.Tables[0] | Format-Table -AutoSize
Write-Output 'Number of Rows: ' $failedJob.Rows.Count
if ($failedJob.Rows.Count -gt 0) {
[PSCustomObject]@{
Server = $sqlServer
JobName = $failedJob.JobName
#RunDate = $failedJob.run_date.ToString('MM/dd/yy') + ' ' + $failedJob.run_time.ToString('hh:mm tt')
#StepName = $failedJob.step_name
Message = $failedJob.message
}
}
And here’s the result…no error but noticed I commented out the #RunDate and
#StepName. The JobName also has {$null, $null} as value retuned.
748cb679-231b-4b8b-8a7e-1897fdb02b9d-result4.png763×289 32.9 KB
ich.ni.sanGhost Chili
Feb 20
Asking to learn:
Is there an advantage to putting the dataset into the variable and then pulling
information from the variable with:
$failedJob = $dataset.Tables[0] | Format-Table -AutoSize
And
JobName = $failedJob.JobName
?
In my coding, I’ve been pulling the information from the dataset directly and it
would look more:
Foreach ($Row in $dataset.Tables[0].rows)
{
$JobName = "$($Row.JobName)"
$StepName = "$($Row.StepName)"
}
NeallyPure Capsaicin
Feb 20
> Is there an advantage to putting the dataset into the variable and then
> pulling information from the variable with:
I guess it depends how the data is getting massaged.
I too prefer to use a foreach.
spiceuser-lny6vPoblano
Feb 21
Just kicks, I tried the foreach:
foreach ($row in $dataset.Tables[0].Rows) {
[PSCustomObject]@{
Server = $sqlServer
JobName = "$($row.JobName)"
RunDate = "$($row.run_date)"
#RunDate = $failedJob.run_date.ToString('MM/dd/yy') + ' ' + $failedJob.run_time.ToString('hh:mm tt')
StepName = "$($row.step_name)"
Message = "$($row.message)"
}
}
And here’s the result below. I do not see the exception error but I’m still not
getting the value out of the $row. It showing up as blank.
fd84291a-fbdc-40d5-9710-576f8a3d56aa-result5.png745×232 5.2 KB
I’m much closer though than before but still not there yet. @ich-ni-san , thank
you for the post. That helped a lot!
1
ich.ni.sanGhost Chili
Feb 21
spiceuser-lny6v:
> Just kicks, I tried the foreach:
>
> foreach ($row in $dataset.Tables[0].Rows) {
> [PSCustomObject]@{
> Server = $sqlServer
> JobName = "$($row.JobName)"
> RunDate = "$($row.run_date)"
> #RunDate = $failedJob.run_date.ToString('MM/dd/yy') + ' ' + $failedJob.run_time.ToString('hh:mm tt')
> StepName = "$($row.step_name)"
> Message = "$($row.message)"
> }
> }
>
>
> … but I’m still not getting the value out of the $row. It showing up as blank.
I think this part may be easy. To my understanding, $($row.message) is the only
one that should have dataset information. My code was based off of the posted
code, specifically where data from the dataset was being put into the variables.
I did not pay attention to the SQL. But now, looking at the SQL, it appears the
field names were changed. For example, in the original SQL, the following was
written
h.step_name AS ‘Step Name’,
The dataset now has a field named, “Step Name”. However, in the ForEach loop
above, an attempt is made to capture data from “$($row.step_name)”. I don’t
think there is a field named, “step_name” in the dataset. The field names need
to match.
For simplicity’s sake, I would change the SQL to something like:
h.step_name AS ‘StepName’,
Or, you could even skip renaming the field and just use the actual database
field name. The important thing is that whatever name is used in the creation of
the dataset is what is used when trying to capture information from the dataset.
Edit: You probably already know this, if so, please ignore. When “AS” is used,
the field is basically being assigned a new name. So,
h.step_name AS ‘Step Name’,
takes data from a field in the database named, “step_name” and puts the data
into a field in the result named, “Step Name”. When you pull information from
the resulting dataset, there is no such field as “step_name”. That field existed
only in the original database.
Best Answer
spiceuser-lny6vPoblano
Feb 21
@ich-ni-san , thank you so much! That solves the issue…learned something today
again.
One small thing, formatting the date.
I tried:
RunDate = "$($row.RunDate.ToString('MM/dd/yy'))"
Or:
$row.RunDate.ToString('MM/dd/yy')
However, it only gives me:
RunDate: MM/dd/yy
instead of the actual date.
CONNECT
* Tech Vendors
* Live Events
* SpiceCorps Meetups
* SpiceWorld
NEED HELP?
* FAQs
* Support Forum
* Community Guidelines
RESOURCES
* All Categories
* Tech How-Tos
* Scripts
* IT Research
* Product Reviews
FOR TECH MARKETERS
* Marketing Services
* Demand Generation
* Account Based Marketing
* Data Capabilities
* Contact Sales
SPICEWORKS
* About Us
* Contact Us
* News & Insights
* Community
* Tools & Apps
* Aberdeen
* Careers
* Press/Media
*
*
*
*
* Sitemap
* Privacy Policy
* Terms of Use
* Cookie Policy
* Accessibility Statement
* Do Not Sell My Personal Information
Copyright © 2006 — 2024 Spiceworks Inc.
Invalid date Invalid date