How To: Import Software Licenses With A Software License Import Rule

Version 10

    Verified Product Versions

    Endpoint Manager 9.6Endpoint Manager 2016.xEndpoint Manager 2017.xEndpoint Manager 2018.x





    One of the most common reasons to use Data Analytics is to import licensing information. To import this data, you usually have the options below:


    Pre-built B2B Connector (A connector built and updated by Ivanti for common vendors)

    Web Import

    Universal B2B Connector

    Software License Import


    While our pre-built B2B connectors, and web import rules are quite easy to use, they can fail to retrieve data due to a vendor changing their website, changing their API, or the vendor's infrastructure simply being under too great of a load. In these situations, Software License Import rules are ideal, as they don't rely on a third party site. As long as you have a source of data, such as a csv or xls file, you can import software licensing data.


    Another big benefit to using a Software License Import rule over a pre-built rule is total customization. You decide exactly what data gets imported and where it goes. Going through the extra time to build a custom rule that meets your specific needs is often a better option.


    This guide goes over importing software licenses specifically, not hardware assets. If you need to import hardware assets as well, a Universal B2B Connector is a better option. The acronyms/abbreviations below will be used:


    DA - Data Analytics

    SLM - Software License Monitoring

    DTS - Data Translation Services


    Organizing Your Data


    Before you start building a rule, you first need to check your data source. If your data doesn't show things exactly the way you want, it's much easier to change it ahead of time. You may also notice some fields are missing and will want to address these beforehand.


    In general, you should decide on how you want fields named for specific values, and stick to it. A good example of this is the Vendor field. If I import licenses purchased from CDW, PDS, and Insight, Microsoft might show up in the various forms below:


    Microsoft, Inc


    Microsoft Inc


    While these are the same to you, they aren't the same to DA. So you'll want to decide a common name and stick to it, i.e just "Microsoft". This will make reporting on this data cleaner and easier.


    Next, check to see if you have entries of the same type on multiple lines. For example, having 10 lines for Adobe Reader, all with the same PO Number, Price, Quantity, etc. It's helpful to consolidate these to one line.


    It's also good to remove any non-standard characters, such as ™ or ©. While they aren't guaranteed to cause an issue, they don't really add any value to the import, so why risk it?


    The last thing to check is the location of the data. It's highly recommended to have the data source exist on the LDMS Core server to reduce the chance of network or permissions issues, the resource being moved, etc. That said, it's not a requirement, you'll just want to be sure you have access to the location your data is stored on, as well as the user that the Managed Planet Core Scan Processor service is running under.

    Creating The Rule


    Now that you've cleaned your data and checked your data source, it's time to actually build the rule. To do so, navigate to Tools > Data Analytics > Data Translation Services. Then expand All Types >Software License Import, and select "All Rules".


    Screenshot_8.png Screenshot_11.png


    You'll notice there are already some existing rules. These come with some vendor specific additions. However, for this guide we'll be creating a new, custom rule. Start off by right clicking on "All Rules" and select "New Rule". Name it what you want.


    Next you'll need to provide the format for the data source. Below are the options:


    • Use Current Database Settings
      • This uses the LDMS database as a data source. This isn't commonly used.
    • CSV
      • A CSV File. This can technically be used for any delimited file, since you can specify the delimiter. Your options in this type:
        • Headers in the first row - Check this if your CSV file has headers determining the columns, i.e. Serial Number, PO Number, etc.
        • Delimiter - This is the delimiter we'll look for. By default this is a comma.
        • Use column numbers instead of names - This uses numbers as column headers
        • Character Set - What the character encoding of the data source is. Default will use the system's default encoding.
    • Excel
      • An Excel Sheet. Supports xls and xlsx. Options are:
        • Sheet - Which sheet this rule will look to for data. Useful for using one file for multiple rules, with each looking to a different sheet.
    • ODBC
      • An ODBC connection to a database such as MariaDB, MySQL, etc. Options are:
        • DSN - DSN string for the connection. Leave off user and password.
        • User - User for the connection.
        • Password - Password for the connection.
    • Oracle
      • An Oracle database connection. Options are:
        • TNS Server - Connection details to Oracle TNS Server/Oracle Database Server and database
        • User - User for the TNS connection
        • Password - Password for the TNS connection
    • SQL
      • A Microsoft SQL server connection. Options are:
        • Server - The hostname of the SQL Server
        • Database - The database containing the data
        • User - The user to connect to the database
        • Password - The password for the user


    After you specify your settings, hit next. For this rule, we'll be using an Excel file, and using Sheet1.


    After hitting next, we'll see the columns extracted from Sheet1 of the excel file.




    The first thing you'll want to do is set the Import Key. This should be a value that identifies a given row as that specific row. this should be a unique value like license key or serial number. Select the attribute and click "Edit", and you'll see a "Map CSV Column" window. This is where you decide where to store this value. In the example below, I'm mapping the Serial Number column to the Software License.Serial Number attribute.




    You'll see you have a few options in this window:

    • Don't overwrite existing value - This will prevent future imports from overwriting this value for existing license records.
    • Static Value - This will import the same value for this attribute, for every record, regardless of the data in the xlsx file. This is useful for dummy columns used for values that are always the same. An example might be Purchased By, if the same person bought all of the software being imported.
    • VBScript window - This is where you can write VBScript to modify the data being imported. This can be useful for a variety of situations, such as normalizing data on the fly, converting special values into more readable ones (like department ID to the actual department name), etc.


    Once you map that attribute, you can click the "select import key" button to set that as the import key. Make sure you've mapped the value first, or you won't be allowed to set it as the import key.


    The next thing you'll want to specify is the Product Key. This is essentially the "lookup code" used to associate a product with a product DA knows about. You don't need to worry too much about this, as you'll have the option to manually specify any unmatched values down the line, but setting a good product key will reduce the manual mappings needed. Generally this should be the full name of the product, like "SQL Server Enterprise 2016". Unlike the Import Key, you don't need to map the attribute you specify as the Product Key, and the attribute you probably would have mapped it to (Primary Product) can't be specified manually anyways.


    Below is an example of a finished mapping. You'll notice not all values are mapped, in this case those values will be ignored.





    Running The Rule


    So now you've built a rule, and it's time to run it. To run it, right click the rule. Your main options are "Run now" and "Run now with Exception Handling". You always want to run with exception handling until you've worked out all the kinks in your rule and know it runs smoothly, and imports data the way you want.


    Once we select that, we'll see a perform operations window. Click "Start" to begin the import process. Any messages or errors will be logged in the "Messages" window.



    If the process can't automatically match any of the rows to an existing, known product using the "Product Key" value, you'll get an "Assign Product Lookup Value" window. This is where you specify data about this row. This window is explained here





    Go ahead and work through any lookups you're prompted for. Once you're done, you'll get a cmd popup, which is your SLM license calculation running. Let that finish, and then you're done! You've imported licenses from a custom data source using a custom Software License Import rule!



    Additional Information


    During Product Lookup, some products aren't under very obvious listings. For example, Microsoft Visio is under Microsoft Office Visio.


    If you find that software you're trying to import isn't showing, be sure your DA Content is up to date. If your content is up to date, we may not yet have content for this product, in which case you can submit a content request


    If you're importing a lot of data from multiple sources (such as Insight, CDW and PDS) all at once, consider adding a column to your CSV or Excel file that's basically an "ID". Map it to a custom attribute, and set that to your import key. That ensures you have a line specific value to import with.


    In general, if you have DA, you should use DA to report (ERP reports) and not SLM. However, some features of SLM aren't available to DA, such as reclamation. If you must use SLM, be sure you check your Monitored products to ensure they've been added by DA (The product will show up as such), and check your licenses. Any licenses imported using DA will say "Imported by DTS". Try to stick to using DA products and DA licenses in SLM, and ignore or remove any items not added by DA, to keep things cleaner and avoid confusion.