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

[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,Department,Company,Location

Server1,Yes,Government,DRDO,Bangalore
Server1,No,Government,DRDO,Bangalore
Server1,Yes,Government,DRDO,Bangalore
Server2,No,Private,TCS,Chennai
Server2,No,Private,TCS,Chennai
Server3,Yes,Private,Infosys,Bangalore
Server3,Yes,Private,Infosys,Bangalore
Server4,Yes,Private,Tech Mahindra,Pune
Server5,No,Government,IncomeTax India, Mumbai
Server6,Yes,Private,Microsoft,Hyderabad
Server6,No,Private,Microsoft,Hyderabad
Server6,Yes,Private,Microsoft,Hyderabad
Server6,No,Private,Microsoft,Hyderabad
Server7,Yes,Government,GST Council,Delhi
Server7,Yes,Government,GST Council,Delhi
Server7,Yes,Government,GST Council,Delhi
Server7,Yes,Government,GST Council,Delhi
Server8,No,Private,Apple,Bangalore
Server8,No,Private,Apple,Bangalore
Server8,No,Private,Apple,Bangalore
Server8,No,Private,Apple,Bangalore

Output should looks similar to below:

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

The Status count of any server should show based on search of any of the fields Department, Company, Location. The Department , Company, Location value wont change for any given server. Only status value will change. 

I already have a query to get the output. Below query gives me unique status of each server.

| 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

But what I want is whenever I search a department, or Company or Location, I need to get the Final Status count of each server based on these fields search.  for say, based on Location search, I need to get the final status count for a servers. if i search a Company, I should be able to get final status count for servers based on company. 

I think its like 

| search department="$department$"  Company="$Company$"  Location="$Location$"  

Please help with spunk query. 

[ITWhisperer]

Given that "No" is lexicographically less than "Yes", you could try something like this

| stats min(Status) as Status by ServerName

[PickleRick]

I'm not sure what is supposed to be the connection between first and second search. If you end your search with the table command listing just two fields you can't search by other fields - they are gone from your results.

[Mallik657]

Table ServerName, Final Status is not necessary here. What i want is whenever i search based on department, Company, Location, I should get the count of servers unique in its status. based on condition i mentioned above. If any No in status, then everything to that server status is no. If all status column value are Yes, then only its Yes. So now. I want to display count of Status based on search department, or Company or Location.

Provide the Final Status count for a server,  based search of any of the above fields. Note Final status should be Unique for each server based on if else condition.

[PickleRick]

Sorry, but I still can't understand what the problem is (true, that can be my fault).

I'm not sure if you want something different than

<search by your conditions>
| stats values(status) by host <and the rest of split fields>
| eval finalstatus=if(status="No","No","Yes")
| stats count by <your split fields> finalstatus