Search failing to show last formula

[crossap]

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

[Arun_N_007 · Accepted solution]

Can you try like

('SANS3.1'+ 'SANS3.2'+' SANS3.3'+' SANS3.4'+ 'SANS3.5'+' SANS3.6')/6

Problem might be with "." operator.

[crossap]

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!

[Arun_N_007]

I'm glad it worked. Can you accept the answer mate 🙂

[crossap]

can you pop something in the answer box - seems I cant mark comments as answers

I will then accept and award points thanks!

[Arun_N_007]

Can you try like

('SANS3.1'+ 'SANS3.2'+' SANS3.3'+' SANS3.4'+ 'SANS3.5'+' SANS3.6')/6

Problem might be with "." operator.

[Arun_N_007]

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

[crossap]

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

[Arun_N_007]

If null is present in any then eval expression will not get executed.