Splunk Search

How to extract values from multivalue field and Count number of co-occurences of values in a table?

EvaRex
Engager

Hi!

I'm really new to Splunk so please excuse the simplicity of my question.

I have an inputlookup table (eg.csv) that looks like:

  APP | Connected_Apps
 APP1 | 1=APP1 2=APP2 3=APP3 
      | 1=APP2 2=APP1 3=APP5
    ...
 APP2 | 1=APP1 2=APP2 3=APP3 ...

We get this inputlookup table by executing:

| inputlookup tiers.csv
| makemv tiers
| rex field=tiers "\d=(?[^ ]*)"
| nomv tiers
| stats values(tiers) AS tiers by single_tiers

Now, I want to break this down such that the output is:

 Single_tier| Connected_tier | Connection_Strength
         APP1 | APP2 | 15
              | APP3 | 32 
        APP2 | APP1  |2
             | APP5   |94

The Connection_Strength is how many times these 2 apps occur together in the same tier in the original inputlookuptable (tiers.csv).

This is what I have so far:
| inputlookup eg.csv
| eval app_tiers=split(tiers, "\d+=")
| makemv app_tiers |mvexpand app_tiers| dedup app_tiers| mvcombine app_tiers| sort tiers

The other thing I tried is:
|inputlookup eg.csv | eval app_tiers=split(tiers, "=") | makemv app_tiers | eval clean_app_tiers=mvfilter(match(app_tiers,"(?[a-zA-Z]+)"))

Please help, thank you! Appreciate it a lot!!!

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="APP1 | 1=APP1 2=APP2 3=APP3 
APP1 | 1=APP2 2=APP1 3=APP5
APP2 | 1=APP1 2=APP2 3=APP3"
| makemv delim="
" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<APP>\S+)\s*\|\s*(?<Connected_Apps>.*)$"
| table APP Connected_Apps

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

| rename APP AS single_tier Connected_Apps AS tiers
| rex max_match=0 field=tiers "\d\=(?<single_tiers>[^ ]*)"
| stats count BY single_tier single_tiers
| stats list(single_tiers) AS Connected_tier list(count) AS Connection_Strength BY single_tier

View solution in original post

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="APP1 | 1=APP1 2=APP2 3=APP3 
APP1 | 1=APP2 2=APP1 3=APP5
APP2 | 1=APP1 2=APP2 3=APP3"
| makemv delim="
" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<APP>\S+)\s*\|\s*(?<Connected_Apps>.*)$"
| table APP Connected_Apps

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

| rename APP AS single_tier Connected_Apps AS tiers
| rex max_match=0 field=tiers "\d\=(?<single_tiers>[^ ]*)"
| stats count BY single_tier single_tiers
| stats list(single_tiers) AS Connected_tier list(count) AS Connection_Strength BY single_tier

EvaRex
Engager

Thank you @woodcock!

0 Karma

EvaRex
Engager

Hey @woodcock, I'm facing some trouble with a step after this. I want the Connected_tier to not have the app/single_tier in which it is there. For example, (taking your example), if APP1 is the single_tier, then the connected_tier values should only be APP2, APP3, APP5. APP1 should not be included there, it should also not be counted when we do the |stats count BY single_tier single_tiers .

I'm pretty sure this is very straightforward, but I've looked at a lot of things and I have not been able to find an answer so far.

Thank you @woodcock!

0 Karma

woodcock
Esteemed Legend

If I understand you correctly, like this:

| makeresults 
| eval raw="APP1 | 1=APP1 2=APP2 3=APP3 
APP1 | 1=APP2 2=APP1 3=APP5
APP2 | 1=APP1 2=APP2 3=APP3" 
| makemv delim="
" raw 
| mvexpand raw 
| rename raw AS _raw 
| rex "^(?<APP>\S+)\s*\|\s*(?<Connected_Apps>.*)$" 
| table APP Connected_Apps

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

| rename APP AS single_tier Connected_Apps AS tiers 
| rex max_match=0 field=tiers "\d\=(?<single_tiers>[^ ]*)"
| eval single_tiers=mvjoin(single_tiers, "::") . "::" 
| eval single_tiers=replace(single_tiers, single_tier. "::", "") 
| makemv delim="::" single_tiers 
| stats count BY single_tier single_tiers 
| stats list(single_tiers) AS Connected_tier list(count) AS Connection_Strength BY single_tier
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...