none
SQL query to Pull the Custom Fields RRS feed

Answers

  • Hi,

    I'm no SQL expert, but this can surely be done in a better way, here's a SQL query to retrieve all the SCOM Alert's custom fields and other information:

    Run it against the Operations Manager Data Warehouse

    select top 10000 
    AlertName,
    AlertDescription,
    CustomField1,
    CustomField2,
    CustomField3,
    CustomField4,
    CustomField5,
    CustomField6,
    CustomField7,
    CustomField8,
    CustomField9,
    CustomField10
    from Alert.vAlertResolutionState ars
    inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
    inner join Alert.vAlert alt on ars.alertguid = alt.alertguid


    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    • Marked as answer by RatheeshAV Wednesday, August 14, 2019 2:44 PM
    Tuesday, August 13, 2019 5:03 PM

All replies

  • Hi,

    I'm no SQL expert, but this can surely be done in a better way, here's a SQL query to retrieve all the SCOM Alert's custom fields and other information:

    Run it against the Operations Manager Data Warehouse

    select top 10000 
    AlertName,
    AlertDescription,
    CustomField1,
    CustomField2,
    CustomField3,
    CustomField4,
    CustomField5,
    CustomField6,
    CustomField7,
    CustomField8,
    CustomField9,
    CustomField10
    from Alert.vAlertResolutionState ars
    inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
    inner join Alert.vAlert alt on ars.alertguid = alt.alertguid


    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    • Marked as answer by RatheeshAV Wednesday, August 14, 2019 2:44 PM
    Tuesday, August 13, 2019 5:03 PM
  • Hi,

     

    For the custom Fields, based as I know, with rules it’s possible to pass parameters through the GenerateAlert module , In most cases, it is accomplished via script when it checks the status which it is met,  it will trigger to generate the alerts to add the custom Fields. So we can see them in alerts not in the rule. If in your environment, it is the same, we can query the Custom Fields from the alerts which generated from the rules. For the query, you can refer to the information which Leon provided.

     


    Hope the information can help.

     

    Best regards.

    Crystal


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, August 14, 2019 3:01 AM
  • Hi,

    This will only give generated alerts custom fields, any way we can pull the result from rules configuration.

    Wednesday, August 14, 2019 2:49 PM
  • Hi,

    Could you let us know how did you configure your rule? if the rule is configured to run a script which will update the custom fields, to get these custom fields information, you can check the script directly. 

    Hope the information can help.

    Best regards.

    Crystal


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, August 15, 2019 3:10 AM
  • Hi Crystal,

    This for Custom management pack developed by our team. We are having 100's custom MP with 1000's of Rules. all rules are updated Custom filed 5 some info. I need to Pull the info for all the rules-custom Filed 5.

    Thnx,

    Thursday, August 15, 2019 3:04 PM
  • Hi,

     

    Based on my research these days, if the custom fields are designed directly in the rules in the MP, we can write scripts to export all the rules to query the custom Fields. If not, you can open a case to see if more help we can get

     

    https://support.microsoft.com/en-sg/help/4051701/global-customer-service-phone-numbers

     

    Best regards.

    Crystal


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.



    Monday, August 19, 2019 6:14 AM