Error 500 when trying to create a report

[oreni]

Hello,

I'm running a saved search which runs perfectly fine, but when I'm trying to use Report Builder I'm getting a 500 error code with the following message: SearchException: Error in 'eval' command: The expression is malformed.

The eval command looks like that:

eval r=[search host="myhost" sourcetype="mysourcetype" event="Searches" |dedup user| lookup UserDate user output user_date | eval today=relative_time(now(),"-d@d") | eval yesterday=relative_time(now(),"-1d@d") | eval valid=if(user_date>=yesterday AND user_activate<date,1,0) | stats sum(valid) as s | rename s as search]

It looks fine to me, and in light of the fact that it runs perfect, I find it kind of strange that I'm getting such response when trying to use Report Builder.

Will appreciate any help with that.

[sideview · Accepted solution]

This means that when the subsearch returns its 'search' value out into the main search, the resulting overall expression is malformed.

Most likely the inner search isn't actually matching any events. When an empty result set comes into stats sum(valid) as s, the stats command will give a null value for 's'. You can see this yourself by running the subsearch manually. stats sum(foo) will be null if there are no rows, or if in the rows there are only null values for foo.

So then what happens is null comes out into the main search as an emptystring value, eval r=, which is indeed malformed.

When using advanced subsearch techniques like this (by which I mean using the special 'search' field name and not using the format command), you're assuming the responsibility for corner cases like this that the less advanced techniques would be doing for you. So you could double down with the hand-rolled approach and put a fillnull command in there that could put some placeholder value in there, or you could factor the r= into the subsearch, and then use an if statement inside the subsearch to glue the "r=" on yourself but only when there's a value.

But both of those would just make it more complicated. Instead I recommend fixing it by making it simpler -- dont use the special search fieldname and let the automatic stuff detect the null case automatically. The following will yield a "r=12" searchterm out to the main search, but it wont put a term there when the value is null.

eval [search host="myhost" sourcetype="mysourcetype" event="Searches" |dedup user| lookup UserDate user output user_date | eval today=relative_time(now(),"-d@d") | eval yesterday=relative_time(now(),"-1d@d") | eval valid=if(user_date>=yesterday AND user_activate<date,1,0) | stats sum(valid) as r]