How to merge results by column values?

[Dzmitry]

Hi guys,

I have a search for the host with check_id statuses:

index="..." exec_mode="..." host="..."  check_id="..." | table check_id status

that returns a column with 'passed'/'failed' values

I'm looking for the solution to how to check the column for 'failed' statuses in it and merge all results based on such condition: if 'failed' in statuses then statuses='failed'

Table

check_id	status
check1	Passed
check1	Passed
check1	Failed
check1	Passed

Expected result:

check_id	status
check1	failed

Thank you in advance.

[gcusello · Accepted solution]

Hi @Dzmitry,

you have to use the stats command instead table, something like this:

index="..." exec_mode="..." host="..."  check_id="..." 
| stats dc(status) AS status_count Values(status) AS status BY check_id 
| eval status=if(status_count=1 AND status="Passed","Passed","Failed")
| table check_id status

Ciao.

Giuseppe

[Dzmitry]

Hi Giuseppe
thank you for help!

May I ask one more question?
This part of the query usually returns 6 columns (  host, check_id, status, info, region, msg)

index="..." exec_mode="..." 

 then I use 

| stats dc(status) AS status_count Values(status) AS status BY check_id 
| eval status=if(status_count=1 AND status="Passed","Passed","Failed")
| table check_id status

the table has values 
after it I'd like count statuses by host_name  | stats count(status) BY host_name
however I get 'No results found.'

What am I doing wrong?
Thank you