I haven't run any Custom Helpdesk Rules before, so bear with me...What I want to do is take the Hardware>"Virtual Machine: YES" and have that update the corresponding asset field of Physical or Virtual. It would have to Convert YES to Virtual and NO to Physical and update that field. How can I set this up?

1 Comment   [ + ] Show Comment

Comments

  • Question: Which field are you pulling from and which field do you want the information to go from?

    You want someone to put in a ticket, and then for it to read the ASSET information associated with that PC then update a custom field with "PHYSICAL OR VIRTUAL" within the help desk ticket?
    • Trying to pull ORG2.MACHINE.VIRTUAL to ORG2.ASSET_DATA_5.FIELD_23.

      I am just trying to create a way to automatically update our static asset record page to show an accurate Physical or Virtual record. No ticket really involved, just wondered if I could manipulate the ticket rules to accomplish this.
      • I saw this too late :( I built something below because it sounded like you wanted to know if something was virtual or physical....


        Mmkk... yes what you want done makes sense, and you can use a ticket rule to do this, just set it to a schedule... let me see what I can come up with...
Please log in to comment

Answers

1

So here is a VERY basic answer with a LOT of assumptions.

First assumption:  Your COMPUTER asset is it's own asset data set, mine is ASSET_DATA_5, yours MIGHT be the same or different which means you need to match your ASSET_DATA_5 and in that turn, match what FIELD you pull.

This is also assuming your asset data is setup.

Start with the SELECT STATEMENT:

select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,

HD_TICKET.MACHINE_ID,
COMPUTER.FIELD_70,

U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'EMAIL' as NEILEMAIL, /*<<<change your email here */
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
JOIN ASSET_DATA_5 COMPUTER ON COMPUTER.ID = HD_TICKET.MACHINE_ID

where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'

I've added to the select statement: 

HD_TICKET.MACHINE_ID,
COMPUTER.FIELD_70,

Then made this join:

JOIN ASSET_DATA_5 COMPUTER ON COMPUTER.ID = HD_TICKET.MACHINE_ID

Play with this as you like... but this is a ticket rule designed to shoot off only when tickets are created.  It won't work in a "TEST" because of

  and HD_TICKET_CHANGE.ID=<CHANGE_ID>

So the best way to test this rule is to change the SQL to put a change ID number in there and see if it runs (probably a '1' with 0 results) or to test it by creating a new ticket in a test queue while the rule is enabled.

 

ANYYWAAYYSSS this will select the tickets as they are created and give you the information as to whether or not this machine is virtual or physical.  You then have step 2:  The Update query!

 update HD_TICKET 
Set HD_TICKET.CUSTOM_FIELD_VALUE2 = COMPUTER.FIELD_70
where COMPUTER.FIELD_70 <> ''
and (HD_TICKET.ID in (<TICKET_IDS>))


 

This is assuming that the custom field you are trying to populate is CUSTOM_3 on the ticket layout.

Give this a shot... remember the match the test conditions by creating a ticket for the rule to kick off (match the WHERE statements) and remember to make sure the CUSTOM_FIELD_VALUE is set correctly and you're pulling the right ASSET_DATA

 

Let me know, this was kind of a quickie throw out since I saw this the other day and didn't have time to help out :(

 

 

Answered 10/16/2013 by: Wildwolfay
Red Belt

Please log in to comment
1

Editing this post-comments....

Here's the idea, take machine.virtual NO and change the asset data....

Going to build that right now for you sir.

 

 

OKAY----

 

It seems to me the EASIEST way to do this would be to create a rule that says

 UPDATE ASSET_DATA_5
left join MACHINE.VIRTUAL on MACHINE.NAME = ASSET_DATA_5.FIELD_102
Set ASSET_DATA_5.FIELD_70 = "Virtual Machine"
where MACHINE.VIRTUAL = "YES"

 

Your fields may be different (and since my ASSET_DATA_5 doesn't show the NAME data column for some reason, I used my Service Tag column and just matched the computer name

 

BUT ---  I'm getting error trying to access the VIRTUAL table as B1@Local host, which I've never seen before....

 

You can try the above sql by just putting it in a ticket rule SELECT STATEMENT area or putting "SELECT 1" in the select statement and placing the above SQL in the update statement.

I honestly don't even know where the MACHINE.VIRTUAL field is pulling from... as I'm not seeing it in my computer inventory.  Got any idea?

Answered 10/16/2013 by: Wildwolfay
Red Belt

  • I'm getting the same error B1@[...]

    I'm using SQL workbench and the MACHINE table has the column VIRTUAL with YES or NO values. Not sure why the user wouldn't have access to that...

    Here is what works to return the values I want to interchange...I just don;t know how I can say "If Virtual = YES then write Virtual in FIELD_23, if NO then write Physical...


    SELECT VIRTUAL FROM MACHINE
    ^^Returns the VIRTUAL column from MACHINE


    SELECT FIELD_23 FROM ASSET_DATA_5
    ^^Returns the PHYSICAL OR VIRTUAL column from ASSETS
    • Right... but where is the VIRTUAL YES/NO coming from int he first place I guess is the question.....

      Maybe it can't be read / selected because it is reported by the agent?? BUT THAT MAKES NO SENSE....
      • maybe it's just adding that as an assumption considering the Model=VMware Virtual Platform...
Please log in to comment
Answer this question or Comment on this question for clarity