SPL querry

[srujana96]

I have a lookup with server details and OS details(details are in the below table), and the index with CR no., Date, server and status.  so, with respective to the CR no. the total no. of server which are patched is 4(irrespective of the status whether it is success or failure)  and the rest of the server in my lookup table is not patched with respect to that CR no.
i want to write the query in order to get the count of the server which are not patched
with respect to that CR no. that is the count is 11.

Please Note: I need this query to show case the count of server that are not patched in the dashboard.

lookup				Index
Server	OS			CR No.	Date	Server	Status
1	Unix			1	1-Jan	1	Success
2	Win			1	1-Jan	2	Success
3	Unix			1	1-Jan	3	Success
4	Win			1	1-Jan	4	fail
5	Unix			2	25-Dec	5	Success
6	Win			2	25-Dec	6	fail
7	Unix			2	25-Dec	7	fail
8	Win			3	1-Nov	8	Success
9	Unix			3	1-Nov	9	Success
10	Win
11	Unix
12	Win
13	Unix
14	Win
15	Unix

[scelikok · Accepted solution]

Hi @srujana96,

Please try below sample;

index=patch_index 
| stats dc(Server) as patch_count by CR_No 
| append
    [| inputlookup server_details 
    | stats count as server_count] 
| eventstats max(server_count) as server_count 
| eval not_patched_count=server_count-patch_count 
| table CR_No not_patched_count

If this reply helps you an upvote is appreciated.