Consolidate data in table using Dedup command

[neerajs_81]

Hello,  
I am using the below query to output which of our Searches/Rules are mapped to which Mitre Technique IDs.

| inputlookup mitre_all_rule_technique_lookup 
| `lookup_technique_tactic_from_rule_name`
| search rule_disabled=0
| dedup rule_name, technique_id, rule_disabled

The Result is as follows:

rule_name	tactic_ID	tactic_name	Technique_ID	Tecnique_name
Rule001	TA001	Persistence	T1136	Create Account
Rule001	TA002	Persistence	T1098	Account Manipulation
Rule001	TA008	Defense Evasion	Txxxx	Modify infrastructrue

As you can see ,  it is showing different entries for  the same data in the "rule_name" column .   The Rule mentioned in the Rule_name column is mapped to 3 different Tactic_ID ,Technique_IDs etc which is why  it shows 3 results for the same rule.  How can i consolidate all this ?

Basically this is the output i want :

rule_name	tactic_ID	tactic_name	Technique_ID	Technique_name
Rule001	TA001 TA002 TA008	Persistence Persistence Defense Evasion	T1136 T1098 TXXXX	Create Account Account Manipulation Modify infrastructure
Rule002	TAxxx TAXXX	....	.....	......

If i change my dedup command in the query  to:   | dedup rule_name  ,  then it displays only the 1st row  of every rule_name and omits the remaining values.

Pls advise. I am sure this is something very fundamental.

[gcusello · Accepted solution]

Hi @neerajs_81,

did you tried to use the stats command?

something like this:

| inputlookup mitre_all_rule_technique_lookup 
| `lookup_technique_tactic_from_rule_name`
| search rule_disabled=0
| stats 
   values(tactic_ID) AS tactic_ID 
   values(tactic_name) AS tactic_name 
   values(Technique_ID) AS Technique_ID 
   values(Tecnique_name) AS Tecnique_name 
   BY rule_name

Ciao.

Giuseppe

[neerajs_81]

Thank you very much. 

[gcusello]

Hi @neerajs_81,

good for you, see next time.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉