topic Re: How to edit my search to create a table with multiple lines per single event in Splunk Search

How to edit my search to create a table with multiple lines per single event

julz0815 — Mon, 30 Jan 2017 09:39:50 GMT

Hi,

having JSON formatted events there are parts of the event with the same key like:

   events: [    [-] 
     {  [-] 
       classifications: [   [+] 
       ]    
       data: {  [-] 
         rewrite_sql: select * from users where username='ZAP' UNION ALL select NULL -- ' and password='ZAP';   
         rewrite_with_sql: SELECT NULL LIMIT 0; 
       }    
       stack: [ [-] 
       ]    
       type: Rewrite    
     }  
     {  [-] 
       classifications: [   [+] 
       ]    
       data: {  [-] 
         corpusType: SQL    
         infected: select * from users where username='ZAP' UNION ALL select NULL -- ' and password='ZAP';  
         injectedPart: ZAP' UNION ALL select NULL -- 
       }    
       stack: [ [-] 
       ]    
       type: Injection  
     }  
   ]

My search looks like this:

source="rasp logs" | spath | rename events{}.type as "Event_Type", events{}.data.corpusType as "Attack_Type", id as "Event ID" | eval x=mvzip(Event_Type,Attack_Type) | mvexpand x | eval x = split(x,",") | eval RASP_Event_Action=mvindex(x,0) | eval Event_Type=mvindex(x,1) | table "Event ID", "Event_Type", RASP_Event_Action

but my table looks like this:

c58b842a-2b70-4077-a7fa-e2e4bdb04688        SQL     Rewrite
c58b842a-2b70-4077-a7fa-e2e4bdb04688

c58b842a-2b70-4077-a7fa-e2e4bdb04688        SQL     Injection
c58b842a-2b70-4077-a7fa-e2e4bdb04688

I would like to have my table formatted like this

c58b842a-2b70-4077-a7fa-e2e4bdb04688         SQL    Injection
.............................................SQL    Rewrite (dots only to make the display correct)

happy to get any idea to move to the right direction

Re: How to edit my search to create a table with multiple lines per single event

jkat54 — Mon, 30 Jan 2017 14:06:44 GMT

Can you try this please?

source="rasp logs" 
| spath 
| rename events{}.type as "Event_Type", events{}.data.corpusType as "Attack_Type"
| eval x=mvzip(Event_Type,Attack_Type) 
| eval z=mvzip(x,id)
| mvexpand z 
| eval z = split(z,",") 
| eval Event_Action=mvindex(z,0) 
| eval Event_Type=mvindex(z,1)
| eval Event_ID=mvindex(z,2)
| table Event_ID, Event_Type, Event_Action

Re: How to edit my search to create a table with multiple lines per single event

julz0815 — Mon, 30 Jan 2017 14:17:54 GMT

not 100% but a very good start - THX!

my Event ID is not only displayed once, but for the second line of the same event id I don't need it at all. not sure this is working at all?

Re: How to edit my search to create a table with multiple lines per single event

jkat54 — Mon, 30 Jan 2017 14:29:37 GMT

Oh i see what you mean now. You need something to thread the event id together... see if this works

 source="rasp logs" 
 | spath 
 | rename events{}.type as "Event_Type", events{}.data.corpusType as "Attack_Type"
 | eval x=mvzip(Event_Type,Attack_Type) 
 | eval z=mvzip(x,id)
 | mvexpand z 
 | eval z = split(z,",") 
 | eval Event_Action=mvindex(z,0) 
 | eval Event_Type=mvindex(z,1)
 | eval Event_ID=mvindex(z,2)
 | transaction Event_ID
 | chart  values(Event_Type), values(Event_Action) by Event_ID

Re: How to edit my search to create a table with multiple lines per single event

julz0815 — Mon, 30 Jan 2017 14:49:43 GMT

wow, great! thanks a lot!

one last question: if the event part now is somehow dynamic and there can be n events. Can the display/search made dynamic as well? I mean now I ask for the different elements on their own like z,0 z,1 and z,2. Can there be some sort of loop to do so?

Re: How to edit my search to create a table with multiple lines per single event

jkat54 — Mon, 30 Jan 2017 18:24:38 GMT

Can you provide an example of what you're looking for?