Need a little help on 5.0

[morsnoctus]

trying to combine 2 different datasets and keep getting errors..any help would be greatly appreciated

select attackid, sum(count) as totalnum from $log where $filter and attackid is not null group by attackid having sum(count)>0 order by totalnum desc
 
 select attackname, count(*) as attack_count from $log where $filter and attackname is not null group by attackname order by attack_count desc

Basically I am looking to have the output show Attackname, attackID, and the total counts all correspondent with each other this is on a FAZVM64 v5.0-build0266 131108 (GA Patch 5)

[AtiT]

Hi morsnoctus, Welcome to the forum. Try the dataset: SELECT `attackname`, `attackid`, COUNT(*) as totalnum FROM $log GROUP BY `attackname`, `attackid` ORDER BY totalnum DESC

[morsnoctus]

that worked, but unfortunately, I' ve now been tasked to get the srcip and dstip to display in relation to the attacks as well, but when I do, it appears that there is only 1 src and 1 dst that show up per attack: ie. atackname, attackid, count 1, 1.1.1.1 2.2.2.2

[AtiT]

Hi, I have the same. This is some test message to be logged just for testing the logging function of the fortigate. That is not a real attack.

[morsnoctus]

Just to clarify my previous post, I am trying to get a resulting report that looks like this:

 attackname | attackid | totalnum | srcip | dstip | service
 sample          1234         5   x.x.x.x   y.y.y.y   http
                                  x.x.x.y   y.y.y.y   http
                                  x.x.x.z   y.y.y.y   http
                                  x.x.y.x   y.y.y.y   http
                                  x.x.y.z   y.y.y.y   http
                                  x.x.x.x   y.y.y.y   http
 sample2         4321         5   x.x.x.x   y.y.y.y   http
 

[AtiT]

SELECT `attackname`, `attackid`, COUNT(*) as totalnum, `srcip`, `dstip`, `service` FROM $log GROUP BY `attackname`, `attackid`, `srcip`, `dstip`, `service` ORDER BY totalnum DESC

[morsnoctus]

That' s kind of what I had already written, but when it spits out the results is will have the same attack ID listed over and over again, the totalnum is always 1 and the addresses show up as IPv4-xxxxx instead of x.x.x.x

[AtiT]

Hi, My results seems to be correct: The same attack name will have the same attack id. If the attack was from/to different IP address it will be on a separate line.

[morsnoctus]

That' s what I was getting as well. I was just hoping there was a way to get it a little cleaner and felt that if one attack was 5000 hits, that there was no way it was 5000 different src and dst.