Get info from different dataset and compare

[FabioFerreira]

Hi,

I need to get failed logins from critical assets.

So I was trying to get tag "CRITICAL" in endpoints dataset and if there are any "event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4625 in xdr_data dataset.

Could you help pls

[timurphy]

Hi @FabioFerreira,

This can be accomplished by using a nested query (wrapped in parenthesis) in a filter stage. Here is an example of such a query to help you get started:

preset = xdr_event_log 
| filter action_evtlog_event_id = 4625
| filter agent_hostname in (dataset = endpoints 
| filter tags contains "CRITICAL"
| fields endpoint_name)

 I hope this helps!

Regards,

Tim

[FabioFerreira]

Thank you Tim!

That helped me a lot, now I need to extract TargetUserName from column "action_evtlog_data_fields ", that it is in json format.

I was trying to use json_extract without success.

Regards,

Fabio

[timurphy · Accepted solution]

Hi @FabioFerreira,

You are on the right track using the json_extract function, perhaps there was a syntax error. Here is an example building upon the previous query showing how to extract the TargetUserName property from the JSON object and create a new column with that value using the alter stage:

preset = xdr_event_log 
| filter action_evtlog_event_id = 4625
| filter agent_hostname in (dataset = endpoints 
| filter tags contains "CRITICAL"
| fields endpoint_name)
| alter TargetUserName = json_extract(action_evtlog_data_fields, "$.TargetUserName")

The TargetUserName property displays the username value wrapped in quotation marks, so if you wanted to take this a step further and remove those, you could use the trim function, using the entire json_extract function as an input like this: 

| alter TargetUserName = trim(json_extract(action_evtlog_data_fields, "$.TargetUserName"), "\"")

This will remove the quotation marks and give you a new column with just the username value.

Regards,

Tim

[FabioFerreira]

Hi @timurphy 

Thanks, that was perfect.

I was trying 

| alter action_evtlog_data_fields = json_extract(action_evtlog_data_fields, "TargetUserName")

But your answer solved my problem.

| alter TargetUserName = trim(json_extract(action_evtlog_data_fields, "$.TargetUserName"), "\"")

Thanks a lot!

Regards,

Fabio