/build/static/layout/Breadcrumb_cap_w.png
07/12/2019 155 views
Previously our asset inventory was not managed within Kace, and a couple months ago we started linking Serials with Assets(this should have been done from the beginning but I wasn't around).  Ideally we want the Asset Name to be the same as the Serial Number.  This is accomplished for everything that has been added in 2019.  I made a change earlier this year which created a TON of new assets showing the MACHINE.NAME with the SN logged.  I want to update all assets in a csv I have to have a new name, model, Purchase date, etc.  I can adjust the mysql update statement, I just need to know how to make Kace run the update.  In the past someone mentioned that this can be done via tickets, but I haven't found information to point me in the right direction.  Any suggestions is greatly appreciated.  Thanks.
0 Comments   [ + ] Show comments

Comments


All Answers

0

For this case, I would recommend performing an asset import instead of messing with the current assets. You may need to delete all of the "bad" assets first, which will lose their history, but I think that it will be a better (and safer) method. I would export all of the assets first, of course.

Answered 07/15/2019 by: chucksteel
Red Belt

  • But is there a way to change asset information. People mentioned via ticket rules in the past, but I haven't found anything that makes sense.
0

Yes, it is possible to change asset information using ticket rules, but it isn't supported, so if you get in trouble, Quest won't help you. Here's an example of something that I have setup. For various reasons, I needed a custom field in the computer asset that contains the serial number. Our assets are named using the serial number, so I created a rule that populates the custom field based on the asset name. Here is the select statement that finds assets without the custom field set properly:

SELECT ASSET.ASSET_DATA_ID as ID, ASSET.NAME, ASSET_DATA_5.FIELD_10014
FROM ASSET
JOIN ASSET_DATA_5 on ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE ASSET.ASSET_TYPE_ID = 5
AND ASSET.NAME != ASSET_DATA_5.FIELD_10014
AND ASSET.NAME != ""

The key to using ticket rules for things other than tickets is that the select statement returns a variable called TICKET_IDS based on the ID column returned in the statement. In this case, it is returning the ASSET_DATA_ID for those assets where FIELD_10014 (my custom field) is not the asset name.

Here is the update statement:

UPDATE ASSET_DATA_5
set FIELD_10014 = (select ASSET.NAME as SERIAL
FROM ASSET
where ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID and ASSET.ASSET_TYPE_ID = 5)
where ID in (<TICKET_IDS>)

You can see how the TICKET_IDS variable is used in the last line.

In your case, you would want to do this the other way, I think. For instance, if you have a field in the asset which contains the serial number, you could select those assets where the NAME doesn't match that field, and then set the NAME using that data. You could then perform the import of the other data (purchase info, etc.) with a normal asset import to update those assets.

For instance, this query will return all of your computer assets that aren't named for the matching computer inventory serial number:

SELECT A.ID, M.NAME as 'Machine', A.NAME as 'Asset', M.BIOS_SERIAL_NUMBER
FROM MACHINE M
JOIN ASSET A on A.MAPPED_ID = M.ID AND A.ASSET_TYPE_ID = 5
WHERE A.NAME != M.BIOS_SERIAL_NUMBER

This statement would update those assets to be named for the matching computer in inventory:

UPDATE ASSET
SET ASSET.NAME = (SELECT BIOS_SERIAL_NUMBER FROM MACHINE WHERE ID = ASSET.MAPPED_ID)
WHERE ASSET.ID in (<TICKET_IDS>)

That should (I didn't test it) change all of those asset names.

If you have a SQL statement that will make all of the changes you want, then you can just put that in the select part of a ticket rule and run the rule. I would urge you to make backups and test thoroughly before doing any of these operations. 

Also, if you haven't already setup a tool like MySQL Workbench I highly recommend it. You will need it to find the names of any custom fields if you are dealing with them.

Answered 07/15/2019 by: chucksteel
Red Belt

  • Pretty damn close to what I was going to do. I didn't know about the TICKET_ID thing.

    select ADVC.ID, ADVC.ASSET_ID, M.NAME, ADVC.MAPPED_ID, ADVC.Asset_Tag__ "Asset_Tag FROM", M.NAME "Asset_Tag TO",ADVC.Serial_Number, ADVC.Model "Model FROM", M.CS_MODEL "Model TO", ADVC.BRAND "BRAND FROM", M.BIOS_MANUFACTURER "BRAND TO" from ASSET_DATA_VIEW_Computer ADVC join MACHINE M on ADVC.MAPPED_ID = M.ID where ( ADVC.Asset_Tag__ != M.Name or ADVC.Model != M.CS_Model or ADVC.Brand != M.BIOS_MANUFACTURER ) and ADVC.SERIAL_NUMBER = M.BIOS_SERIAL_NUMBER and M.CHASSIS_TYPE not like "other";

    I would then do the update to set the ADVC table fields to match these
    ADVC.Asset_Tag__ = M.Name
    ADVC.Model = M.CS_Model
    ADVC.Brand = M.BIOS_MANUFACTURER

    I want to confirm though. The "Where ASSET.ID in (<TICKET_ID>)" Are you paraphrasing, or is that the exact code I would need to do the update?

    update ASSET_DATA_VIEW_Computer ADVC join MACHINE M on M.ID = ADVC.MAPPED_ID Set ADVC.Asset_Tag__ = M.Name and ADVC.Model = M.CS_Model and ADVC.Brand = M.BIOS_MANUFACTURER where ADVC.SERIAL_NUMBER = M.BIOS_SERIAL_NUMBER and M.CHASSIS_TYPE not like "other" and M._BIOS_SERIAL_NUMBER not like "VMware%"
    ....
    and ADVC.ID in (<TICKET_IDS>)?

    Also is there a formatting cheatsheet?
    • I don't normally work with views, instead I use the underlying tables, in this case, it's ASSET_DATA_5. That said, if you can update via that view, more power to you.

      That is the exact code for referencing the TICKET_IDS variable. In your update statement, you don't need to include the additional where statements (ADVC.SERIAL_NUMBER = M.BIOS_SERIAL_NUMBER and M.CHASSIS_TYPE not like "other" and M._BIOS_SERIAL_NUMBER not like "VMware%") because the update will only affect the items identified by the select statement.

      To test this, I would put a limit clause on the select statement, ( LIMIT 1) or an additional where to match a specific asset. I check the box to Email Results so that I can see the rows that were selected.

      I'm not sure what you mean by formatting cheatsheet.
      • Sorry for the delay. I meant formatting of the posts/replies here. You're marking lines as "Code", etc...

        As for the update, I am doing this now

        select A5.ID, A5.FIELD_83 as SERIAL, A5.FIELD_78 as "ASSET TAG FROM" ,M.NAME as "ASSET TAG TO" , FIELD_84 as "MODEL FROM",M.CS_MODEL as "MODEL TO", A5.FIELD_83 as "BRAND FROM",M.BIOS_MANUFACTURER as "BRAND TO" from ASSET_DATA_5 A5 join MACHINE M on A5.FIELD_82 = M.BIOS_SERIAL_NUMBER where A5.FIELD_82 not like "VMware%" and (A5.FIELD_78 = M.NAME or FIELD_84 = M.CS_MODEL or A5.FIELD_83 = M.BIOS_MANUFACTURER);

        and the update would be

        UPDATE ASSET_DATA_5 A5 join MACHINE M on A5.FIELD_82 = M.BIOS_SERIAL_NUMBER set A5.FIELD_78 = M.NAME, FIELD_84 = M.CS_MODEL, A5.FIELD_83 = M.BIOS_MANUFACTURER where A5.ID in (<TICKET_IDS>) limit 1

        Unfortunately the Select is coming up with syntax error when I click "View Ticket Search Results" in the Ticket Rule, but running the select from the R1 user comes back with exactly what I want

        mysql> select A5.ID, A5.FIELD_83 as SERIAL, A5.FIELD_78 as "ASSET TAG FROM" ,M.NAME as "ASSET TAG TO" , FIELD_84 as "MODEL FROM",M.CS_MODEL as "MODEL TO", A5.FIELD_83 as "BRAND FROM",M.BIOS_MANUFACTURER as "BRAND TO" from ASSET_DATA_5 A5 join MACHINE M on A5.FIELD_82 = M.BIOS_SERIAL_NUMBER where A5.FIELD_82 not like "VMware%" and (A5.FIELD_78 = M.NAME or FIELD_84 = M.CS_MODEL or A5.FIELD_83 = M.BIOS_MANUFACTURER);
        +------+-----------------+-------------------+-------------------+----------------------------------+----------------------------------+-----------------+-----------------+
        | ID | SERIAL | ASSET TAG FROM | ASSET TAG TO | MODEL FROM | MODEL TO | BRAND FROM | BRAND TO |
        +------+-----------------+-------------------+-------------------+----------------------------------+----------------------------------+-----------------+-----------------+
        | 430 | HP | MB-CA-06 | MB-CA-06 | | HP Compaq 6200 Pro MT PC | HP | Hewlett-Packard |
        | 1834 | Hewlett-Packard | | MB-CA-06 | HPCompaq6200ProMTPC | HP Compaq 6200 Pro MT PC | Hewlett-Packard | Hewlett-Packard |
        | 469 | | SJ-SAV-02 | SJ-SAV-02 | | HP Compaq 6200 Pro MT PC | | Hewlett-Packard |
        | 472 | | SJ-SAV-03 | SJ-SAV-03 | | HP Compaq 6200 Pro MT PC | | Hewlett-Packard |
        | 479 | | SRO-IS-01 | SRO-IS-01 | | HP Compaq 6200 Pro MT PC | | Hewlett-Packard |
      • The View Ticket Search Results link isn't going to work, since you aren't actually returning tickets. To test you can add LIMIT 1 to the end of the select query and only one record will be selected (it should be the first result that you get when you run the query manually). Then run the rule and see what happens.

        Right now you have the limit 1 on the update statement, I always test with the limit on the select statement. Also, if you turn on the email results option you'll see what was selected.

        Unfortunately, the ITNinja.com site only gives the formatting options on the initial answer, not on the replies.
      • I have completed all of the changes in a test environment and verified everything is working. the Asset_data_5 table has a TON of useful data, and will be updated on a nightly basis based on what changes on machines. Thank you for all of your help!

        Step 1: Bringing in Machine Name as Asset Tag, model name, and brand.
        select A5.ID, A5.FIELD_83 as SERIAL, A5.FIELD_78 as "ASSET TAG FROM" ,M.NAME as "ASSET TAG TO" , FIELD_84 as "MODEL FROM",M.CS_MODEL as "MODEL TO", A5.FIELD_83 as "BRAND FROM",M.BIOS_MANUFACTURER as "BRAND TO" from ASSET_DATA_5 A5 join MACHINE M on A5.FIELD_82 = M.BIOS_SERIAL_NUMBER where A5.FIELD_82 not like "VMware%" and (A5.FIELD_78 != M.NAME or FIELD_84 != M.CS_MODEL or A5.FIELD_83 != M.BIOS_MANUFACTURER);

        UPDATE ASSET_DATA_5 A5 join MACHINE M on A5.FIELD_82 = M.BIOS_SERIAL_NUMBER set A5.FIELD_78 = M.NAME, FIELD_84 = M.CS_MODEL, A5.FIELD_83 = M.BIOS_MANUFACTURER where A5.ID in (<TICKET_IDS>);


        Step 2: Fixing purchase date based on Invoice from CDW
        Select A5.ID,A5.FIELD_81, A5.FIELD_82 from ASSET_DATA_5 A5 where A5.FIELD_82 in ("PC0A00GD","PC08E8MQ"....,"R90RNRG3") order by A5.ID;

        update ASSET_DATA_5 AD5 Set AD5.FIELD_81 = CASE
        WHEN AD5.FIELD_82 = "PC0A00GD" THEN "2016-02-01"
        WHEN AD5.FIELD_82 = "PC08E8MQ" THEN "2016-02-01"
        WHEN AD5.FIELD_82 = "PC08E8MR" THEN "2016-02-01"
        WHEN AD5.FIELD_82 = "PC08E8MV" THEN "2016-02-01"
        WHEN AD5.FIELD_82 = "PC08E8PE" THEN "2016-02-01"
        ...400 lines later
        WHEN AD5.FIELD_82 = "R90RNRFP" THEN "2018-11-08"
        WHEN AD5.FIELD_82 = "R90RNRG3" THEN "2018-11-08"
        ELSE AD5.FIELD_81 END where A5.ID in (<TICKET_IDS>);


        Step 3: Updating Purchase date if No date set, using Either CREATED or OS_INSTALLED_DATE based on which is newer(doesn't matter 99% of it is over 3 years old). Setting lifecycle to +3years
        select A5.ID, A5.FIELD_81, A5.FIELD_88, LEFT(LEAST(M.CREATED,M.OS_INSTALLED_DATE),10) OLDEST, M.CREATED,M.OS_INSTALLED_DATE, M.CS_MODEL,A5.FIELD_82, M.OS_NAME from ASSET_DATA_5 A5 join MACHINE M on M.BIOS_SERIAL_NUMBER = A5.FIELD_82 where A5.FIELD_81 = "0000-00-00" and A5.FIELD_82 not like "VMw%" order by OLDEST;

        update ASSET_DATA_5 A5 join MACHINE M on M.BIOS_SERIAL_NUMBER = A5.FIELD_82 set FIELD_81 = LEFT(LEAST(M.CREATED,M.OS_INSTALLED_DATE),10), FIELD_88 = LEFT(LEAST(M.CREATED,M.OS_INSTALLED_DATE),10) + Interval 3 year where A5.ID in (<TICKET_IDS>);


        Step 4:
        select A5.ID, A5.FIELD_81, A5.FIELD_88, A5.FIELD_82 from ASSET_DATA_5 A5 where A5.FIELD_88 = "0000-00-00" and A5.FIELD_82 not like "VMw%" and A5.FIELD_81 != "0000-00-00";

        update ASSET_DATA_5 A5 set A5.FIELD_88 = A5.FIELD_81 + Interval 3 year where A5.ID in (<TICKET_IDS>);
      • Nice work.

        For your purchase date info, I would recommend creating a new asset type for invoices and import CDW invoice data. Then you can set the purchase date on the asset based on the invoice, instead of having to go through those 400+ case statements. This is also a more future ready solution.

        We use a service desk queue for purchasing, so our internal PO numbers are the ticket number. When assets are uploaded to the SMA we include the PO number and I use that to get the PO date.