/build/static/layout/Breadcrumb_cap_w.png

Helpdesk Rule SQL Query Help

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
  • 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? - Wildwolfay 11 years ago
    • 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. - easterdaymatt 11 years ago
      • 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... - Wildwolfay 11 years ago

Answers (2)

Posted by: Wildwolfay 11 years ago
Red Belt
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 :(

 

 

Posted by: Wildwolfay 11 years ago
Red Belt
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?


Comments:
  • 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 - easterdaymatt 11 years ago
    • 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.... - Wildwolfay 11 years ago
      • maybe it's just adding that as an assumption considering the Model=VMware Virtual Platform... - easterdaymatt 11 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ