Get info from different dataset and compare

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Get info from different dataset and compare

L2 Linker

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

 

1 accepted solution

Accepted Solutions

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

View solution in original post

4 REPLIES 4

L3 Networker

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

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

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

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

 

  • 1 accepted solution
  • 1702 Views
  • 4 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!