CA Client Automation

Expand all | Collapse all

SQL Query

Jump to Best Answer
  • 1.  SQL Query

    Posted 09-01-2016 03:24 PM

    I need help in SQL Query Language get for all agent this fields for ITCM

     

    name 

    OS

    Service Pack

    IP

    MAC

    Vendor

    Model

    Type

    Total Memory

    Agent Last Run

    Serial Number

    Domain

    AM status

    SD Status

    RC status

     

    actually use this but dont have all fields

    select host_name, serial_number, primary_mac_address, asset_tag, vendor_name, last_update_date, creation_date from ca_discovered_hardware



  • 2.  Re: SQL Query

    Posted 09-01-2016 03:44 PM

    The easiest way to get a selection of fields from inventory is to create a report with the fields you want, and schedule it with an export to CSV.

     

    Steve McCormick, ITIL

    CA Technologies

    Principal Services Consultant

    Stephen.McCormick@ca.com

    <mailto:Stephen.McCormick@ca.com>



  • 3.  Re: SQL Query
    Best Answer

    Broadcom Employee
    Posted 09-05-2016 05:38 AM

    Hello Jonathan,

     

    The SQL Query is complex as some information is stored in General Inventory table (inv_generalinventory_item).

    Here is an example of SQL Query :

     

     

    SELECT
    host_name [Name],
    d.class_name [Class Name],
    OS_Name.item_value_text [OS Name],
    OS_SP.item_value_text [Service Pack],
    a.ip_address [IP Address],
    primary_mac_address [MAC Address],
    System_Vendor.item_value_text [Vendor Name],
    System_Model.item_value_text [Model Name],
    System_Type.item_value_text [Type],
    System_TotalMemory.item_value_double/(1024*1024*1024) [Total Memory Gb],
    dateadd ( ss, a.last_run_date + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) [Agent Last Run],
    serial_number [Serial Number],
    t.label [Domain Name],
    CASE a.derived_status_am
    WHEN -4 THEN 'Preregistered'
    WHEN -3 THEN 'Agentless'
    WHEN -2 THEN 'Not Installed'
    WHEN -1 THEN 'Legacy'
    WHEN 0 THEN 'Operationnal'
    WHEN 1 THEN 'Alert'
    WHEN 2 THEN 'Warning'
    WHEN 3 THEN 'Information'
    ELSE ''
    END [AM Status],
    CASE
    WHEN a.derived_status_sd=0 THEN 'Operationnal'
    WHEN a.derived_status_sd & 0x00000001 = 0x00000001 THEN 'Locked by RAC'
    WHEN a.derived_status_sd & 0x00000002 = 0x00000002 THEN 'Locked by Move'
    WHEN a.derived_status_sd & 0x00000004 = 0x00000004 THEN 'Locked by Roam'
    WHEN a.derived_status_sd & 0x00000001 = 0x00000080 THEN 'Locked by Migration'
    WHEN a.derived_status_sd & 0x10000000 = 0x10000000 THEN 'Not Installed'
    WHEN a.derived_status_sd & 0x20000000 = 0x20000000 THEN 'Preregistered'
    ELSE ''
    END [SD Status],
    CASE a.derived_status_rc
    WHEN 7 THEN 'Not Installed'
    WHEN 9 THEN 'Preregistered'
    WHEN 401 THEN 'Locked'
    WHEN 402 THEN 'Active Session'
    WHEN 403 THEN 'Listening'
    WHEN 404 THEN 'Not Listening'
    WHEN 405 THEN 'Migrated'
    WHEN 406 THEN 'Stopped'
    ELSE ''
    END [RC Status]
    FROM ca_discovered_hardware h LEFT JOIN ca_agent a ON h.dis_hw_uuid=a.object_uuid
    LEFT JOIN ca_n_tier t ON a.domain_id=t.domain_id
    LEFT JOIN ca_class_def d ON h.class_id=d.class_id
    LEFT JOIN (
    SELECT gi.object_uuid, gi.item_value_text
    FROM inv_generalinventory_item gi(NOLOCK)
    JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
    AND gi.domain_uuid = iname.domain_uuid
    AND gi.item_name_id = iname.item_name_id
    AND iname.item_name = 'Operating System'
    WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$Operating System$' and domain_uuid=gi.domain_uuid)
    ) OS_Name ON OS_Name.object_uuid = h.dis_hw_uuid
    LEFT JOIN (
    SELECT gi.object_uuid, gi.item_value_text
    FROM inv_generalinventory_item gi(NOLOCK)
    JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
    AND gi.domain_uuid = iname.domain_uuid
    AND gi.item_name_id = iname.item_name_id
    AND iname.item_name = 'Service Pack'
    WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$Operating System$' and domain_uuid=gi.domain_uuid)
    ) OS_SP ON OS_SP.object_uuid = h.dis_hw_uuid
    LEFT JOIN (
    SELECT gi.object_uuid, gi.item_value_text
    FROM inv_generalinventory_item gi(NOLOCK)
    JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
    AND gi.domain_uuid = iname.domain_uuid
    AND gi.item_name_id = iname.item_name_id
    AND iname.item_name = 'Vendor'
    WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=gi.domain_uuid)
    ) System_Vendor ON System_Vendor.object_uuid = h.dis_hw_uuid
    LEFT JOIN (
    SELECT gi.object_uuid, gi.item_value_text
    FROM inv_generalinventory_item gi(NOLOCK)
    JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
    AND gi.domain_uuid = iname.domain_uuid
    AND gi.item_name_id = iname.item_name_id
    AND iname.item_name = 'Model'
    WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=gi.domain_uuid)
    ) System_Model ON System_Model.object_uuid = h.dis_hw_uuid
    LEFT JOIN (
    SELECT gi.object_uuid, gi.item_value_text
    FROM inv_generalinventory_item gi(NOLOCK)
    JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
    AND gi.domain_uuid = iname.domain_uuid
    AND gi.item_name_id = iname.item_name_id
    AND iname.item_name = 'Type'
    WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=gi.domain_uuid)
    ) System_Type ON System_Type.object_uuid = h.dis_hw_uuid
    LEFT JOIN (
    SELECT gi.object_uuid, gi.item_value_double
    FROM inv_generalinventory_item gi(NOLOCK)
    JOIN inv_item_name_id iname ON gi.item_name_id = iname.item_name_id
    AND gi.domain_uuid = iname.domain_uuid
    AND gi.item_name_id = iname.item_name_id
    AND iname.item_name = 'Total Memory'
    WHERE gi.item_parent_name_id = (select tree_name_id from inv_tree_name_id where tree_name='$System$' and domain_uuid=gi.domain_uuid)
    ) System_TotalMemory ON System_TotalMemory.object_uuid = h.dis_hw_uuid

     

     

    Regards,

    Jean-Yves



  • 4.  Re: SQL Query

    Posted 09-21-2016 02:21 PM

    Thanks for SQL Steatment, i use this information to create a new View and then connect with Excel to create this dashboard, if you like excel file contact me...

     

     Dashboard



  • 5.  Re: SQL Query

    Posted 09-22-2016 01:02 PM

    Jonathan,

     

    I'm interested in your spreadsheet.  Can you send it to me?

     

    Bill Endraske

    bill.endraske.osv@fedex.com



  • 6.  Re: SQL Query

    Posted 05-25-2018 09:57 AM

    Hi

     

    If you're still here...  that look fantastic.  Could you send it over to me. 

     

    Thanks

     

    remy.dessureault@mern-mffp.gouv.qc.ca



  • 7.  Re: SQL Query

    Posted 02-01-2019 01:34 PM

    I am very interested can you send it to me too thank you very much



  • 8.  Re: SQL Query

    Posted 03-02-2019 11:40 PM

    Hi Jon 

     

    This looks absolutely fantastic. Can you please send this to me 

     

    Thanks

    Santhosh

    Santhosh.bhojraj@kyndleit.com



  • 9.  Re: SQL Query

    Posted 06-29-2017 04:48 PM

    Hi,
    I need a query that returns the number of processors and total disk of each server. How I do?



  • 10.  Re: SQL Query

    Posted 05-25-2018 10:08 AM

    Hi Felipe,

     

    This document is not the direct answer you are looking for, but rather an overview of how to query inventory from the ITCM database, with some examples...

     

    How to use SQL to Query Computer Inventory from th - CA Knowledge 

     

    This should help with this question, and any future requests.

     

    -Brian