Miscellaneous

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 :(

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?