topic Re: Sonicwall VPN group by field then sum of a field? in Splunk Search

Sonicwall VPN group by field then sum of a field?

mashhoorgulati — Wed, 30 Sep 2020 04:35:39 GMT

Hi,

We are getting data from syslog for ssl vpn login. Here is a sample log.

,,"'0'",,"'-'",,"Thor","'Tunnel'","MCU","'192.168.1.8:0'",,,"'14711'","'197'","'-'","'0'",,,,"Restricted Users"
,"'W'",,"'0x101'","'[::ffff:xxx.xxx.xx.xxx]:13996'","'11343'",,
,"'(Thor)@(BRANCH) (CN=Thor,OU=Restricted Users,OU=MCU (VDI),OU=MCU,DC=MCU,DC=com)'","'-'"
,"Mar 16 03:21:03 192.168.2.92 Mar 16 13:21:03 SSLVPN02 logserver: [16/Mar/2020:13:21:03.645800 +0300]
ADMSSLVPN02 000000 kt 00000000 Info Audit Src='[::ffff:xxx.xxx.xx.xxx]:13996' Auth='-'
User='(Thor)@(BRANCH) (CN=Thor,OU=Restricted Users,OU=MCU (VDI),OU=MCU,DC=MCU,DC=com)' SocksVersion='0x101' Command='Tunnel'
Dest='192.168.3.80:0' Error='0' SrcBytes='11343' DstBytes='14711' Duration='197' VirtualHost='-' PlatformPrefix='W' EquipmentId='-'
AppNumber='0'","2020-03-16T13:21:03.000-0700",,3,16,21,march,3,monday,2020,local,,,,"192.168.2.92",main,,1,,,logserver
,"::_...:::[//:::.+]___________='[:::...]:","udp:514",syslog,"Splunk-WIN",,15,0

Here is the query I've written.

extracted_source="udp:514" Duration="" Tunnel CN="Appa" | rex field=Duration "\'(?P.+)\'" | rex field=Src ":(?P.+):" | eval Start_Time = strftime(strptime(time, "%Y-%m-%dT%H:%M:%S")-Duration, "%D-%T") | eval Duration(minutes)=Round(Duration/60) | eval End_Time = strftime(strptime(time, "%Y-%m-%dT%H:%M:%S"), "%D-%T") | eval User=CN | eval SourceIP=replace (SourceIP, ":ffff:", "") | eval SourceIP=replace (SourceIP, "]", "") | table CN,Start_Time,SourceIP,End_Time,Duration(minutes)

this gives me a result like this.

CN Start_Time SourceIP End_Time Dura(minutes)
Thor 03/16/20-12:20:46 xxx.xxx.xx.xxx 03/16/20-12:59:02 38
Thor 03/16/20-12:58:57 xxx.xxx.xx.xxx 03/16/20-13:08:14 9
Thor 03/16/20-13:08:21 xxx.xxx.xx.xxx 03/16/20-13:10:11 2
Thor 03/16/20-13:10:18 xxx.xxx.xx.xxx 03/16/20-13:12:02 2
Thor 03/16/20-13:12:05 xxx.xxx.xx.xxx 03/16/20-13:17:40 6
Thor 03/16/20-13:17:46 xxx.xxx.xx.xxx 03/16/20-13:21:03 3
Thor 03/16/20-13:21:12 xxx.xxx.xx.xxx 03/16/20-14:12:57 52

I need to make it concise. Desired output I'm trying to get will look like this.

CN Start_Time End_Time Dura(minutes)
Thor 03/16/20-12:20:46 03/16/20-14:12:57 112

CN = UserName
Start_Time = first session's timestamp
End_Time = last session's timestamp
Dura(minutes) = sum of duration of all sessions

How can I achieve this.?

Re: Sonicwall VPN group by field then sum of a field?

manjunathmeti — Thu, 19 Mar 2020 13:04:51 GMT

Hi @mashhoorgulati,

Add this to the end of your query:

<your query> | rename Duration(minutes) as Duration | stats min(Start_Time) as Start_Time, max(End_Time) as End_Time, sum(Duration) as Duration(minutes) by CN

Re: Sonicwall VPN group by field then sum of a field?

woodcock — Thu, 19 Mar 2020 18:29:49 GMT

Like this:

|makeresults | eval _raw="CN   Start_Time        SourceIP       End_Time          Dura(minutes)
Thor 03/16/20-12:20:46 xxx.xxx.xx.xxx 03/16/20-12:59:02 38
Thor 03/16/20-12:58:57 xxx.xxx.xx.xxx 03/16/20-13:08:14 9
Thor 03/16/20-13:08:21 xxx.xxx.xx.xxx 03/16/20-13:10:11 2
Thor 03/16/20-13:10:18 xxx.xxx.xx.xxx 03/16/20-13:12:02 2
Thor 03/16/20-13:12:05 xxx.xxx.xx.xxx 03/16/20-13:17:40 6
Thor 03/16/20-13:17:46 xxx.xxx.xx.xxx 03/16/20-13:21:03 3
Thor 03/16/20-13:21:12 xxx.xxx.xx.xxx 03/16/20-14:12:57 52"
| multikv forceheader=1
| rename Dura_minutes_ AS "Dura(minutes)"

| rename COMMENT AS "everything above generates sample event data; everything below is your solution"

| rename "Dura(minutes)" AS Dura_minutes_
| foreach *Time [ eval <<FIELD>> = strptime(<<FIELD>>, "%m/%d/%y-%H:%M:%S") ]
| stats min(Start_Time) AS Start_Time max(End_Time) AS End_Time sum(Dura_minutes_) AS "Dura(minutes)" BY CN
| fieldformat Start_Time = strftime(Start_Time, "%m/%d/%y-%H:%M:%S")
| fieldformat End_Time = strftime(End_Time, "%m/%d/%y-%H:%M:%S")