How to Pull Events that Don't Have an Existing App

[TRAVIS ANDERSON]

Looking for a way to identify events that do not have a respective application. We tend to have a lot of users delete their application but do not delete the event. We are using 11.3.3. The below SQL query I thought would do the trick but it doesn't pull accurate information. My logic is to pull all the apps tied to all events, noted in the ESP_EVENT_RP table then compare that list with all defined applications in the ESP_APPLICATION table to pull out all events who application that is defined to it, does not exist. I'd appreciate any feedback on this.

 

select appl_name,identifier
from esp_event_rp
where not exists
    (select appl_name
    from esp_application
    where esp_event_rp.appl_name = esp_application.appl_name)

[Ravi Kiran Kunduri]

Hi,

If your goal is to identify invalid applications in your entire server , the best way is to use "jobmap" cli command.

It can scan through all events and it will display the application that does not exist.

This cli command is available in R12 and above versions.

 

-> jobmap events("schedmaster.notvalid")

Event Name:SCHEDMASTER.NOTVALID
-----------------------------------------------------------
Generation of this event is skipped
Application UISTORY does not exist.

 

Hope it helps!

Ravi Kiran

[TRAVIS ANDERSON]

Thank you Ravi for the information. I will keep this command in mind for version 12 but as I stated above, we are on 11.3.3 so this does not help me right now. We plan to go to version 12 probably within the next year. There has to be a way of utilizing the DB to get this information. I was previously given a query to identify agents that have not ran any jobs in the last 90 days and it worked really well, I would think something can be used to pull the information I am wanting. I tried modeling after that query to get the data but couldn't get it to work.

[SHARON SHIMANEK]

We get emails from DE manager if an event tries to trigger but doesn't have an application to trigger.

 

If an event tries to trigger but the application doesn't exist you can also grep the tracelog

c80001> grep -a 'did not trigger application' tracelog.txt
Parameter 5: uxunt080:7500: Event C80001.XX_SHARON3 did not trigger application XX_SHARON3 at Tue Sep 19 08:59:00 CDT 2017. Application XX_SHARON3 does not exist.
uxunt080:/opt/dseries/WA/logs
c80001>

 

However if the event exists but doesn't carry a schedule these are hard to find.  what I do a few times per year is open 2 desktop clients one I have Service- Events open the other I am in Define and I compare that way.

 

I tried the Jobmap however if you wild card it it is really slow.  In most cases you would not know what event has an application that doesn't exist so wildcard is needed.  jobmapping to much could be a performance issue with DE

 

sharon

[TRAVIS ANDERSON]

Thank you Sharon for the information. I do get the emails from the DE manager if an event tries to trigger an app that doesn't exist but this and grepping the tracelog is just not enough since we only keep 7 days worth of the logs and this would just be too much manual work. We have nearly 21.5k events and almost 12k applications and I am hoping to get them all in one action. I am still leaning towards a SQL query for this information, that seems to be the quickest and most effective method, if it can be done.

[SHARON SHIMANEK]

give this a try. I found 37 events in our test env that could be deleted, and non in our prod env.

 

select WA.esp_event_rp.appl_name,WA.esp_event_rp.identifier
from WA.esp_event_rp
where not exists
   (select WA.ESP_AMS_DF.IDENTIFIER from WA.ESP_AMS_DF where WA.esp_event_rp.appl_name = WA.esp_AMS_DF.identifier);

 

Sharon

[TRAVIS ANDERSON]

Thanks so much! I've tested this out on our DEV systems and it seems to be 100% accurate, will next run it on TEST then PROD. You save me so much time, thank you Sharon!

[SHARON SHIMANEK]

your welcome!  I really like looking at these type of things because in the end it can help us.  I never knew sql before DE but I have learned so much in the past several years.  I can get pretty determined to figure out how to get the info needed fr the db tables.

 

Sharon

[TRAVIS ANDERSON]

I took one class years ago and never used it until recently. i can tend to figure out the data and what is needed, just not real good with SQL just yet. ran this against our test env, 4,500+ events that need to be deleted . i hate keeping garbage like this and can't wait to get rid of all of them. i'll take this query and see if i can come up with one i've been thinking of. i'm working on identifying apps that have not ran in 180 days. in our env a good bit of apps "runtime name" is different than the actual app name. i'm going to work on this for a bit and see what i can come up with, i don't like to immediately ask someone for the solution without first digging into it myself.

Travis

[SHARON SHIMANEK]

that sounds like a good sql too.  Let me know what you come up, when I have time I might work on it too.

 

sharon

[TRAVIS ANDERSON]

Hi Sharon. Below is what I have come up with so far. However, I cannot get the "not greater than" operator to work. I keep receiving an error of "missing equal sign". Have you or anyone ever come across this? Does !> not represent not greater than? I'm even sure yet if all of this would even get it, still would need to verify if I can get over the error I am getting. In my mind this would get it but I don't know.

 

select ESP_APPLICATION.APPL_NAME,ESP_APPLICATION.END_DATE_TIME
from ESP_APPLICATION
where exists
(select ESP_AMS_DF.IDENTIFIER from ESP_AMS_DF where ESP_APPLICATION.APPL_NAME = ESP_AMS_DF.IDENTIFIER
and ESP_APPLICATION.END_DATE_TIME <sysdate-180 and ESP_APPLICATION.END_DATE_TIME !>sysdate-180)

[SHARON SHIMANEK]

Hi Travis

I have come up with the below.  I am not sure if you move history data from ESP_APPLICATION to H_APPLICATION but we do.  I have verified and it seems pretty accurate but I would and see a few watch outs because. 

 1. NEW applications would not have history. 

  2.  Depending on how much history you keep of each application could vary the results, for frequent interval jobs we don't   keep as much history as applications that load once a day. 

 3.  Some applications are meant to be on request

 

I have not worked with the !> but i have found google searches to be helpful with SQL

 

select WA.ESP_AMS_DF.IDENTIFIER

from WA.ESP_AMS_DF

where WA.ESP_AMS_DF.artifacttype ='APPLICATION' and not exists

   (select WA.esp_APPLICATION.APPL_NAME, WA.esp_APPLICATION.Start_date_time from WA.esp_APPLICATION

   where (WA.ESP_AMS_DF.IDENTIFIER=WA.esp_APPLICATION.APPL_NAME and WA.esp_APPLICATION.START_DATE_TIME > add_months(trunc(sysdate, 'MM'),-6)));

 

select WA.ESP_AMS_DF.IDENTIFIER

from WA.ESP_AMS_DF

where WA.ESP_AMS_DF.artifacttype ='APPLICATION' and not exists

   (select WA.h_APPLICATION.APPL_NAME, WA.h_APPLICATION.Start_date_time from WA.h_APPLICATION

   where (WA.esp_AMS_DF.IDENTIFIER=WA.h_APPLICATION.APPL_NAME and WA.h_APPLICATION.START_DATE_TIME > add_months(trunc(sysdate, 'MM'),-3)));

[TRAVIS ANDERSON]

Thank you Sharon for this info, these look like pretty involved queries but I will try them out. We do not move data to the history tables. I think this would be a great starting point. I would end up sending out the list to the app owners informing them that the listed apps were found to have not ran in 6 months, asking them to verify and delete ones no longer needed. We will likely have some that just have not ran yet but were recently created but I'm betting most just were never deleted since our app teams don't seem to do a whole lot of cleaning up after the app is no longer needed.

 

Appreciate all of your work and time with this!

[TRAVIS ANDERSON]

Sharon, just wanted to inform you that these queries "appear" to not be 100% accurate if you have applications that run with a different name. Example, I took app PAS_TEST_OUTAGE_35 which runs as the same name. It was originally on the list of ones that hadn't ran in 6 months. I then changed the runtime name to PAS_TEST_OUTAGE_35_TEST (the app name is kept the same, PAS_TEST_OUTAGE_35). I then ran the app (PAS_TEST_OUTAGE_35, runtime name of PAS_TEST_OUTAGE_35_TEST) and reran the query, PAS_TEST_OUTAGE_35 was still on that list.

 

You can see where this could be a problem, thinking that PAS_TEST_OUTAGE_35 can be deleted if believed to not have ran in 6 months when in fact it just runs as a different name. Now, I believe I know why it is working like this, because the data in APPL_NAME in ESP_APPLICATION is the runtime name of the app, not the name of the defined app. Of course the runtime name defaults to the appl name if not specified. Just thought I'd reach back out and let you know in case you or anyone was planning on using it. I believe the query does work just fine if all applications when ran, are ran with the same name. However, in our shop that is not always the case, something that was in place before I started in my position, the extra characters in the app when ran are not always at the end of the app name.

 

Let me know your thoughts on this, if you agree or not. I'm not asking you to spend more time on this and come up with a query that does work (if what i have stated is correct). To do so seems like it would be really complex if it can even be done. I'll continue to see what I can come up with on this, if this query can be modified slightly to pull the data I need.

[SHARON SHIMANEK]

your welcome.   I cannot explain my date things I got have several examples from a former coworkers.  I think your way and mine would do the same thing with date.    

 

hm... we don't use different run time names.  Something else to figure out where that data is all stored and how to use it. 

 

Sharon.