Query to compare both row & column status for a server and get unique final status count based on dept, Location,Company

[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 horizontally & vertically for each server second column status, The condition is 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

Note : The Department, Location & Company is same for any given server, Only Server status differs for each row of the server.

I already have a query to get the Final Status for a server. Below query gives me unique Final status count 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")
| stats count(FinalStatus) as ServerStatus

But what I want is I have a 3 dropdown on the top of the classic dashboard where

1. Department 2. Company 3. Location   - Dropdown list 

Whenever I select a department, or Company or Location from any of the dropdowns, I need to get the Final Status count of each server based on any of the fields search. For say, If Bangalore is selected from Location dropdown, I need to get the final status count for a servers. if i search a Company DRDO from dropdown, 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.

[Mallik657]

Sorry, I am a beginner. Where is the complete query. When i select a location Bangalore from drop down. The single value count for Final Status column should be displayed for that Location. If i select Company Name DRDO from dropdown, it should display Final Status single value count for that company. 

Eg: Single value count for Bangalore location is 3. Single value count for Company DRDO is 1.

[ITWhisperer]

For your dropdowns, where do the values come from? Are they static (known ahead of time and configured in the dashboard), or dynamic (the results of a search)?

If more than one dropdown is selected, do you want both to used e.g. the count for DRDO in Bangalore is 1?

[Mallik657]

Sample query for dropdown. other tags exist. 

<input type="dropdown" token="department" searchWhenChanged="true">
index=abc laas_appId=xyz source="/opt/src/var/sample.csv" | search department="$department$" Company="$Company$" Location="$Location$" | dedup department | table department


<input type="dropdown" token="Location" searchWhenChanged="true">
index=abc laas_appId=xyz source="/opt/src/var/sample.csv" | search department="$department$" Company="$Company$" Location="$Location$" | dedup Location | table Location


<input type="dropdown" token="Company" searchWhenChanged="true">
index=abc laas_appId=xyz source="/opt/src/var/sample.csv" | search department="$department$" Company="$Company$" Location="$Location$" | dedup Company | table Company

[ITWhisperer]

Just dedup the field you want

<input type="dropdown" token="department" searchWhenChanged="true">
index=abc laas_appId=xyz source="/opt/src/var/sample.csv" | dedup department | table department


<input type="dropdown" token="Location" searchWhenChanged="true">
index=abc laas_appId=xyz source="/opt/src/var/sample.csv" | dedup Location | table Location


<input type="dropdown" token="Company" searchWhenChanged="true">
index=abc laas_appId=xyz source="/opt/src/var/sample.csv" | dedup Company | table Company

You should also add a static value of "*" with a label of "All" to each dropdown

[Mallik657]

Thank you. But this is not my requirement. My requirement is when i select a dropdown like Location Bangalore, I need to get the Final Status count of servers for Bangalore.  If i select Company DRDO, I need to get final Status count for all servers that are DRDO company. same applies for department drop down.

The Final Status of the server is derived from second column of the smaple.csv. Where, if any of the second column status value is No, then Final status is No. If all the status column value is Yes, then Yes for given server.

So final status depends on status column values for that particular server. same applies for other servers also. I need to display total final status count as default. When someone select drop down like Location, then final Status count shoudl refresh based on Location selected. or Department selected or Company selected.

Please help me with splunk query to achieve this.

[ITWhisperer]

Your search (mentioned in the initial post)

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

will filter the events as you require, however, these tokens need a value, otherwise your search will be waiting for input. The value (if not otherwise selected) should be "*". This is what I mean about adding this to your dynamic list of options for your dropdowns.

Your dropdowns need unique values for location, department and company. This is what the dedup is doing for each of the dropdowns.

How is it that this is not satisfying your requirement?

[Mallik657]

Sorry its not complete. Can you help me with complete query to achieve above result. First need to get Final status then display the final status count based on dropdown selection like department, Location, Company. 

I have given the sample.csv raw data. Can you give me single value count query to  get the final status count for servers and then again display the the count based on department, location or company dropdown selection.

[Mallik657]

Dropdown is also dynamically populated from sample.csv file.  Yes if i select multiple from dropdown. it should display accordingly like DRDO, Bangalore for both conditions.

This is actually i need to apply all over my other search results like Pie chart. table format. etc. that are displayed in my dashboard.  But for now, I am trying to sort it out for single value count. so i can apply same logic for pie chart etc.

[ITWhisperer]

What is it that you need help with? You already have a query using the tokens (from the dropdowns?)