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

[ahmadsaadwarrai]

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

[ahmadsaadwarrai · Accepted solution]

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

[richgalloway]

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

[pkeenan87]

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

[ahmadsaadwarrai]

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:

1. site src_ip destination download(MB)
2. ALSTP 10.98.66.5 Youtube 150
3. ALSTP 10.98.66.5 Google 103
4. ALSTP 10.98.66.5 Microsoft_Updates 85
5. ALSTP 10.98.66.5 Dropbox 25
6. ALSTP 10.98.66.5 Other 430
7. ALSTP 10.98.70.10 Google 12
8. ALSTP 10.98.70.10 Youtube 650
9. ALSTP 10.98.70.10 Apple_Updates 502
10. ALSTP 10.98.70.10 iTunes 300
11. ALSTP 10.98.70.10 Other 4040