Client Management Suite

I thought I would call upon the combined brainpower of all the Connect users and extend a request for assistance.

 

I am looking for a way to automate the assignment of ‘owner’ to an asset.  Now before you say, ‘well, there is the task to assign the primary user as the owner’, I already know that.  I don’t want to assign the ‘primary owner’.  I want to assign the ‘owner’ of the computer to a specific person based on specific criteria.

 

That criteria is, the person whose phone number from the AD User Import matches the first four digits of the computer name.  It just so happens that the phone number of a person in AD is their phone extension. Yes, that is how they do it here.  Don’t ask.  And it just so happens that the computers are named <4digitExtnesion>-<serial number).  Ex.  4567-fhg723a.

 

 We know there is a person in AD that has that phone extension, so it is a perfect match.  I don’t want to have to manually right-click | CMDB Functions | Assignment Functions | Assign Owner  on every computer.

 

In the case where there is more than one user who has the same phone extension, we will simply assign it to the manager of that user (which also comes in from AD.  First one found to match is ok)

 

I am thinking automation policy with a report as the base that has a kick-a$$ sql statement, or possibly some ASDK work, but not really sure, so I wanted to get some ideas.

Sounds like a job that could be easily accomplished wtih Workflow. Not sure if you have any experience with it but it's definitely something to look into. Here is a link to a video that talks about Editing Asset Management Objects.

 

http://www.screencast.com/t/CutBAnIpH

Nothing can be 'easily' accomplished with Workflow.

Would assigning the primary user of the computer as the asset owner work? I recently wrote a SQL query / connector rule to do this. I can dig it up if need be, let me know.

I have performed one such task but while integrating CMS with IBM TSRM. It was done by creating a v iew and assigning owners on the basis of there loaction. reply if you want those queries to be shared.

@JMP51483: Yes, I thought about that.  Would you mind posting that query? Thank you.

Hey Ziggy -

 

After looking over my previous query, It's not going to end up working for you... But I'm all for trying to help you figure this out.

 

Just so I clearly understand a bit better... Each user has a primary phone number, which is also part of their machine name. We need to take these two results and combine them together essentially... right?

 

We should be able to use the AD inventory table and join it against the vComputer table. Once we have a match on phone > computer name. We can grab the primary user and assign it over to the AssetOwner.

Does that make sense?

Correct. Phone number (which is always 4 digitis) of the user from the AD import = the first 4 digits of the computer name for which they are assigned.  So upon a match, I will want to assign that AD user as the assigned owner.  In the cases where thier are more than one match, it would simply need to be assigned to the users manager (as reported in the AD import).

The SQL is easy enough:

SELECT vc.Guid, vc.Name, vu.Guid, vu.[Office Telephone]
FROM vComputer vc
JOIN vUser vu ON vu.[Office Telephone]=LEFT(vc.[Name],4)

This returns the computer GUID, computer name, user GUID, and User's telephone for all matches of a user's 4-digit extension matching the left-most 4 characters of a computer's name.

In fact, if you had four users in AD with the extension 1234, and one computer named 1234-D0GCMD1, I'm sure you could configure it so that each user had 25% ownership.  That would be a fun one to write.

You may find it easier to export to Excel and use Excel as the data source.