Splunk Search

Consolidate data in table using Dedup command

neerajs_81
Contributor

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_nametactic_IDtactic_nameTechnique_IDTecnique_name
Rule001TA001PersistenceT1136Create Account
Rule001TA002PersistenceT1098Account Manipulation
Rule001TA008Defense EvasionTxxxxModify 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_nametactic_IDtactic_nameTechnique_IDTechnique_name
Rule001TA001
TA002
TA008
Persistence
Persistence
Defense Evasion
T1136
T1098
TXXXX
Create Account
Account Manipulation
Modify infrastructure
Rule002TAxxx
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.

Labels (1)
Tags (1)
0 Karma
1 Solution

gcusello
Legend

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

View solution in original post

gcusello
Legend

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
Contributor

Thank you very much. 

Tags (1)
0 Karma

gcusello
Legend

Hi @neerajs_81,

good for you, see next time.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...