https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657817#M227208 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/260645">@Yashvik</a>,</P><P>please try this search:</P><LI-CODE lang="markup">index=_internal source=*license_usage.log* type="Usage" | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | bin _time span=1d | stats sum(b) as b by _time, pool, s, st, h, idx | bin span=1d _time | stats sum(b) AS volumeB by _time idx st | eval volumeB=round(volumeB/1024/1024/1024,2) | sort 20 -volumeB</LI-CODE><P>Ciao.</P><P>Giuseppe</P> Sun, 17 Sep 2023 11:05:47 GMT gcusello 2023-09-17T11:05:47Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657815#M227206 <P>Hello All,<BR />I need to identify the top log sources which are sending large data to Splunk. Tried Licence master dashboard which isn't helping much.&nbsp;</P><P>My requirement is to create a table which contains following fields. e.g: sourcetype, vol_GB, index, percentage.</P> Sun, 17 Sep 2023 06:11:12 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657815#M227206 Yashvik 2023-09-17T06:11:12Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657817#M227208 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/260645">@Yashvik</a>,</P><P>please try this search:</P><LI-CODE lang="markup">index=_internal source=*license_usage.log* type="Usage" | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | bin _time span=1d | stats sum(b) as b by _time, pool, s, st, h, idx | bin span=1d _time | stats sum(b) AS volumeB by _time idx st | eval volumeB=round(volumeB/1024/1024/1024,2) | sort 20 -volumeB</LI-CODE><P>Ciao.</P><P>Giuseppe</P> Sun, 17 Sep 2023 11:05:47 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657817#M227208 gcusello 2023-09-17T11:05:47Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657818#M227209 <P>Hello&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/161352">@gcusello</a>&nbsp;<BR />Thanks a lot for the swift response. the query gives the most of info I was looking for. However, it contains multiple entries for single index. Lets say If Index A has 3 sourcetypes, it appears in 3 rows.&nbsp;<BR />Can we group them in a single row?&nbsp;<BR />e.g:</P><TABLE border="1" width="100.12642225031605%"><TBODY><TR><TD width="18.963337547408344%">_time</TD><TD width="18.078381795195956%">index</TD><TD width="23.640960809102403%">sourcetype</TD><TD width="25.28445006321112%">Vol_GB</TD><TD width="14.15929203539823%">percentage</TD></TR><TR><TD width="18.963337547408344%">17th Sep</TD><TD width="18.078381795195956%">Main&nbsp;</TD><TD width="23.640960809102403%">st1<BR />st2<BR />st3</TD><TD width="25.28445006321112%">100G</TD><TD width="14.15929203539823%">10.00%</TD></TR></TBODY></TABLE> Sun, 17 Sep 2023 13:47:18 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657818#M227209 Yashvik 2023-09-17T13:47:18Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657844#M227223 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/260645">@Yashvik</a>,</P><P>if you're not interested to the value for each sourcetype, but only to know which sourcetypes are in an index, you cam modify the above search in this way:</P><LI-CODE lang="markup">index=_internal source=*license_usage.log* type="Usage" | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | bin _time span=1d | stats sum(b) as b by _time, pool, s, st, h, idx | bin span=1d _time | stats values( st) AS sourcetype sum(b) AS volumeB by _time idx | rename idx AS index | eval volumeB=round(volumeB/1024/1024/1024,2) | sort 20 -volumeB</LI-CODE><P>Ciao.</P><P>Giuseppe</P> Mon, 18 Sep 2023 06:18:21 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657844#M227223 gcusello 2023-09-18T06:18:21Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657853#M227229 <P>Hello&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/161352">@gcusello</a>&nbsp;<BR />Thanks for the response. Unfortunately, I see only empty values for sourcetype column.&nbsp; other 3 fields showing the info.&nbsp;</P> Mon, 18 Sep 2023 07:50:06 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657853#M227229 Yashvik 2023-09-18T07:50:06Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657859#M227230 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/260645">@Yashvik</a>,</P><P>very strange!</P><P>as you can see it works on my Splunk</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="gcusello_0-1695025353570.png" style="width: 400px;"><img src="https://community.splunk.com/t5/image/serverpage/image-id/27222iFEB50B41AA0702CB/image-size/medium?v=v2&amp;px=400" role="button" title="gcusello_0-1695025353570.png" alt="gcusello_0-1695025353570.png" /></span></P><P>did you exactly copied my search?</P><P>Ciao.</P><P>Giuseppe</P> Mon, 18 Sep 2023 08:23:00 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657859#M227230 gcusello 2023-09-18T08:23:00Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657934#M227254 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/161352">@gcusello</a>&nbsp;<BR />I used the same search which you shared above and didn't made any changes. I will share the screenshot shortly as I am getting some errors in uploading the picture.</P> Tue, 19 Sep 2023 01:53:57 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657934#M227254 Yashvik 2023-09-19T01:53:57Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657942#M227258 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/260645">@Yashvik</a>,</P><P>I found an errore, even if it runs on my search, please try again this and check all the rows:</P><LI-CODE lang="markup">index=_internal source=*license_usage.log* type="Usage" | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | bin _time span=1d | stats sum(b) as b by _time, pool, s, st, h, idx | bin span=1d _time | stats values(st) AS sourcetype sum(b) AS volumeB by _time idx | rename idx AS index | eval volumeB=round(volumeB/1024/1024/1024,2) | sort 20 -volumeB</LI-CODE><P>Ciao.</P><P>Giuseppe</P> Tue, 19 Sep 2023 06:22:43 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/657942#M227258 gcusello 2023-09-19T06:22:43Z https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/658063#M227306 <P>Thanks a lot for the response&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/161352">@gcusello</a>&nbsp;, it works.</P> Wed, 20 Sep 2023 00:18:39 GMT https://community.splunk.com/t5/Splunk-Search/How-to-write-a-SPL-query-to-identify-top-20-largest-indexes/m-p/658063#M227306 Yashvik 2023-09-20T00:18:39Z