Custom Field Population and reporting

[311hits]

Does anyone have any ideas on how to do the following:

I want to populate all of my Custon Field 1 (which I changed the name to read "Systems Engineer") with the name of our engineer who is responsible for that server. We have 150+ servers and 3 engineers. Approximately 50 servers each. Is there an easy way to populate the fields.

then............

I want to be able to report on any server that does NOT have a Systems Engineer assigned to it. The great thing about Lansweeper is that is uses AD, and when a new server is put in place either at our Data Center or somewhere throughotu the US, LS wil pick up the server and add it to the database automatically.

If we had a Dashboard report that would tell us what server do NOT have and SE assigned, it would really be great.

Suggestions, script, or even a little magic would help.

Jim

www.ars.com

[311hits · Accepted solution]

I had my dba create a sql queuery to report on all servers that have Custom Field 1 empty(which is now "Assigned Systems Enginner").

What this does is show us when new servers have been put in place either in our data center or somewhere out in the field (remote locations) and havent been assigned to a specific engineer.

Here is the script we used, and we then added it to the main dashboard. We populated all the field one time using a text input file with servername and assigned engineer. Our dba simply used the inoput file to populated each servers "Custom1" field. To do 160+ servers didnt seems like a good use of time 🙂

Here it is:

Select Top 1000000 dbo.tblComputers.Computername, dbo.tblComputers.ComputerUnique, dbo.tblComputers.Domain, Web40OSName.OSname, tblOperatingsystem.Description, dbo.tblComputersystem.Lastchanged, Web40OSName.Compimage As icon From dbo.tblComputersystem Inner Join dbo.tblComputers On dbo.tblComputersystem.Computername = dbo.tblComputers.Computername Inner Join dbo.web40ActiveComputers On dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername Inner Join tblOperatingsystem On dbo.tblComputers.Computername = tblOperatingsystem.Computername Left Join dbo.tblCompCustom As tcc On tblOperatingsystem.Computername = tcc.Computername Where dbo.tblComputersystem.Domainrole > 1 And (tcc.Custom1 Is Null Or tcc.Custom1 = '') Order By dbo.tblComputers.Computer
Give it a try,

Enjoy.