topic Consolidate data in table using Dedup command in Splunk Search

Consolidate data in table using Dedup command

neerajs_81 — Thu, 30 Dec 2021 07:43:35 GMT

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.

Re: Consolidate data in table using Dedup command

gcusello — Thu, 30 Dec 2021 07:49:07 GMT

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

Re: Consolidate data in table using Dedup command

neerajs_81 — Thu, 30 Dec 2021 07:52:57 GMT

Thank you very much.

Re: Consolidate data in table using Dedup command

gcusello — Thu, 30 Dec 2021 07:56:10 GMT

Hi @neerajs_81,

good for you, see next time.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉