Report condition for item in active list not modified in the last hour.

[justin.gruwell@]

I am trying to create a report that will query an active list and display any items not modified in the last hour. The part I am having a hard time with is defining in the query to look for items not modified in the last hour. I think this can be done with a local variable and I have tried a few but I am not getting the correct results. Any suggestions? Have any of you guys defined a similar query before?

[dcorwin25]

So, I am assuming that you already have a rule set up to modify the list each time a certain event comes in (typically agent:043). If that is the case and your rule/active list is working properly, then creating the right query is where we are at. In that case, you would create a query that queried on the active list obviously, establish the fields that you want populated (make sure the "last modified time" is one of the fields) and then the conditions is where you will tell it to alert if not modified within an hour. So basically in the conditions, just put 'Last Modified Time < $Now - 1h'. There shouldn't be a need for local variables.

From there, just create the report using the query you just created.

[justin.gruwell@]

Yeah I thought the same thing but when I tried that it just reports everything in the active list regardless of when it was last modified.

[dcorwin25]

hmmm...that's interesting...we run all of our reports through logger, so my skills on report creations in ESM is minimal. However, based on basic ESM content knowledge, that should work for your report! So, you actually have the "Last Modified Field" in your active list? Maybe to verify that your query is working correctly, create a query viewer using the query. Put the query viewer into a dashboard and see if it populates. I would set the "last modified" thresh hold to like 1 minute though, so you can immediately see if it is working. I'd say if you determine that the query is working correctly, then I'm at a loss. Luckily for you, there are some solid content guys/gals on this forum!

[justin.gruwell@]

I did get it figured out and working using the local variable time difference in hours. I then set a condition that time difference in hours was >= .5.

Seems to be showing the desired results now.

[dcorwin25]

Awesome, I'll definitely take note of that!

[justin.gruwell@]

After further testing I found .5 was not correct so I changed it to .1 and now it is correct.

[daveh009]

Don't underestimate the power of using expiry timers in active lists. If your list has a timeout of 1hr, a base event is generated by ESM when the count on a row has not increased within the timeout value set (deviceEventClassId = activelist:104).

This event includes the key field of the activelist in deviceCustomString4.

Then write your query to look for 104 events and include DCS4 in your report. You might need to use a var to pull out the first value in DCS4 as it's a pipe delimited string.