Topic
3 replies Latest Post - ‏2013-01-28T15:45:49Z by sunjit
SystemAdmin
SystemAdmin
255 Posts
ACCEPTED ANSWER

Pinned topic Two SQL in single Impact Policy

‏2013-01-25T06:07:15Z |
Hi Team,

Is it possible to write 2 SQL's in 1 impact policies.

I have a policy written. Could you please check and update if that will work.I will run this policy in Policy activator.

DataSource="NCOMS_P";
SQL = "select InterfaceName,BranchName,FirstOccurrence,BranchCode,Region,Location from alerts.status where InterfaceName like 'BRI' and Type=2 and Severity=0";
Result = DirectSQL(DataSource, SQL, false);
IF1 = ITNM.InterfaceName;
N1 = ITNM.BranchName;
F1 = ITNM.FirstOccurrence;
B1 = ITNM.BranchCode;
R1 = ITNM.Region;
L1 = ITNM.Location;
DataType="Omnibus";
addDataItem(DataType,ITNM);
DataSource="NCOMS_P";
SQL = "select InterfaceName,BranchName,FirstOccurrence,BranchCode,Region,Location from alerts.status where InterfaceName like 'Serial' and Type=1 and Severity = 5";
Result = DirectSQL(DataSource, SQL, false);
IF2 = LL.InterfaceName;
N2 = LL.BranchName;
F2 = LL.FirstOccurrence;
B2 = LL.BranchCode;
R2 = LL.Region;
L2 = LL.Location;
I1 = LL.IsdnUp;
DataType="Omnibus";
addDataItem(DataType,LL);

if(N1=N2 and B1=B2 and R1=R2 and L1=L2)
{
I1 = F1;
}

exit();

Regards,
Avinash
Updated on 2013-01-28T15:45:49Z at 2013-01-28T15:45:49Z by sunjit
  • sunjit
    sunjit
    126 Posts
    ACCEPTED ANSWER

    Re: Two SQL in single Impact Policy

    ‏2013-01-25T14:05:54Z  in response to SystemAdmin
    Yes, you can run 2 or more SQL's in a policy, there is no such limit.

    I don't think your policy will work - what is ITNM or LL? Is that something that is defined before?
    Here is something you can try:

    DataSource="NCOMS_P";
    SQL = "select InterfaceName,BranchName,FirstOccurrence,BranchCode,Region,Location from alerts.status where InterfaceName like 'BRI' and Type=2 and Severity=0";
    DirectSQL(DataSource, SQL, false);
    ITNM = DataItems[0];//Now ITNM stores the first row that corresponds to the above SQL select's output
    IF1 = ITNM.InterfaceName;
    N1 = ITNM.BranchName;
    F1 = ITNM.FirstOccurrence;
    B1 = ITNM.BranchCode;
    R1 = ITNM.Region;
    L1 = ITNM.Location;
    DataType="Omnibus";
    addDataItem(DataType,ITNM);

    Try something similar for LL also.

    Regards,
    Sunjit Tara
    • SystemAdmin
      SystemAdmin
      255 Posts
      ACCEPTED ANSWER

      Re: Two SQL in single Impact Policy

      ‏2013-01-28T10:28:00Z  in response to sunjit
      Hi Sunjit,

      Thanks for the reply.

      Actually, I am looking for following condition.

      1) In omnibus we are getting events for Leased Line and ISDN links.
      2) Scenario is, Whenever leased line goes down, ISDN links shows up event. So we would have 2 events, 1 for leased line(severity 5) and the other for ISDN up(severity 0).
      3) Now, my requirement is ISDNup column(manually created) for Leased Line event(Down) should be updated with FirstOccurrence of ISDN up event.
      4) For that I would need to check few conditions as branchname, location, region and branchcode should be same for both the events.

      Could you suggest me how can I achieve this via Impact policy.

      I think I would need to go for omnibus event reader service.

      Regards,
      Avinash
      • sunjit
        sunjit
        126 Posts
        ACCEPTED ANSWER

        Re: Two SQL in single Impact Policy

        ‏2013-01-28T15:45:49Z  in response to SystemAdmin
        If you are correlating two events, I would suggest using DirectSQL to check (select) and populate (update) by using apppropriate filters. You can either use the OMNIbusEventReader that triggers the policy with the events that are fetched or can trigger a policy every few seconds using a Policy Activator and pull events from OMNIbus using DirectSQL - both should work.

        Regards,
        Sunjit Tara