Query to compare both row & column value for a server and get unique final status based on comparison.

[Mallik657]

I have a sample data pushed to splunk as below: Help me with splunk query where I want only unique server names with final status as second column. compare both horizantally & vertically for each server second column status, if any of the second column value is No for that server then consider No as final status for that server, if all the second column values are Yes for a Server, then consider that server final status as Yes.

sample.csv:
ServerName,Status
Server1,Yes
Server1,No
Server1,Yes
Server2,No
Server2,No
Server3,Yes
Server3,Yes
Server4,Yes
Server5,No
Server6,Yes
Server6,No
Server6,Yes
Server6,No
Server7,Yes
Server7,Yes
Server7,Yes
Server7,Yes
Server8,No
Server8,No
Server8,No
Server8,No

Output should looks similar to below: 

ServerName,FinalStatus
Server1,No
Server2,No
Server3,Yes
Server4,Yes
Server5,No
Server6,No
Server7,Yes
Server8,No

[Mallik657]

Its not working & not giving required output . below is my sample query:

index=abc laas_appId=XYZ source="/opt/var/directory/sample.csv" | dedup _raw | table ServerName,Status | eval FinalStatus = if(Status="Yes", 1, 0) | eventstats min(FinalStatus) as FinalStatus by ServerName | eval FinalStatus = if(FinalStatus=1, "Yes", "No") | table ServerName, FinalStatus

[richgalloway · Accepted solution]

It would help if you would explain "its not working" and show the output of the sample query.  However, I think I know what the problem is.  I left out a command in the query.

...
| eval FinalStatus = if(Status="Yes", 1, 0)
| eventstats min(FinalStatus) as FinalStatus by ServerName
| stats min(FinalStatus) as FinalStatus by ServerName
| eval FinalStatus = if(FinalStatus=1, "Yes", "No")
| table ServerName, FinalStatus

[richgalloway]

You should be able to do that using eventstats.

...
| eval FinalStatus = if(Status="Yes", 1, 0)
| eventstats min(FinalStatus) as FinalStatus by ServerName
| eval FinalStatus = if(FinalStatus=1, "Yes", "No")
| table ServerName, FinalStatus