SQLTeam.com | Weblogs | Forums

Job scheduled for every 4 hours in multiple servers


#1

Hi All,

I scheduled a job (Health check) for every 4 hours in XX servers individually, everything works fine and I can get reports from all servers daily (every 4 hrs).

But here what I am looking was? I need to take all servers reports in to one file, what I mean was instead of xx number of mails I hopping to collect all servers reports in One file.

Any one had any idea about this scenario? Please share your thoughts here...!


#2

change the jobs to write the reports to a network share where you can access them easily


#3

Thanks Britton. Here I need consolidated reports from XX servers in one mail, suggest me the best automation way (Not like manually)


#4

Powershell would be the ideal tool to do this type of tasks that involve multiple servers. You don't have to be a Powershell expert, there are a number of examples online that you can find if you search for it. For example here.


#5

Why do you need them in an Email at all? I wouldn't do that. I'd change the jobs to write to a shared folder or SharePoint site, then access them from there.

Since you have four machines producing results, there is no way (that I know of) to get them to somehow work together to send one email.


#6

Good question. Every 4 hours we received (DB health reports) from all 8 machines, so in a day reports count is very high and those are accessible from shared folder.

I don't know how to consolidate all in one email, but I'm keep looking in to possible ways to get it done.

Thank you so much for valuable info.


#7

Why email them at all?


#8

I keep asking my clients the same question. "Why do you want to clog up your inbox?" We provided them with a dashboard where they can see all the info they want and more, and in a very easily navigable, nice user interface. Still people want to get e-mails. The responses are like, "E-mail is more proactive", "I can look at e-mails from my iPhone" (even though they can look at the dashboard from the iPhone too, but that requires separate authentication).

The end result is that my processes end up sending a large number of e-mails to various people within the business.


#9

You can save the results to a single file, and then send that as an attachment (or save the data into PS variables and stream them into the body of the e-mail). The link I posted there should have guidance. Here is a simple example of consolidating data from two servers into a single file.

invoke-sqlcmd "select @@servername" -ServerInstance "SERVER_1"  | 
       Out-file -filePath "c:\temp\Serverdata.txt" -Append
invoke-sqlcmd "select @@servername" -ServerInstance "SERVER_2"  | 
       Out-file -filePath "c:\temp\Serverdata.txt" -Append

#10

Yes James. You are 100% correct, If we provide N user interfaces few still needs emails. Because those are easy to access and can respond immediately.

Thanks for detailed explanation and valuable info. :slight_smile: