×

Join the Conversation

Without signing in, you're just watching from the sidelines. Sign in or Register to connect, share, and be part of the Splunk Community.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ask a Question
puppy0723
New Member
Member since: ‎07-31-2020
‎07-31-2020
Community Statistics
Posts 1
Solutions 0
Karma Given 0
Karma Received 0
Member Since ‎07-31-2020
View All

Search for sample to convert my Pivot Table from e...

by in Splunk Search
‎07-31-2020 07:40 AM
‎07-31-2020 07:40 AM
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     ... View more
Labels
Contact Me
Online Status
Offline
Date Last Visited
‎07-31-2020 12:12 PM