How to bring all similar values rows in a single row after using stats

[dtccsundar]

Hi,

My requirement is like brining all the rows which are similar which has different numerical values in it ,

EX :

Search|stats sum(CountOf_xxx) as "count" sum(CountOf_yyy) as True sum(CountOf_zzz) as False by Platform Environment Tested Rule Severity |sort Tested "Rule"

Using this i am getting repeated rows aligned with same values in column.I tried of using values(Platform) as Platform values(Environment) as Environment. But still i am not getting values in single rows with sum(xxx) Sum(yyy) sum(zzz) all aligned in single rows.

Can anyone help me with answer pls.

[aasabatini]

Hi,

it's not really clear, if you can share your search could be useful.

I try to reply you and sorry if I don't understand your point, try to use after the stats the comand by, and you can remove sort because the stats comand with the by at the end can sort the data for you.

[dtccsundar]

i need to merge the values with similar values ad the related count in one row as we get out put in SQL query.We use Sql view as input to the index here.

Percentage is calculated like (true/machines tested*100,2).

My search :

|eval Percent=round(sum(CountOf_true)/sum(CountOf_MaAchinesTested)*100,2)|eval "Percent of True Machines Tested"=case(Percent==0,0.00,1=1,Percent)|stats sum(CountOf_MachinesTested) as "Machines Tested" sum(CountOf_True) as True sum(CountOf_False_AssetsTested) as "False by Platform Environment "Tested"  Rule Severity "Percent of True Machines Tested"|sort Tested Rule 

I hope this will help you ..

Platform	Environment	Tested	Rule	Severity	Percent of true	machines Tested	true	false
mns	DEV	ALB Security Baseline	3.2.1 ALB's should not be public	High	100.00	43	43	0
mns	LAB	ALB Security Baseline	3.2.1 ALB's should not be public	High	100.00	1	1	0
mns	PROD	ALB Security Baseline	3.2.1 ALB's should not be public	High	100.00	10	10	0
mns	PROD	ALB Security Baseline	3.2.1 ALB's should not be public	High	60.00	15	9	6
mns	PROD	ALB Security Baseline	3.2.1 ALB's should not be public	High	90.91	22	20	2
mns	PSE	ALB Security Baseline	3.2.1 ALB's should not be public	High	100.00	4	4	0
mns	PSE	ALB Security Baseline	3.2.1 ALB's should not be public	High	83.33	6	5	1
mns	PSE	ALB Security Baseline	3.2.1 ALB's should not be public	High	90.91	11	10	1

[aasabatini]

Try this

(unfortunately I don't know the tested field name)

|eval Percent=round(sum(CountOf_true)/sum(CountOf_MaAchinesTested)*100,2)|eval "Percent of True Machines Tested"=case(Percent==0,0.00,1=1,Percent)
|stats sum(CountOf_MachinesTested) as "Machines Tested" sum(CountOf_True) as True sum(CountOf_False_AssetsTested) as "False by Platform Environment "Tested" Rule Severity "Percent of True Machines Tested"
by Platform,Environment,Severity,"Tested Rule"
| table Platform Enviroment Severity "Tested Rule" "machines tested" "Percent of True Machines Tested" True False