Hi,
I am a beginner of SPLUNK and SPL.
Recently I am asked to replace my statistic table from excel into SPLUNK to control windows server status.
Below is my brief raw data in csv format
| systemName | Department | osVersion | patches | DiskD_total_size_TB | DiskD_free_size_TB | Lincense_end |
| HOST00001 | A | Windows 2012 R2 | KB56:KB59:KB94:KB98:KB113:KB118 | 41.8125 | 0.831054688 | 2023/7/25 |
| HOST00002 | A | Windows 2016 | KB107KB131:KB135 | 40.578125 | 3.034179688 | 2023/8/2 |
| HOST00003 | X | Windows 2012 R2 | KB56:KB59:KB94:KB98:KB113 | 41.8125 | 0.538085938 | 2020/10/15 |
| HOST00004 | X | Windows 2012 R2 | KB56:KB59:KB94:KB98:KB113:KB118 | 41.8125 | 10.32324219 | 2022/12/25 |
| HOST00005 | X | Windows 2016 | KB107KB131:KB135 | 41.8125 | 1.803710938 | 2023/7/25 |
| HOST00006 | A | Windows 2019 | KB36:KB37:KB39:KB40:KB41 | 40.578125 | 2.682617188 | 2022/12/25 |
| HOST00007 | Y | Windows 2019 | KB36:KB37:KB39:KB40:KB41:KN50 | 40.578125 | 6.948242188 | 2023/1/25 |
| HOST00008 | Y | Windows 2016 | KB107KB131:KB135 | 41.8125 | 0.034179688 | 2023/1/25 |
| HOST00009 | Y | Windows 2016 | KB107KB131 | 41.8125 | 0.034179688 | 2022/12/25 |
| HOST00010 | A | Windows 2016 | KB107KB131:KB135 | 41.8125 | 6.631835938 | 2019/5/17 |
And I want to summarize the data to a table like this, the key is to judge server is deployed the target patch or not of each version.
| | Windows 2012 R2 | Windows 2012 R2 | Windows 2016 | Windows 2016 | Windows 2019 | Windows 2019 | Total |
| Department | non-patched | KB118 | non-patched | KB135 | non-patched | KB41 | |
| A | 0 | 1 | 0 | 2 | 0 | 1 | 4 |
| X | 1 | 1 | 0 | 1 | 0 | 0 | 3 |
| Y | 0 | 0 | 1 | 1 | 0 | 1 | 3 |
| Total | 1 | 2 | 1 | 4 | 0 | 2 | 10 |
And another table to check license duration is less than one year or not.
| systemName | Department | Lincense_end | License_life < 1 year | License_expired |
| HOST00001 | A | 2023/7/25 | N | N |
| HOST00002 | A | 2023/8/2 | N | N |
| HOST00003 | X | 2020/10/15 | Y | N |
| HOST00004 | X | 2022/12/25 | N | N |
| HOST00005 | X | 2023/7/25 | N | N |
| HOST00006 | A | 2022/12/25 | N | N |
| HOST00007 | Y | 2023/1/25 | N | N |
| HOST00008 | Y | 2023/1/25 | N | N |
| HOST00009 | Y | 2022/12/25 | N | N |
| HOST00010 | A | 2019/5/17 | Y | Y |
I am not familiar with SQL and SPL, could anyone provide some hint or example resouce to me , please help~
I can merely have a SPL to count total of each osVersion and Dept like this , but have no ieda to divide into patched or not and join the sub-search....
index=windows sourcetype=csv search Department In("A", "X", "Y") | stats count(osVersion) by Department patches
