Duplicated Assets

[Melissa Gurney]

Hi,

I have noticed that a number of our assets have been duplicated in Hornbill. We use the AD asset import which runs each night. There is only one of each in AD of course. For the AD LDAP user  import, there was a line that specified whether users were updated/created but I cannot see that option in the .json file for asset. Is it possible that instead of updating an asset in Hornbill, it is creating a new one if there are changes made in AD?

 

Thanks,

Melissa

[Steve G]

Hi @Melissa Gurney,

Thank you for the post!

With regards to your question, we don't actually have a tool that imports assets directly from Active Directory. We have one that takes input from a SQL (MySQL or SQL Server) database, which I presume is the one you are using?

The DB asset import tool does include logic to ensure that, if correctly configured in the JSON, you should have no duplicate assets in your database when using this tool. Could you please Private Message me a copy of your configuration file so that I can see if this is configured as required?

Many thanks,

Steve

 

[Melissa Gurney]

Hi Steve,

 

Thanks for that. Yes - my apologies. We use a script to grab items from our AD DB to an SQL database. We then extract the info into Hornbill using the asset import tool. I can send the info over to you to double check what we have.

Thanks,

Mel

[clampj]

Hi

We've implemented Ivanti End Point Manager (asset inventory and software deployment system) last week and I'm working on populating the Service Manager CMDB but I'm experiencing duplicate asset records.  Every time the import is run it creates a new record.

I'm using the SQL import tool and we're using the latest version (v1.4.2).

I've populated the config file so that serial number is the unique field rather than machine name (which we cant use because it gets changed under certain circumstances and would therefore create a new record).  Here's an extract from the config file less the sensitive info:

"APIKey": "xxxx",
  "InstanceID": "xxxx",
  "AssetIdentifier":"h_serial_number",
  "LogSizeBytes":1000000,
  "SQLConf": {
      "Driver": "mssql",
      "Server": "xxxx",
      "Database": "xxxx",
      "Authentication":"",
      "UserName": "xxxx",
      "Password": "xxxx",
      "Port": 1433,
      "AssetID": "SystemSerialNumber",
      "Encrypt": false,
      "Query": "SELECT DISTINCT A0.DISPLAYNAME AS [MachineName], A0.TYPE, A1.OSTYPE AS [OperatingSystem], A0.PRIMARYOWNER, A2.BYTESTOTAL, A3.ADDRESS, A4.MODEL AS [SystemModel], A4.MANUFACTURER AS [SystemManufacturer], A4.SERIALNUM AS [SystemSerialNumber]  FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn LEFT OUTER JOIN Memory A2 (nolock) ON A0.Computer_Idn = A2.Computer_Idn LEFT OUTER JOIN TCP A3 (nolock) ON A0.Computer_Idn = A3.Computer_Idn LEFT OUTER JOIN CompSystem A4 (nolock) ON A0.Computer_Idn = A4.Computer_Idn  WHERE (A0.DEVICEID IS NOT NULL)"
  },

This creates duplicates.

I've amended the config file for testing purposes so that we import the serial number into the h_asset_tag field and set this as the asset identifier (to rule out whether the unique value in Service Manager has to be a generic field rather than a type field but I get the same result.

The import works correctly if I use the machine name as the unique value but as already mentioned we cant do this.

Can someone advise where I'm going wrong or if this is a bug?

Thanks

J

[clampj]

Hi @Victor any chance you can take a look at this when you get chance?

Thanks

J

[Victor]

2 minutes ago, clampj said:

any chance you can take a look at this when you get chance?

Done. The Problem lays here:

On 8/13/2018 at 3:10 PM, clampj said:

We've implemented Ivanti End Point Manager

We need to be careful here, Hornbill does not take treason lightly...  it punishes users who dare to disbelief..   

 

Jokes aside, I'll have a look in a bit  

[clampj]

lol

Do Hornbill have an equivalent product?

Thanks - just let me know if you need any more info

[Steve G]

Hi @clampj @Victor
I've just had a look at the code and this is a defect (well - an unfinished feature that's partially made its way in!). It's only searching against the h_name field in the Assets entity for existing records, hence why it never finds a matching asset in Hornbill and duplicates the record.

It's not going to be a straightforward fix - it will definitely need changes to the Service Manager app as well as the import utility. There are complications due to the limited Asset fields currently defined as searchable, plus the fact that the serial number field doesn't live in the main asset entity - it's in an asset type-specific related entity. I'll have a think about the best way to handle this and will post back here when I have a solution.

Thanks,

Steve

[Victor]

15 minutes ago, clampj said:

Do Hornbill have an equivalent product?

Hornbill does not have equivalents!!! Is unique. Magic. Magnificent. One of its kind... The Only... The Preciousss... I think I got carried away a bit..  (less gaming, Victor, less gaming... )

 

On a quick look, I think the problem is with the "AssetIdentifier":"h_serial_number" mapping... If you map this to h_name (like "AssetIdentifier":"h_name") does this resolves the duplication issue?

P.S keep the "AssetID": "SystemSerialNumber" mapping....

 

[Victor]

I should refresh before hitting "Reply" I guess...    ... Oh well... 

[clampj]

Thanks both

Although this is the worst possible answer for us as we will be looking to perform a PC refresh of over 450 devices (spread from London to Aberdeen!) in the coming months and the plan was to have populated the SM CMDB before then to be able to plan the rollout activities.  This will now make this impossible.

I can live with importing the serial number into the asset tag field within the generic fields if this speeds up the fix - I really need to be able to use a different value than the machine name as the unique ID as soon as possible though  

[Victor]

9 minutes ago, clampj said:

I really need to be able to use a different value than the machine name as the unique ID

So, to make sure I understand this right, currently you have assets that have the same name? Are you not using Serial Number as the name of the asset or this is just a workaround? (Sorry for asking but I think I might be missing something here...like the bigger picture...or smaller)

[clampj]

Hi Victor

No all our assets have different machine names.  The machine name we use is of a format xxxyyyyy where xxx is a 3 letter location code of the site it is located at and the yyyyy is the number from the asset tag.  If the machine is relocated to another site we update the name to reflect this (I know this isn't a great idea but it was implemented before my time and before we had any asset management processes.  It will be nigh on impossible to change across the estate given how geographically dispersed the machines are - 21 sites from London to Aberdeen).  This would result in a new record being created rather than updated at the next import.

Granted we would still have the same issue using serial number should a motherboard fail and need replacing but this will be a rare occurrence.

This is how we had Supportworks setup.

It's a bit disappointing that this wasn't picked up in testing and I just hope that because the bug is experienced when using only a slightly different configuration to the default config that it's given a high priority for the fix.

Thanks

J

[Victor]

@clampj - ah, ok, I think I understand...  the asset name (which supposed to be unique and more importantly, should not change) can change which means matching asset based on name is not (currently) feasible... 

[clampj]

@Victor Correct although I'd dispute the part about the machine name shouldn't change but that's just me being picky (if the machine name was purely the asset tag number without the site code prefix then that could still change if the asset tag gets worn or comes off the asset and a new asset tag needs applying) - unless the machine name is something completely random which wouldn't work from a support perspective as service desk wouldn't know the name of the device to be able to remotely connect etc.

If possible can you advise any ETA on fixing this because as I mentioned we have a major PC rollout approaching so I'd need to be able to factor this into the project.

Thanks

J

[Victor]

9 minutes ago, clampj said:

If possible can you advise any ETA on fixing this

Would today be good?  

[Steve G]

@clampj ,

On 8/15/2018 at 11:03 AM, clampj said:

If possible can you advise any ETA on fixing this

How about now  

I had an epiphany last night and managed to fix this entirely in the tool, rather than having to make changes to Service Manager.

The changes are documented on the Hornbill Wiki: https://wiki.hornbill.com/index.php/Database_Asset_Import

As well as the Github page: https://github.com/hornbill/goDBAssetImport

And the link for the new release (v1.5.0) download is here:  https://github.com/hornbill/goDBAssetImport/releases/download/1.5.0/goDBAssetImport_v1_5_0.zip

As a TL;DR, I've removed the AssetIdentifier and SQLConf>AssetID params from the tool config, and bundled these into an enhanced AssetTypes section:

• AssetTypes is now an array of objects instead of a flat object;
• Each object contains the following:
  • AssetType - the Asset Type Name which needs to match a correct Asset Type Name in your Hornbill Instance (the left-string from the AssetTypes section of the previous release)
  • Query - additional SQL filter to be appended to the Query from SQLConf, to retrieve assets of that asset type (the right-string from the AssetTypes section of the previous release)
  • AssetIdentifier - an object containing details to help in the identification of existing asset records in the Hornbill instance. If the value in an imported records DBColumn matches the value in the EntityColumn of an asset in Hornbill (within the defined Entity), then the asset record will be updated rather than a new asset being created:
    • DBColumn - specifies the unique identifier column from the database query
    • Entity - the Hornbill entity where data is stored
    • EntityColumn - specifies the unique identifier column from the Hornbill entity

So for each asset type, you can now define how the tool should look for matching records - the source column from the database query, and the entity (primary asset entity or related asset details entity - specific to the class of asset you're searching for) plus entity column. Note - if the DB column is NULL (so if you don't have a serial number in a returned record for instance), then a new asset will be created - so please make sure you cater for this in your query.

There are examples in the readme, and on the Wiki, but if you need any help just tag me in here and I'll take you through it.

Hope this helps!

Steve

[Victor]

@clampj - we need to get a beer case for or saviour here @Steve G !!!!  

[clampj]

Wow!

This is amazing!

Feature request for the forum - some kind of beer token mechanism!

Seriously though I can't overstate how impressed I am that you've turned this around so quickly.  You've potentially saved my bacon!

I'll let you know if I have any problems.

[clampj]

Hi @Victor and @Steve G

Only just had chance to check this out and I'm receiving a few errors in the import log files and no assets are importing.  The asset types section is configured as per the following:

    "AssetTypes": [{
            "AssetType": "Server",
            "Query": "AND A0.TYPE = 'Server'",
            "AssetIdentifier": {
                "DBColumn": "SystemSerialNumber",
                "Entity": "AssetsComputer",
                "EntityColumn": "h_serial_number"
            }
        },
        {
            "AssetType": "Laptop",
            "Query": "AND A0.TYPE = 'Portable'",
            "AssetIdentifier": {
                "DBColumn": "SystemSerialNumber",
                "Entity": "Asset",
                "EntityColumn": "h_serial_number"
            }
        },
        {
            "AssetType": "Desktop",
            "Query": "AND A0.TYPE IN ('Workstation', 'Machine')",
            "AssetIdentifier": {
                "DBColumn": "SystemSerialNumber",
                "Entity": "Asset",
                "EntityColumn": "h_serial_number"
            }
        },
        {
            "AssetType": "Virtual Machine",
            "Query": "AND A0.TYPE IN ('Virtual Workstation', 'Virtual Server')",
            "AssetIdentifier": {
                "DBColumn": "SystemSerialNumber",
                "Entity": "Asset",
                "EntityColumn": "h_serial_number"

 

Which I'm assuming is correct (the SQL query filter worked with the previous version of the import tool so I know this returns the expected results - plus the log file returns serial numbers for the assets in the inventory tool so I'm confident its fine).

Here's an extract from the log file showing the errors.  The following error occurs across all the asset types (we don't have any server in the new inventory tool yet) and for each asset within those types:

2018/08/16 15:08:27 [DATABASE] Running database query for Desktop assets. Please wait...
2018/08/16 15:08:27 [DATABASE] Query for Desktop assets:SELECT DISTINCT A0.DISPLAYNAME AS [MachineName], A0.TYPE, A1.OSTYPE AS [OperatingSystem], A0.PRIMARYOWNER, A2.BYTESTOTAL, A3.ADDRESS, A4.MODEL AS [SystemModel], A4.MANUFACTURER AS [SystemManufacturer], A4.SERIALNUM AS [SystemSerialNumber]  FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn LEFT OUTER JOIN Memory A2 (nolock) ON A0.Computer_Idn = A2.Computer_Idn LEFT OUTER JOIN TCP A3 (nolock) ON A0.Computer_Idn = A3.Computer_Idn LEFT OUTER JOIN CompSystem A4 (nolock) ON A0.Computer_Idn = A4.Computer_Idn  WHERE (A0.DEVICEID IS NOT NULL) AND A0.TYPE IN ('Workstation', 'Machine')
2018/08/16 15:08:27 [DEBUG] Processing Assets
2018/08/16 15:08:27 [ERROR] API Call failed when searching instance for existing Asset:Invalid HTTP Response: 404
2018/08/16 15:08:27 [DEBUG] API Call XML: <params><application>com.hornbill.servicemanager</application><entity>AssetsDesktop</entity><searchFilter><column>h_serial_number</column><value>CZC2092LZR</value><matchType>exact</matchType></searchFilter><maxResults>1</maxResults></params>
2018/08/16 15:08:27 [DEBUG] Create Asset: CZC2092LZR
2018/08/16 15:08:27 [ERROR] Error running entityAddRecord API for createAsset:Invalid HTTP Response: 404

Appreciate your help with this.

Thanks

J

[Steve G]

@clampj,

The h_serial_number column is in the AssetsComputer entity, not the entity you have defined, when trying to search for assets of type Desktop - which is why the API call is failing. You just need to change the Entity to AssetsComputer for the types where you want to search against the h_serial_number column.

Hope this helps,

Steve

[clampj]

Thanks @Steve G I now understand what you meant by asset entity.

I've changed it but its still not working unfortunately - Its still not creating assets and I get the same errors.

I've DM'd you the log file and config file

Regards

Jason

 

 

 

 

[Victor]

@clampj - not sure if Steve is/will be around at the moment as he is actually on holiday (if he is though that would be great)... It might be the case, for now, you need to settle for the less fortunate option, which would be me   ... so can you PM me the log file and config file as well?

[clampj]

Of course!  Thanks for letting me know

[clampj]

Thanks to @Victor for pointing out the instance name in the config file is case sensitive.

All working now and I can confirm that subsequent imports do not create duplicate assets  

Very happy