How to join events?
I want to collect the event names coming from a set of IP addresses and the number of times they appear (along a day for instance); for accomplishing this, I create a query and a trend based on it. My problem is the following: mostly event names contain the time when they happen. The report that I generate has many pages due to this fact, so I would like to merge all these events in only one name. Can you please let me know your approaches for solving this problem?
Thank you very much in advance.
First off, that's bad juju! The event name should be just that - the name, with the timestamp in a timestamp field. Barring fixing that, you can use a variable to pull the event name from, well, the event name , and then use that in your trend instead of the actual event.name field. Do all the events have the same name + timestamp?
Let me see if I guess your way of proceeding: do you mean creating a variable of type Conditional with the corresponding filter checking the beginning of the event.name string? I think in this way I can indeed count the number of events of this type, but not change the name of the event.
The answer of your question is NO, so far only 5 or 6 kinds of events.
No, I would actually use some substring variable magic to accomplish it. Here's an example using the string "Account Login 09/09/09 11:11:11" (without knowing the strings you have, this is all just a guess, but the example would work )
First, get the index of the first /
Then take the substring of everything up to the index:
Then get the index of the last space (the one just before the cut off date starts). Note: This is a ESM 4.5+ function, IIRC.
Then take the substring of the last substring function to remove the numbers:
And viola, you have the event name without the timestamp. Here's what it looks like in the editor:
Keep in mind - this assumes the timestamp uses forward slashes, and that a forward slash doesn't exist in the event name itself. Also, again, this only works on 4.5.
Does this help?
It sounds like you are running into an inherent issue with the name field itself. The problem is you can’t “normalize” data via conditional filters once the data is in the trend. The path Chris mentioned is a good place to start (and you can even do that on the query for the report if you wanted to keep the data “native”). That said there is an element of your question that reminds me of one of the slides I briefed at the conference. AN option is to use a series of filters in a series of conditional variables. So the condition of the trend query is very broad (sourceAddress = whatever) and then for each entry it would check if the event explicitly meets the each conditional variable. If true, then write whatever; else, nothing. Then you concatenate all of those conditional variable strings into one variable and add that field to your Trend. Now you have one field you can do your count of events from that have the “event name”. The MAJOR problem with this approach is it doesn’t scale. You basically have one shot to get it right otherwise you have to recreate the entire Trend. I guess the upshot to that is you can create a Trend and throw the start date back in time.
I beat the we-need-to-be-able-to-do-conditional-evaluations-on-Trends drum fairly loudly at the conference.
Thank you very much for your help. Chris, I followed your recommendation and it partially solved the problem; actually, the situation is a little bit tricky because not all the event names have this structure of type name+time stamp. After thinking several possibilities, I decided to create two trends, one of them containing the events with the special names and the another with the rest of the events. Next, I create a report of two tables (one for each trend).
Maybe there are better solutions, but so far the two trends provide what we were looking for.
Thank you very much for all.