Splunk Dev

How to edit my search query for xyseries to group by User and transaction time

MattSmith129
Explorer

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
Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

MattSmith129
Explorer

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.

0 Karma

woodcock
Esteemed Legend

DO NOT use transaction; it does not scale and is a guaranteed fail.

0 Karma

cmerriman
Super Champion

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
0 Karma

MattSmith129
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...