Hot!No matching log data

Author
jharris
New Member
  • Total Posts : 1
  • Scores: 0
  • Reward points: 0
  • Joined: 2015/05/19 14:43:43
  • Status: offline
2019/02/12 09:07:08 (permalink)
0

No matching log data

I'm running into the problem (yet again) where a dataset returns records but the report always shows 'No matching log data'. This is m custom query to show how many times per category users clicked through the warning page:
 
select `user` as user, catdesc, count(*)as pair_count
from $log
where $filter and ( ( lower(trim(`msg`)) LIKE lower('%warning%')) AND (`action` = 'passthrough'))
group by t10.user, catdesc
 
I expect to see:
 
UserA  Shopping   12
UserA  Social Networking 20
UserB Shopping 33
etc...
 
This works in dataset testing, but never on the report. The thing I've been focusing on is the group by user. If I just put user, the dataset throws the "must be used in the group by or an aggregate statement warning" but it references t10.user instead of just user. If I understand SQL correctly t10 is a temp table the query uses. If I ignore it and leave group by with 'user' instead of 't10.user'. the report is still blank. I'm at a loss here. Can anyone see what's wrong?
#1
AtiT
Platinum Member
  • Total Posts : 456
  • Scores: 38
  • Reward points: 0
  • Joined: 2012/04/18 12:13:27
  • Location: Prague / Czech Republic
  • Status: offline
Re: No matching log data 2019/02/12 11:06:18 (permalink)
0
Hello,
If I were you I would modify your dataset to something like this:
 
select `user` as user_src,
       `catdesc`,
        count(*) as pair_count
from $log
where $filter
      and lower(`msg`) LIKE '%warning%'
      AND (`action`='passthrough')
group by user_src,
         catdesc
 
It seems to me that you not need to use so much brackets.
I am not sure whether the trim() function s needed where the LIKE is used, maybe some performance tweaking? I don't know.

AtiT
--------------------
NSE 8, CCNP R+S
#2
Jump to:
© 2019 APG vNext Commercial Version 5.5