December 17, 2014 at 4:53 am
So I have it in my head to design a webpage that my team can pull up at month end and, at a glance, tell what jobs have run, what jobs are running, and what jobs have failed / not run. This wouldn't replace checking our list against the activity monitor, but it would give us a quick heads up on what to check first.
The problem is my Google-Fu does not find the things I think I want to find.
I'm working in Visual Studio 2012. I want VB code that will give me something similiar to the attached picture, but pulling the information from the SQL Server MSDB jobs tables instead of polling file locations to verify the progress of reports (which is what our current Production Monitor process does).
Any hints on what I should be googling or what kind of code I could be pulling to design a page like this?
December 17, 2014 at 5:13 am
Not sure if there is specific articles that would give you a complete solution but my answer for this would be
A repeater control populated with a datasource containing details for Report1,Report1 etc
In the repeater put a gridview containing two columns one imagefield and the other a boundfield to show the red/green image and text
and populate from a query depending on the repeater datasource row ie Report1,Report2 etc
You can google msdn for examples of each control
Far away is close at hand in the images of elsewhere.
Anon.
December 17, 2014 at 5:34 am
David Burrows (12/17/2014)
Not sure if there is specific articles that would give you a complete solution but my answer for this would beA repeater control populated with a datasource containing details for Report1,Report1 etc
In the repeater put a gridview containing two columns one imagefield and the other a boundfield to show the red/green image and text
and populate from a query depending on the repeater datasource row ie Report1,Report2 etc
You can google msdn for examples of each control
Okay, this is my bad. I'm not trying to do Reports. That was a sample pic from an already existing process that views reports (names changed to protect the innocent data). I'm trying to translate that into a SQL Job monitor.
I'll look up the repeater control. But what commands in Visual Studio would I connect to the controls to also connect to SQL Server?
EDIT: I'm also trying to figure out what project type I should create this as. Any suggestions?
December 17, 2014 at 5:52 am
No problem didn't think it was reports, my interpretation was querying a list of servers for status values and showing good/bad (green/red) traffic light for the status text in question.
You could add a SqlDataSource to the form and set it's properties for connection and query and set this as the repeater's datasource
The Repeater1.ItemDataBound event is used to analyse the data row to populate child controls, ie GridView, which also could be populated from a SqlDataSource.
I prefer to code the sql connections and queries (or procedure calls) in the behind code but that is just my preference.
Without a more accurate (obfuscated if need be) view of the webpage ui it is difficult to state much more.
p.s. sorry if this seems to be teaching the obvious but I do not know what level you want the answer to be.
Far away is close at hand in the images of elsewhere.
Anon.
December 17, 2014 at 6:59 am
Brandie Tarvin (12/17/2014)
So I have it in my head to design a webpage that my team can pull up at month end and, at a glance, tell what jobs have run, what jobs are running, and what jobs have failed / not run. This wouldn't replace checking our list against the activity monitor, but it would give us a quick heads up on what to check first.The problem is my Google-Fu does not find the things I think I want to find.
I'm working in Visual Studio 2012. I want VB code that will give me something similiar to the attached picture, but pulling the information from the SQL Server MSDB jobs tables instead of polling file locations to verify the progress of reports (which is what our current Production Monitor process does).
Any hints on what I should be googling or what kind of code I could be pulling to design a page like this?
I built one of those that I receive from each server every morning (I've only got a half dozen that are "production"). It also tells when the next time is that the job will run and the last time the job was modified. It highlights "one time" jobs in Orange and highlights disabled jobs and disabled schedules in Yellow. Of course, any failures or retries are highlighted in Red. I'm considering writing an article on the subject.
But, it's not a web page. It certainly could be because I built it all as HTML from T-SQL but it isn't currently a web page.
Would that do?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 17, 2014 at 7:02 am
I would be interested Jeff 😀
Far away is close at hand in the images of elsewhere.
Anon.
December 17, 2014 at 7:42 am
David Burrows (12/17/2014)
p.s. sorry if this seems to be teaching the obvious but I do not know what level you want the answer to be.
My major issue is that I'm a "power user" when it comes to VS. I know just enough to get by with minor script tasks in SSIS and not enough to actually do anything useful with VS outside of that.
So, please. "Obvious" away. I need all the direction I can get. My approach to this (the wrong approach) has been to grab various VS books looking for that "one thing I need" and coming away with half-measures that don't seem to get me anywhere but frustrated.
December 17, 2014 at 7:42 am
Jeff Moden (12/17/2014)
Brandie Tarvin (12/17/2014)
So I have it in my head to design a webpage that my team can pull up at month end and, at a glance, tell what jobs have run, what jobs are running, and what jobs have failed / not run. This wouldn't replace checking our list against the activity monitor, but it would give us a quick heads up on what to check first.The problem is my Google-Fu does not find the things I think I want to find.
I'm working in Visual Studio 2012. I want VB code that will give me something similiar to the attached picture, but pulling the information from the SQL Server MSDB jobs tables instead of polling file locations to verify the progress of reports (which is what our current Production Monitor process does).
Any hints on what I should be googling or what kind of code I could be pulling to design a page like this?
I built one of those that I receive from each server every morning (I've only got a half dozen that are "production"). It also tells when the next time is that the job will run and the last time the job was modified. It highlights "one time" jobs in Orange and highlights disabled jobs and disabled schedules in Yellow. Of course, any failures or retries are highlighted in Red. I'm considering writing an article on the subject.
But, it's not a web page. It certainly could be because I built it all as HTML from T-SQL but it isn't currently a web page.
Would that do?
YES! That would be fantastic.
February 4, 2015 at 12:13 pm
I have been thinking about this for a while and would love to hear where you are at with it. My idea is to create a process that goes outs and gets a bunch of data from each server and saves it to a set of main tables. Job status, growth rates, error logs, etc.
Then instead of having to wade through a million emails or having to log into a couple dozen servers I could go to a web page that access the main tables and see what severs might be having issues. What jobs (across all servers) failed. Did a database double in size overnight? That kind of stuff.
I know there are third party tools to do this but they never do exactly what you want and they normally cost an arm and a leg.
Has anyone set up this kind of thing from scratch before? I know I can't be the only one who would love to find something like this in open source.
Jim
February 6, 2015 at 10:35 am
Setting this up "from scratch" is sort of what I'm doing. I'm fortunate enough to have an example created by another DBA, but not fortunate enough to have all of his code. I can make the screen via HTML (and have already completed it), but now I have to write the code behind it to visualize the statuses.
That's the part that's kicking my butt.
I'm doing this in Visual Studio 2012 based (right now) off a SQL 2008 Enterprise platform. I am not pulling everything into a single set of tables because we don't have that many servers and I want this real time (minimal latency). Pulling the data would be simple enough (SSIS packages are wonderful things), but that's not what I am interested in at the moment.
EDIT: I did write a TSQL Script that emails the DBA team with job failures on a daily basis, though.
February 9, 2015 at 4:39 am
Jim Youmans-439383 (2/4/2015)
I have been thinking about this for a while and would love to hear where you are at with it. My idea is to create a process that goes outs and gets a bunch of data from each server and saves it to a set of main tables. Job status, growth rates, error logs, etc.Then instead of having to wade through a million emails or having to log into a couple dozen servers I could go to a web page that access the main tables and see what severs might be having issues. What jobs (across all servers) failed. Did a database double in size overnight? That kind of stuff.
I know there are third party tools to do this but they never do exactly what you want and they normally cost an arm and a leg.
Has anyone set up this kind of thing from scratch before? I know I can't be the only one who would love to find something like this in open source.
Jim
Yes, I created a DBA repository at my last work place. I had more than a dozen SQL Server instances (some in private domain some in DMZ) that I was looking after. hundreds of databases / jobs etc...
I got the IT manager to do me a favour and setup an old box as a so-called utility server. I then installed an instance of SQL Server (database engine, SSIS, and SSRS). I also setup a virtual SMPT server and I was ready to go.
My inspiration came from the following link: http://sql-ution.com/category/dbarepository/
I invested about an hour a day for about a month to get mine setup. By the end I had 6 daily reports telling me about backup status of all my databases (200+) how long each backup took, space of backup file, job status on all servers, disk space, DB growth etc...
Well worth it and you will learn a great deal especially if you haven't used SSIS + SSRS before.
If you need any help let me know.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 9, 2015 at 4:56 am
Brandie Tarvin (12/17/2014)
So I have it in my head to design a webpage that my team can pull up at month end and, at a glance, tell what jobs have run, what jobs are running, and what jobs have failed / not run. This wouldn't replace checking our list against the activity monitor, but it would give us a quick heads up on what to check first.The problem is my Google-Fu does not find the things I think I want to find.
I'm working in Visual Studio 2012. I want VB code that will give me something similiar to the attached picture, but pulling the information from the SQL Server MSDB jobs tables instead of polling file locations to verify the progress of reports (which is what our current Production Monitor process does).
Any hints on what I should be googling or what kind of code I could be pulling to design a page like this?
http://sql-ution.com/category/dbarepository/ is a great starting point. I used some ideas from this site and built my own custom DBA repository. You don't really need VS for this kind of thing. SSIS + SSRS would suffice IMHO.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 9, 2015 at 8:33 am
Abu Dina (2/9/2015)
Brandie Tarvin (12/17/2014)
So I have it in my head to design a webpage that my team can pull up at month end and, at a glance, tell what jobs have run, what jobs are running, and what jobs have failed / not run. This wouldn't replace checking our list against the activity monitor, but it would give us a quick heads up on what to check first.The problem is my Google-Fu does not find the things I think I want to find.
I'm working in Visual Studio 2012. I want VB code that will give me something similiar to the attached picture, but pulling the information from the SQL Server MSDB jobs tables instead of polling file locations to verify the progress of reports (which is what our current Production Monitor process does).
Any hints on what I should be googling or what kind of code I could be pulling to design a page like this?
http://sql-ution.com/category/dbarepository/ is a great starting point. I used some ideas from this site and built my own custom DBA repository. You don't really need VS for this kind of thing. SSIS + SSRS would suffice IMHO.
I appreciate the link. But as far as not needed VS for this, I'm not really doing a data repository for system data. I'm doing a web page dashboard of point-in-time month end jobs.
I want little "lights" that change from "not started" to "in progress" to "completed / failed" as the jobs trigger. It's a quick-n-dirty visual of stuff during month end that allows us to focus on the stuff we know has failed rather than comparing a big list of jobs in a word document to the Activity Monitor in SQL Server. I am using Visual Studio and piggy-backing this off a current "daily dashboard" that we have. And while I appreciate links advising me to do other things, I really really want to do learn how to do this.
Plus, we already have a tool designed by someone else. Rather than create a whole 'nother process, I want to build off the tool that exists.
Edit: typo fix. apparently I can't spell this a.m.
February 10, 2015 at 10:02 am
Jeff Moden (12/17/2014)
I built one of those that I receive from each server every morning (I've only got a half dozen that are "production"). It also tells when the next time is that the job will run and the last time the job was modified. It highlights "one time" jobs in Orange and highlights disabled jobs and disabled schedules in Yellow. Of course, any failures or retries are highlighted in Red. I'm considering writing an article on the subject.But, it's not a web page. It certainly could be because I built it all as HTML from T-SQL but it isn't currently a web page.
Would that do?
Sounds good. I have simple t-sql to check instances we have for job status at the step level and would be interested in different solutions.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply