Announcement

Collapse
No announcement yet.

ETL with some programming in it.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • ETL with some programming in it.

    My boss is asking me to reconcile a list of IT assets provided by discovery scans with the data base of IT assets that exist in the Configuration Management Data Base. We need the contractor to create a repeatable process by which discovered assets can be added to the DB.

    Is there anyone out there that has done this before? Fpr a start this is my thought , not for sure how it works? I can't envision the practical approach.

    1. Tenable line of product with python API--Identitfied as the scan engine
    2. Extracting the discovered asset from the repository using python (extracting data via API)
    3. Create a SQL database TABLE for the records which could be accessed with access front end.
    4. Automate the process (ETL). Is there any adapter that can help with this without manual update.

    My biggest is how to bring it all together in a practical sense.

    Skills
    1. Powershell
    2. Python
    3. Text manipulation (I can deal with this one well)
    4. JSON

    OTHER tools
    3. ServiceNow

    If you can put the idea together if you have done this before thanks.

  • #2
    Re: ETL with some programming in it.

    tbh, I am not clear what you want done - a statement that it IS possible, or someone to do the work for you.

    Presumably "Tenable" is the discovery tool? If so, it appears to produce XML result files (see http://static.tenable.com/documentat...ile_format.pdf) which SSIS (SQL Server integration services) can read and process into a suitable database table or tables. SSIS packages can then be scheduled.
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: ETL with some programming in it.

      Ossian thanks for responding to me.

      I will like to let you know what I am trying to achieve in a step-by-step fashion.

      1. Asset Identification:
      We try to inventory the assets: It is like head count with the help of tools.
      2. Tools are Tenable (A) and SCCM (B)
      3. We proceed to reconcile the assets found.
      *************Reconciling the asset is actually its own process**********
      4. We must produce matching asset by data manipulation: We compare system A and B and take out the union as reconciled. Otherwise we proceed to the next level.
      5. Verifying the unreconciled assets to see the possibility of validating them with other systems (VCM or ECM) if possible.
      6. If they can in fact be verified then they can be validated as reconciliation candidates.
      7. If in fact any asset or computer can't be validated then it can't be reconciled.
      8. All the reconciled asset will then be massaged and cleaned up to go into DB.
      9. The ultimate goal is to develop a method of getting update from discovery in an automatic fashion.

      Is this comparison, updating and transformation in an automatic fashion to a DB possible ?
      One more thing tenable uses Python API. Thanks.

      Comment


      • #4
        Re: ETL with some programming in it.

        Originally posted by Ossian View Post
        tbh, I am not clear what you want done - a statement that it IS possible, or someone to do the work for you.

        Presumably "Tenable" is the discovery tool? If so, it appears to produce XML result files (see http://static.tenable.com/documentat...ile_format.pdf) which SSIS (SQL Server integration services) can read and process into a suitable database table or tables. SSIS packages can then be scheduled.
        I think you can help me here. I mentioned all my steps. I will only like to know how to accomplish these steps especially extracting the asset (server or computer) count from the Tenable scanner ( A ). I know I can get all the assets from SCCM ( B ) which only serves as my yardstick. I am not sure how to extract the assets from Tenable. My problem is how to automatically make comparison between the SCCM count and Tenable as to validate any reconciliation. I also will like any new discovery to be reflected on the DB once it is confirmed by A and B comparison. The process must be repeatably automatic.

        I hope this helps you to help me. Thanks.

        Comment


        • #5
          Re: ETL with some programming in it.

          I suspect this is a situation where you will need some specialist consultancy to achieve your goals. I have pointed you at the relevant tool (SSIS) to process the XML files from Tenable into a SQL Server database (which you will need to design based on the data you want to store.

          SCCM already uses SQL server and the database design is reasonably well documented, so a SQL server job to compare the two will not be difficult.

          A consultant will look at your environment and requirements and design a solution to suit your exact needs - the cost will be non-trivial but since you have a need for the information, the money will not be wasted.
          Tom Jones
          MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
          PhD, MSc, FIAP, MIITT
          IT Trainer / Consultant
          Ossian Ltd
          Scotland

          ** Remember to give credit where credit is due and leave reputation points where appropriate **

          Comment


          • #6
            Re: ETL with some programming in it.

            Originally posted by Ossian View Post
            I suspect this is a situation where you will need some specialist consultancy to achieve your goals. I have pointed you at the relevant tool (SSIS) to process the XML files from Tenable into a SQL Server database (which you will need to design based on the data you want to store.

            SCCM already uses SQL server and the database design is reasonably well documented, so a SQL server job to compare the two will not be difficult.

            A consultant will look at your environment and requirements and design a solution to suit your exact needs - the cost will be non-trivial but since you have a need for the information, the money will not be wasted.

            JSON is preferred over xml. In any case I do appreciate your insight. Thanks.

            Comment

            Working...
            X