Hi,
I asked a previous question ( answer-554369) regarding formatting of VPN data, that conducts 5 client posture checks before a user is allowed onto the network.
my query that builds the table of User posture checks and results;
index="netscaler_syslog" packet_engine_name=CLISEC_EXP_EVAL| eval status=if(like(cli_eval_status,"%FAILED%"),"Failed","Passed") | xyseries User sec_module status
This produces a table as below;
User AV_CHECK HD_Encrypt MAC_AV_CHECK MAC_PATCH WINDOWS_PATCH
User1 Passed Passed Failed Failed Failed
User2 Failed Failed Failed Passed Failed
User3 Passed Passed Failed Failed Failed
User4 Failed Failed Failed Passed Failed
User5 Passed Passed Failed Failed Failed
However because i have grouped the the xyseries by User, it summaries all their attempts over the time period. e.g. even if User1 authenticated against the VPN 5 times that day, i will only get one record for that user.
What i am after is the output to look like;
User AV_CHECK HD_Encrypt MAC_AV_CHECK MAC_PATCH WINDOWS_PATCH DATETIME
User1 Passed Passed Failed Failed Failed 07/07/2017 08:53
User2 Failed Failed Failed Passed Failed 07/07/2017 08:53
User2 Passed Passed Failed Failed Failed 07/07/2017 08:25
User3 Failed Failed Failed Passed Failed 07/07/2017 08:00
User1 Passed Passed Failed Failed Failed 07/07/2017 06:23
I know this is limitation with my grouping by User in the xyseries over the time period that forces the results to be summarized down to 1 line per-user, but i am struggling to modify my query to display it grouped User and time of authentication attempt? I have tried using transaction e.g.
transaction User maxspan=3s
But cannot get this to work with the series. Any help or guidance on how to achieve this above would be appreciated.
For completeness, the redacted raw data this is based off to demonstrate it is one line per posture check and no session id is available.
Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87714 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('MAC-ANTIVIR_0') EXISTS FAILED(3) on the client machine
Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87213 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('MAC-PATCH_0') EXISTS FAILED(3) on the client machine
Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87212 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('HD-ENC_0') EXISTS PASSED(0) on the client machine
Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87211 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION(ANTIVIR_0) EXISTS PASSED(0) on the client machine
Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87210 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('PATCH_0_ENABLED') EXISTS FAILED(3) on the client machine
This should get you more or less there:
|makeresults | eval raw="Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87714 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('MAC-ANTIVIR_0') EXISTS FAILED(3) on the client machine::Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87213 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('MAC-PATCH_0') EXISTS FAILED(3) on the client machine::Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87212 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('HD-ENC_0') EXISTS PASSED(0) on the client machine::Jul 21 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87211 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION(ANTIVIR_0) EXISTS PASSED(0) on the client machine::Jul 21 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87210 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('PATCH_0_ENABLED') EXISTS FAILED(3) on the client machine"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<_time>.{14}).*?:\s*User\s+(?<User>[^:]+):.*?Client security check CLIENT.APPLICATION\('?(?<sec_module>[^']+?)'?\)\s*EXISTS\s+(?<status>[^\(]+)"
| eval _time=strptime(_time, "%b %d %H:%M:%S")
| rename COMMENT AS "Everything above generates sample events; everything below is the solution"
| streamstats current=f last(_time) AS prev_time BY User
| eval prev_time=coalesce(prev_time, _time)
| eval break_here = if(((_time - prev_time) < 100), null(), "YES")
| streamstats count(eval(isnotnull(break_here))) AS sessionID BY User
| eventstats min(_time) AS _time BY User sessionID
| eval multikey = User . "::" . _time
| xyseries multikey sec_module status
| rex field=multikey "^(?<User>.*?)::(?<_time>.*)$"
| fields - multikey
This should get you more or less there:
|makeresults | eval raw="Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87714 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('MAC-ANTIVIR_0') EXISTS FAILED(3) on the client machine::Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87213 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('MAC-PATCH_0') EXISTS FAILED(3) on the client machine::Jul 20 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87212 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('HD-ENC_0') EXISTS PASSED(0) on the client machine::Jul 21 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87211 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION(ANTIVIR_0) EXISTS PASSED(0) on the client machine::Jul 21 15:46:43 [VPN_INTERNAL_IP] 20/07/2017:14:46:46 GMT 0-PPE-0 : [Redacted] SSLVPN CLISEC_EXP_EVAL 87210 0 : User User1: - Client IP [CLIENT_IP] - Vserver [VPN_INTERNAL_IP2]:443 - Client security check CLIENT.APPLICATION('PATCH_0_ENABLED') EXISTS FAILED(3) on the client machine"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<_time>.{14}).*?:\s*User\s+(?<User>[^:]+):.*?Client security check CLIENT.APPLICATION\('?(?<sec_module>[^']+?)'?\)\s*EXISTS\s+(?<status>[^\(]+)"
| eval _time=strptime(_time, "%b %d %H:%M:%S")
| rename COMMENT AS "Everything above generates sample events; everything below is the solution"
| streamstats current=f last(_time) AS prev_time BY User
| eval prev_time=coalesce(prev_time, _time)
| eval break_here = if(((_time - prev_time) < 100), null(), "YES")
| streamstats count(eval(isnotnull(break_here))) AS sessionID BY User
| eventstats min(_time) AS _time BY User sessionID
| eval multikey = User . "::" . _time
| xyseries multikey sec_module status
| rex field=multikey "^(?<User>.*?)::(?<_time>.*)$"
| fields - multikey
Thank you, this mostly got me there. However, I decided to revisit the problem, and used transaction to group my events then eval statements over the combined fields to produce my table of VPN events and posture checks.
DO NOT use transaction
; it does not scale and is a guaranteed fail.
if you coalesce your fields and then split them, would that work for you? something like below?
|eval userDateTime=User+"|"+DATETIME | xyseries userDateTime sec_module status|rex field=userDateTime "(?<User>.*)|(?<DATETIME>.*)"|fields - userDateTime
Thanks. that doesn't quite work as the _time splits it back out to 1-line per evaluation since time is down to millisecond.
Could transaction User maxspan=3s be chained into the search query to create a sessionid? this could then be concatenated with the User+sessiodID, and then regex-ed out at the end?