topic Search top 4 destinations based on usage by IP and put remaining in others for each IP in Splunk Search

Search top 4 destinations based on usage by IP and put remaining in others for each IP

ahmadsaadwarrai — Thu, 30 May 2019 04:07:08 GMT

I am bit new to splunk.

I want to search top 4 destinations downloads and total ‘Other’ traffic for each source ip.

site    src_ip  destination           download
ALSTP   10.98.66.5  Youtube          150
ALSTP   10.98.66.5  Google            103
ALSTP   10.98.66.5  Microsoft_Updates   85
ALSTP   10.98.66.5  Dropbox          25
ALSTP   10.98.66.5  Other              430
ALSTP   10.98.70.10   Google              12
ALSTP   10.98.70.10   Youtube            650
ALSTP   10.98.70.10   Apple_Updates    502
ALSTP   10.98.70.10   iTunes              300
ALSTP   10.98.70.10   Other            4040

I tried below search but it is not giving the desired result.

| ess eaddr=172.16.9.60:9200 index=nuage_dpi_flowstats-* tsfield=timestamp query="EnterpriseName=Lismore Diocese" 
| eval _time=strftime(_time/1000, "%Y-%m-%d %H:%M:%S") 
| stats sum(TotalMB) as "Total(MB)" by DstIp, L7ClassEnhanced, DestinationNSG 
| sort 5 -"Total(MB)"
| addcoltotals labelfield=L7ClassEnhanced label=Others 
| rename L7ClassEnhanced as Destination, DstIp as SrcIp, DestinationNSG as SourceNSG 
| search NOT SourceNSG=ULT1_NSGX1

SrcIp             Destination             SourceNSG              Total(MB)
10.102.57.21       HTTP                  PMAC_NSG1      395.26492023463334
10.101.144.193   GOOGLE                CASH_NSG1        88.199470520036470
10.101.148.114   YOUTUBE                  CASH_NSG1     201.709753036428500
10.102.81.44       SSL                    PMREG_NSG1       163.37909603115
                                           Others           5072.701299667548000

Re: Search top 4 destinations based on usage by IP and put remaining in others for each IP

pkeenan87 — Thu, 30 May 2019 13:57:10 GMT

You will need to use stats in combination with eval to calculate a sum of the bytes for download traffic and non-download traffic. Hopefully there is a key in your data that will help you differentiate. Here is an example

index=foo
| stats sum(eval(if(action="download", TotalMB, NULL))) AS download_traffic sum(eval(if(action!="download", TotalMB, NULL))) AS other_traffic by src_ip

For reference: https://docs.splunk.com/Documentation/Splunk/7.2.6/Search/Usestatswithevalexpressionsandfunctions

Re: Search top 4 destinations based on usage by IP and put remaining in others for each IP

ahmadsaadwarrai — Thu, 30 May 2019 23:21:15 GMT

Thanks for the reply, I don't want to differentiate download and non-download traffic,

I want to list top 4 destination based on usage and all others destinations as "Others" for each ip, like below:

site src_ip destination download(MB)
ALSTP 10.98.66.5 Youtube 150
ALSTP 10.98.66.5 Google 103
ALSTP 10.98.66.5 Microsoft_Updates 85
ALSTP 10.98.66.5 Dropbox 25
ALSTP 10.98.66.5 Other 430
ALSTP 10.98.70.10 Google 12
ALSTP 10.98.70.10 Youtube 650
ALSTP 10.98.70.10 Apple_Updates 502
ALSTP 10.98.70.10 iTunes 300
ALSTP 10.98.70.10 Other 4040

Re: Search top 4 destinations based on usage by IP and put remaining in others for each IP

ahmadsaadwarrai — Thu, 06 Jun 2019 04:11:55 GMT

Below search gives the desired result:

| ess eaddr=172.16.9.60:9200 index=nuage_dpi_flowstats-* tsfield=timestamp query="EnterpriseName=Lismore Diocese" 
| eval _time=strftime(_time/1000, "%Y-%m-%d %H:%M:%S") 
| stats sum(TotalMB) as "Total(MB)" by DstIp, L7ClassEnhanced, DestinationNSG 
| sort DstIp -"Total(MB)" 
| eval counter = 1 
| streamstats sum(counter) as seqNo by DstIp 
| eval L7ClassEnhanced=case(seqNo < 5, L7ClassEnhanced, seqNo > 4, "Others") 
| rename "Total(MB)" as TotalMB 
| stats sum(TotalMB) as "Total(MB)" by DstIp, L7ClassEnhanced, DestinationNSG 
| fields - counter seqNo 
| sort DstIp -"Total(MB)" 
| search NOT DestinationNSG=ULT1_NSGX1

Re: Search top 4 destinations based on usage by IP and put remaining in others for each IP

richgalloway — Thu, 06 Jun 2019 11:34:31 GMT

@ahmadsaadwarraich If your problem is resolved, please accept an answer to help future readers.