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 |
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.
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.