Naomi_wern
New Member
I have been having quite the time trying to figure this out. Let me try to explain what I am trying to accomplish, I hope i can be clear enough. I am sending two queries to an MSSQL database and receiving them back. The below code works perfect, however I would like to manipulate the format of the XML a bit before it writes to the XML file. I currently get 3 columns (serviceGroupName, numAccounts, numDevices) I would like to accomplish 1 of 2 things:1) Add a new column named "ReportType" and have it fill in "Monthly" Or "Total" depending on if it is pass 1 or 2 of the foreach loop (SQLQuery1 is Monthly report, and SQLQuery2 is Total number since inception)2) Create a new PSObject and have it fill in the appropriate information such as the data it receives back (serviceGroupName, numAccounts, numDevices)Below is my current code. As i mentioned it does work and it generated an XML but i would like to add some more information before the pipe to ConvertTo-XML if possible.\[code\]### Dates to use$Date = (Get-Date -f MM-dd-yyyy)$FDoTM = ((Get-Date -Day 01).AddMonths(0)).AddDays(0)$LDo2PM = ((Get-Date -Day 01).AddMonths(-1)).AddDays(-1)$TempDir = "C:\Temp"$WebDir = @("\\x.x.x.x\c$\inetpub\wwwroot\Reports\Accounts","\\x.x.x.x\c$\inetpub\wwwroot\Reports\Accounts")### Something$OutputXML = "$Date-Monthly-AccountReport.xml"### Connection settings, uses windows authentication$DBServer = "OMMITED"$databasename = "OMMITED"$Connection = new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database$Connection.ConnectionString ="server=$DBServer;database=$databasename;trusted_connection=True" # Connectiongstring setting for local machine database with window authenticationWrite-host "Connection Information:" -foregroundcolor yellow -backgroundcolor black$Connection #List connection information### Connect to Database and Run Query$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands$OutputHeader1 = "This Month's counts"$SqlQuery1 = @"SET NOCOUNT ON;WITH AccountDeviceStats(serviceGroupName,numAccounts,numDevices)AS( SELECT svg.name,COUNT(acct.serviceGroupId) as Accounts, NULL FROM bm_account acct WITH (NOLOCK) INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = acct.serviceGroupId where acct.CreateStamp between '$($LDo2PM)' and '$($FDoTM)'GROUP BY acct.serviceGroupId,svg.nameUNION ALLSELECT svg.name, NULL, COUNT(device.serviceGroupId) as Devices FROM bm_device device WITH (NOLOCK)INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = device.serviceGroupId, bm_account acctwhere device.accountID=acct.accountId and acct.CreateStamp between '$($LDo2PM)' and '$($FDoTM)'GROUP BY device.serviceGroupId,svg.name)SELECT ad1.serviceGroupName,ad1.numAccounts,ad2.numDevices FROM AccountDeviceStats ad1INNER JOIN AccountDeviceStats ad2 ON ad1.serviceGroupName = ad2.serviceGroupNameWHERE ad1.numAccounts IS NOT NULL AND ad2.numDevices IS NOT NULLORDER BY numAccounts DESC,numDevices DESC"@$OutputHeader2 = "Total Counts"$SqlQuery2 = @"SET NOCOUNT ON;WITH AccountDeviceStats(serviceGroupName,numAccounts,numDevices)AS(SELECT svg.name,COUNT(acct.serviceGroupId) as Accounts, NULL FROM bm_account acct WITH (NOLOCK)INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = acct.serviceGroupIdwhere acct.CreateStamp < '12-31-2099'GROUP BY acct.serviceGroupId,svg.nameUNION ALLSELECT svg.name, NULL, COUNT(device.serviceGroupId) as Devices FROM bm_device device WITH (NOLOCK)INNER JOIN bm_servicegroup svg WITH (NOLOCK) ON svg.servicegroupId = device.serviceGroupId, bm_account acctwhere device.accountID=acct.accountId and acct.CreateStamp < '12-31-2099'GROUP BY device.serviceGroupId,svg.name)SELECT ad1.serviceGroupName,ad1.numAccounts,ad2.numDevices FROM AccountDeviceStats ad1INNER JOIN AccountDeviceStats ad2 ON ad1.serviceGroupName = ad2.serviceGroupNameWHERE ad1.numAccounts IS NOT NULL AND ad2.numDevices IS NOT NULLORDER BY numAccounts DESC,numDevices DESC"@$sqlQueries = @($SqlQuery1, $SqlQuery2)$Results = @()Foreach ($Query in $sqlQueries){ $Connection.open() Write-host "Connection to database successful." -foregroundcolor green -backgroundcolor black $SqlCmd.CommandText = $Query $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $SqlCmd.Connection = $Connection $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $Connection.Close()$Results += $DataSet.Tables[0]($Results | ConvertTo-XML -NoTypeInformation).Save("$TempDir\$OutputXML")}if ((Get-ChildItem $TempDir -filter "$Date-*.xml").count -gt 0){ Foreach ($file in (Get-ChildItem $TempDir -filter "$Date-*.xml" -recurse)){ Foreach ($webserver in $WebDir){ Copy-Item $file.fullname "$webserver\$file" -force } Remove-Item $file.fullname -force }}\[/code\]Here is the output formatting of the XML\[code\]<?xml version="1.0"?><Objects> <Object> <Property Name="serviceGroupName">ServiceGroup1</Property> <Property Name="numAccounts">15</Property> <Property Name="numDevices">28</Property> <Property Name="RowError" /> <Property Name="RowState">Unchanged</Property> <Property Name="Table"> <Property>System.Data.DataRow</Property> </Property> <Property Name="ItemArray"> <Property>ServiceGroup1</Property> <Property>15</Property> <Property>28</Property> </Property> <Property Name="HasErrors">False</Property> </Object> <Object> <Property Name="serviceGroupName">ServiceGroup1</Property> <Property Name="numAccounts">45</Property> <Property Name="numDevices">69</Property> <Property Name="RowError" /> <Property Name="RowState">Unchanged</Property> <Property Name="Table"> <Property>System.Data.DataRow</Property> </Property> <Property Name="ItemArray"> <Property>ServiceGroup1</Property> <Property>45</Property> <Property>69</Property></Property><Property Name="HasErrors">False</Property>\[/code\]And one last thing. If it's possible to remove the excess bloat from the XML, as you can see it doubles the data output because it creates a node named ItemArray with all of the same information.I hope this is easy enough to understand. If you need any more information, please let me know. And thank you in advance for any and all help.