Endpoint Management Advisory Board Community (could be private and need to be a group)

 View Only
  • 1.  Help SQL query to resolved "User's Cost Center" by User

    Posted Mar 21, 2014 02:46 PM

    Below is the current query used to report a Device information.  I need to add “User’s Cost Center” to the report.  The problem is I cannot find how to put this in the query.  The query currently lists the cost center if this information is annoted on the DEVICE information itself.  But what needs to happen is the query pull the “User’s Cost Center” from the User per line of the report.

    By default, the "User's Cost Center" field is not part of the User profile.  We attached this field

    The problem I noticed is that the “User’s Cost Center” column is not located in the vUser view.  In the Console the User’s Cost Center is attached to the User Profile.

    select a._ResourceGuid,

             a.[Asset Type],

             [Status],

             p.[Phone Number],

             a.[Serial Number],

             md.[SIM Card #],

             u.[Name],

             cc.[Cost Center Code] as [Cost Center],

             c.[Comment]

    from vAsset a

                    left join Inv_Mobile_Details md on a._resourceGuid = md._ResourceGuid

                    left join Inv_Phone_Details p on a._resourceGuid = p._ResourceGuid

                    left join ResourceAssociation raOwner on raOwner.ParentResourceGuid = a._resourceGuid     

                                    and raOwner.ResourceAssociationTypeGuid = 'ed35a8d1-bf60-4771-9dde-092c146c485a'

                                                    left join vUser u on u.guid = raOwner.ChildResourceGuid

                   left join Inv_Comment c on a._resourceGuid = c._ResourceGuid

                 

                      left join ResourceAssociation raCC on raCC.ParentResourceGuid = a._resourceGuid    

                                  and raCC.ResourceAssociationTypeGuid = '9bc22d17-c0cf-45d5-9a8f-d62bbafd955d'    

                      left join vCostCenter cc on cc._resourceguid = raCC.ChildResourceGuid

               

    where [Asset Type] in ('Pager', 'Blackberry', 'Cell Phone', 'Mobile Device', 'Aircard')

          and [Status] in ('Active', 'In Stock','Loaner In Stock','Loaner Active')



  • 2.  RE: Help SQL query to resolved "User's Cost Center" by User

    Posted Mar 21, 2014 08:04 PM

    You need to join your resource association table (you're using the racc join to attach the computer to the cost center. In reality you need to join it with the user's guid. I don't have that association on my computer, but I do have an object with the guid '9bc22d17-c0cf-45d5-9a8f-d62bbafd955d', this points to the cost center. If you right click on the relationship it shows in your screenshot for the user to cost center and choose properties it will show the guid of that relationship. This should be the value for your resourceassociationtypeguid. Past that I would join the vuser guid table as either the parentresourceguid or the childresourceguid and then associate the vcostcenter table to the other half of that relation. 

     

    Again I don't have that relationship setup, but you will find that the vcomputer guid column is the parentresourceguid and the vcostcenter guid will be the childresourceguid (or vise versa).

     

    I typically find it easiest to turn around and make the association manually first in the gui. You can then select * from resourceassociation where the parentresourceguid =  the guid you found by right clicking the relationship.  You can then select * from vitem  where guid = 'guid found in the in the parentresourceguid'

    to figure out the rest.

    Hope this helps,

     

    Craig



  • 3.  RE: Help SQL query to resolved "User's Cost Center" by User

    Posted Mar 26, 2014 05:49 PM

    cwitter Thank you for the reply.  In my head I understand your reply.  I am fairly new to sql so could you give an example in code what you would change... since you do not have the guid I need you could simply put in "user cost center guid' or something like that as a reference.

    Thank you in advance.



  • 4.  RE: Help SQL query to resolved "User's Cost Center" by User
    Best Answer

    Posted Mar 27, 2014 12:24 PM
      |   view attached

    I attached some code for you to try on this.

    You need to get the resourceassociationtypeguid for this to work you can easily do this by right clicking on the resource association you showed in your screenshot and choosing properties. You then need to take this guid and place it in the two sections labeled 

    'Guid that shows when you right click on the association -- make sure to include the quotes' 

    Be sure to include the single quotes around it or it won't work.

     

    If this doesn't work then you need to swap two things:

     

    Change raCC.ParentResourceGuid

    to

    raCC.ChildResourceGuid 

     

    and  racc.ChildResourceGuid

    to

    racc.ParentResourceGuid

     

     

    Best of luck,

     

    Craig

    Attachment(s)

    txt
    sql_0.txt   1 KB 1 version


  • 5.  RE: Help SQL query to resolved "User's Cost Center" by User

    Posted Mar 27, 2014 01:04 PM

    This worked perfectly.  Thank you cwitter!



  • 6.  RE: Help SQL query to resolved "User's Cost Center" by User

    Posted Mar 27, 2014 01:18 PM

    Glad to help.