User report with bandwidth more then 8 Gb
Good afternoon! I have to make a report with the users whose traffic exceeds 8 GB per month. I formed Daset and it works correctly.
Dataset:
select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out, count(*) as sessions from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by user_src having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>8589934592 order by bandwidth desc
Answer dataset:
User135,369,256,69134,364,302,1501,004,954,541128,694User221,554,866,67321,068,549,543486,317,13038,515User320,008,767,42719,264,670,852744,096,575174,823User417,295,362,95115,710,305,4861,585,057,46582,488User515,714,387,91614,970,889,199743,498,71766,21User613,465,465,87413,009,276,608456,189,26697,073User712,417,131,22012,099,502,104317,629,11648,073User812,380,196,76611,028,109,6931,352,087,07357,715User912,321,302,90811,885,993,681435,309,22785,966User1011,798,190,09411,485,453,193312,736,90151,529User1111,141,326,28510,777,496,136363,830,149115,87User1210,907,830,84110,384,630,534523,200,30761,699User1310,867,487,19110,126,494,552740,992,639179,881User1410,862,346,38510,383,281,467479,064,918148,566User1510,403,770,87410,105,319,717298,451,15749,795User169,372,086,8939,032,419,036339,667,85782,852User179,170,300,6448,954,301,109215,999,53521,383User189,169,358,1228,622,143,986547,214,136408,707User199,019,467,9418,627,627,338391,840,60372,401User208,843,710,7148,601,408,629242,302,08513,666User218,667,455,8878,415,188,865252,267,02237,511But after when formed chart and report, it does not output anything.
Answer report:
No matching log data for this reportProbably not correctly performed unification of values and calculation of the amount. Help me please!!!!!!!