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
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
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
@ahmadsaadwarraich If your problem is resolved, please accept an answer to help future readers.
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
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: