Counting the number of occurrences of a specific string

[russell_pfeifer]

I'm trying to generate a search on the number of times a credential has been automatically rotated using our password vaulting solution (Thycotic). I need to generate a column with a count of this specific string:

metadata.description = "SECRET - SECRETPASSWORDCHANGED"

I've tried adding it to the outcome section in this format:

outcome:
$use_count = count($secret_accessed)
$reset_count = count(metadata.description = "SECRET - SECRETPASSWORDCHANGED")

But it throws an error. I tried using the strings.count_substrings function but the results seem to be innacurate:

outcome:
$use_count = count($secret_accessed)
$reset_count = count(strings.count_substrings(strings.to_lower(metadata.product_event_type), "/SECRETPASSWORDCHANGE"))

I'm guessing I'm missing something obvious but any help would be greatly appreciated, below is the full string.

events:
metadata.log_type = "thycotic" AND (metadata.product_event_type = "SECRET - COPY" OR metadata.product_event_type = "SECRET - LAUNCH" OR metadata.product_event_type = "SECRET - VIEW" OR metadata.product_event_type = "SECRET - PASSWORD_DISPLAYED")
target.file.full_path = $secret_accessed

match:
$secret_accessed

outcome:
$use_count = count($secret_accessed)
$reset_count = count(strings.count_substrings(strings.to_lower(metadata.product_event_type), "/SECRETPASSWORDCHANGE"))
$reset_count = count(metadata.description = "SECRET - SECRETPASSWORDCHANGED")

order:
$reset_count desc

[AymanC]

Hi @russell_pfeifer,

When an event is ingested in chronicle, a byte data type is populated in the UDM field known as 'metadata.id'. Which can be used to identify a unique event ingestion.

If you send 5 events into chronicle, they will all contain a different metadata.id.

The below use case will work (on the basis that you don't have event duplication - and will accurately give you the unique occurrence of the event you're looking for. If you have event duplication, you'll have to find unique attributes to help identify unique cases from one another - such as a 'product's' log id.)

Please let me know if the below use case works - I don't have the same data to replicate, but it should work 🙂

events:
metadata.product_event_type = $EventType

metadata.log_type = "thycotic" and ($EventType = "SECRET - COPY" OR $EventType = "SECRET - LAUNCH" OR $EventType = "SECRET - VIEW" OR $EventType = "SECRET - PASSWORD_DISPLAYED")

match:
$EventType

outcome:
$EventCount = count_distinct(metadata.id)
$Secret_Accessed = array_distinct(target.file.full_path) // Note, only 25 (if i remember correctly) results will be outputted in an array_distinct. Alternatively, you can put this variable in the match statement.
order:
$EventCount desc

Kind Regards,

Ayman 

[russell_pfeifer]

Thanks for weighing in here Ayman--

Your search is not returning an error but there is also no data populating at all when I run it - troubleshooting now

[AymanC]

Hi @russell_pfeifer,

Are you certain both the log type, and product event types are correct?

Kind Regards,

Ayman

[russell_pfeifer]

hi @AymanC 

Yes the log type and product event type are correct - I know this based on the fact that my original query returned results with the expected fields (albeit with inaccurate counts). 

[russell_pfeifer]

The below is my original search - you'll notice it uses the same strings in your search but returns the "secret accessed" column

[AymanC]

It could potentially be case sensitivity in the search I provided causing there to be no events returned. Could you double check that the search has case sensitivity off, or that the log type and product event types are correct case sensitive.

[jstoner · Accepted solution]

I don't have your dataset to replicate but I think this method might solve what you are shooting for. The concept of searching within a field/variable for a string would be handled with a if/then statement, so if we do that with an output of 0 or 1, we can then go ahead and sum those values to get the number of rows that match the string specified...

metadata.event_type = "PROCESS_LAUNCH"
match:
 metadata.event_type
outcome:
  $ev = count(metadata.event_type)
  $wmi_count = sum(if(target.process.command_line = /wmiprvse/, 1, 0))

[russell_pfeifer]

Yep after a little modification the if/then statement with a sum worked! Thank you.

Here is the search I ended up using:

metadata.log_type = "thycotic" 

match:
 target.file.full_path

outcome:
  $Password_Use_Count = sum(if(metadata.description = /SECRET - VIEW/, 1, 0))
  $Rotation_Count = sum(if(metadata.description = /SECRETPASSWORDCHANGE/, 1, 0))

  Order:
  $Password_Use_Count desc