Splunk Search

Consolidate data in table using Dedup command

neerajs_81
Builder

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

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

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
Builder

Thank you very much. 

Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...