How to edit my search to create a table grouped by User based on multiple events?

[MattSmith129]

Hi,

I am struggling with the correct way to approach this. I have VPN data that performs 5 posture checks before clients connect. what I would like to do is summarize the User and the posture check module result in a table.

index="vpn_log"  | eval status=if(like(cli_eval_status,"%FAILED%"),"Failed","Passed") | table User, sec_module, status

Which gives;

| User | Sec_Module | Status |
|Name | MAC-AV         | Failed
|Name | HD-Encrypt   | Passed
...

What I would like to display is the Sec_Module as column names and the status as the field values. E.g;

User | MAC-AV |HD-Encrypt | Patch_Level 
Name| Failed   | Passed        | Failed 
Name| Failed   | Passed        | Failed

I've tried transpose, but am struggling to get the data into the format I expect.

Appreciate any direction or guidance more experienced users can provide. Feel like i am missing something simple here in approach.

[woodcock · Accepted solution]

Like this:

| makeresults
| eval raw="Name,MAC-AV,Failed::Name,HD-Encrypt,Passed"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<User>[^,]+),(?<Sec_Module>[^,]+),(?<Status>[^,]+)$"
| table User Sec_Module Status

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

| xyseries User Sec_Module Status
| eval PatchLevel="Passed"
| foreach * [eval PatchLevel=if(('<<FIELD>>'="Failed"), "Failed", PatchLevel)]

[MattSmith129]

Thank you, "xyseries User Sec_Module Status" solved the question.

I am unclear as to what lines 12 and 13 are doing in your solution? Would you mind expanding?

A follow up question, the xyseries summarises on the User, however each user can attempt to log on to the VPN multiple times in the search time period. I used transaction to associate the Sec_Module results with each attempt (using a maxspan=3s). This does not work however with the xyseries output since it groups it all into one event.

Is there a way to apply a grouping to the User so that each evaluation attempt can be separated in the xyseries output?

Many Thanks,

Matt

To give example;

index="vpn_log"  packet_engine_name=CLISEC_EXP_EVAL| eval status=if(like(cli_eval_status,"%FAILED%"),"Failed","Passed")| transaction User maxspan=3s | table User,sec_module,status,_time

will show 7 authentication attempts over 24 hours for one user

index="vpn_log"  packet_engine_name=CLISEC_EXP_EVAL| eval status=if(like(cli_eval_status,"%FAILED%"),"Failed","Passed") | xyseries User sec_module status 

will show 1 authentication attempt over the same 24 hours for one user.

[woodcock]

Lines 12-13 are creating the PatchLevel field for each User value. If all Sec_Module patches are Successful then it is Success, otherwise, it is Failure. As far as further extensions, it would be best to ask another question and if you do, BE SURE to post the original search, too.