Hi,
I am trying to add each of the scores being pulled through and / to get the average
here is my search (I am sure there's a better method)
| dbquery "database" "SELECT * FROM new_compliancelist"| stats count(eval(FAILED<1)) as success count as total| eval SANS31 = success/total*100 |fields SANS31 | appendcols [ | dbquery "database" "SELECT * FROM SANS20Score" | WHERE SANSID = "3.2" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS3.2] | appendcols [ | dbquery "database" "SELECT * FROM SANS20Score" | WHERE SANSID = "3.3" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS3.3] | appendcols [ | dbquery "database" "SELECT * FROM SANS20Score" | WHERE SANSID = "3.6" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS3.6] | appendcols [ | dbquery "database" "SELECT * FROM SANS20Score" | WHERE SANSID = "3.7" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS3.7] | appendcols [ | dbquery "database" "SELECT * FROM SANS20Score" | WHERE SANSID = "3.10" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS3.10] | eval SANS3=(SANS3.1+SANS3.2+SANS3.3+SANS3.6+SANS3.7+SANS3.10)/6
The plan is to the output the Average to my traffic light range to see how the overall compliance with that SANS control is going.
It all works apart from
| eval SANS3=(SANS3.1+SANS3.2+SANS3.3+SANS3.6+SANS3.7+SANS3.10)/6
The SANS3 column never displays? (even without the /6)
Any help would be greatly appreciated
Can you try like
('SANS3.1'+ 'SANS3.2'+' SANS3.3'+' SANS3.4'+ 'SANS3.5'+' SANS3.6')/6
Problem might be with "." operator.
Can you try like
('SANS3.1'+ 'SANS3.2'+' SANS3.3'+' SANS3.4'+ 'SANS3.5'+' SANS3.6')/6
Problem might be with "." operator.
Hi Arun,
There were 2 issues
You resolved the first with the above (thanks!!) and the second was a incorrect name (not SANS3.1 should have been SANS31 in the forumla)
| eval SANS3=('SANS31'+'SANS3.2'+'SANS3.3'+'SANS3.10'+'SANS3.7'+'SANS3.6')/6
thanks alot mate!
I'm glad it worked. Can you accept the answer mate 🙂
can you pop something in the answer box - seems I cant mark comments as answers
I will then accept and award points thanks!
Can you try like
('SANS3.1'+ 'SANS3.2'+' SANS3.3'+' SANS3.4'+ 'SANS3.5'+' SANS3.6')/6
Problem might be with "." operator.
Please check you are getting any null values in SANS3.1, SANS3.2, SANS3.3, SANS3.6, SANS3.7, SANS3.10. If null is present then do fill null with 0 value.
Then try,
|fillnull value=0 SANS3.1, SANS3.2, SANS3.3, SANS3.6, SANS3.7, SANS3.10| eval SANS3=(SANS3.1+SANS3.2+SANS3.3+SANS3.6+SANS3.7+SANS3.10)/6
Hi Arun,
There were no null values all showed 0 but I tried that anyway - but sadly not resolved the issues
the table that displays
SANS31 SANS3.10 SANS3.2 SANS3.3 SANS3.6 SANS3.7
2.266010 0 0 100 0 100
I then want another column SANS3 which is each of the searches I listed above added together then / 6
FYI it always complains about the /6 saying it must be a number? I have been removing this just to see if the new column appears but it doesnt
If null is present in any then eval expression will not get executed.