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

[EvaRex]

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!!!

[woodcock · Accepted solution]

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]

Thank you @woodcock!

[EvaRex]

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!

[woodcock]

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