topic Re: Get info from different dataset and compare in Cortex XDR Discussions

Get info from different dataset and compare

FabioFerreira — Wed, 15 Mar 2023 12:48:52 GMT

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

Re: Get info from different dataset and compare

timurphy — Wed, 15 Mar 2023 14:28:10 GMT

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

Re: Get info from different dataset and compare

FabioFerreira — Wed, 15 Mar 2023 16:22:14 GMT

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

Re: Get info from different dataset and compare

timurphy — Wed, 15 Mar 2023 17:03:46 GMT

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:

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

Re: Get info from different dataset and compare

FabioFerreira — Wed, 15 Mar 2023 17:18:17 GMT

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