need some help creating tokens

[packet_hunter]

I have a scheduled alert that I need to send to different recipients with different messages depending on the search results, the following is the basic search...

index=secApp sourcetype=secApp_json "malware_CB" OR "malware_Obj"
|rename alert.occurred as Occurred 
|stats  
values(alert.name) as Alert  
values(alert.src.ip) as SourceIP 
values(alert.dst.smtp-to) as Recp 
values(alert.src.host) as Hostname 
values(alert.src.url) as Attachment-or-Link 
values(appliance) as Appliance 
values(alert.smtp-message.subject) as Subj  
values(alert.src.smtp-mail-from) as Sender 
values(alert.smtp-message.id) as Msg_ID 
values(alert.smtp-message.smtp-header) as Header 
by Occurred 
|transpose 
|rename column as Details, row* as occurrence* 

Now I would like to add a token for alert recipient and a token for alert message... the following is code I crafted for the alert_msg

|eval alert_Msg = case (Appliance = USA-emailscan-01, "msg1", 
Appliance = UK-emailscan-01, "msg2", 
Appliance = USA-netscan-01, "msg3", 
Appliance = UK-netscan-01, "msg4") 

is it possible to use a lookup to populate the message content? instead of adding the message text directly in the eval statement?

Thank you

[somesoni2 · Accepted solution]

Give this a try

 index=secApp sourcetype=secApp_json "malware_CB" OR "malware_Obj"
 |rename alert.occurred as Occurred 
 |stats  
 values(alert.name) as Alert  
 values(alert.src.ip) as SourceIP 
 values(alert.dst.smtp-to) as Recp 
 values(alert.src.host) as Hostname 
 values(alert.src.url) as Attachment-or-Link 
 values(appliance) as Appliance 
 values(alert.smtp-message.subject) as Subj  
 values(alert.src.smtp-mail-from) as Sender 
 values(alert.smtp-message.id) as Msg_ID 
 values(alert.smtp-message.smtp-header) as Header 
 by Occurred  
| untable Appliance Details Occurence1

The output will be like this

Appliance        Details             Occurence1
USA-emailscan-01 Recp                somename@domain.tld
USA-emailscan-01 Occurred            2017-02-23 08:39:41+00
USA-emailscan-01 Attachment-or-Link  malicious.doc
USA-emailscan-01 Alert               malware-object
....

You can now add your lookup logic to add a alert_Msg field in the each row. If you want to group an occurrence for an appliance, you can add | stats list(Details) as Details list(Occurence1) as Occurence1 by Appliance at the end.

[packet_hunter]

Thank you. I will give it a try.

[somesoni2]

Not sure if you're getting a field called 'Appliance' after your transpose command, so your case may not be working. If you get that to work, yes, you can use lookup table to do the same. Just create a lookup say appliance_message.csv with fields Appliance and Message and use like this

...| lookup appliance_message.csv  Appliance OUTPUT Message as alert_Msg

[packet_hunter]

you are correct, the "Appliance" field is not populating... any ideas for a work around?

[somesoni2]

Can you give me a rough layout of the results (fields, number of rows etc) before transpose? Any particular reason for doing transpose (or what's the expected format of result)?

[packet_hunter]

the scheduled alerts usually produce 1 result per the time window so in that case (without transpose) there would be 1 row with 10 fields for a malware-Obj

OR
there would be 1 row with 5 fields for a malware-CB

I hope that makes sense

[packet_hunter]

the results the alert look like this in an email

Details occurrence1
Occurred 2017-1-11 14:56:32+00
Alert malware-callback
SourceIP 192.168.2.1
Hostname thiscomputer.company.com

or

Details occurrence1
Recp somename@domain.tld
Occurred 2017-02-23 08:39:41+00
Attachment-or-Link malicious.doc
Alert malware-object
Applicance USA-emailscan-01
Subj

Basically this is the format of the email alerts, I am not able to get the spacing right, but there would be two columns here

[packet_hunter]

I do transpose for readability in the email only, otherwise it becomes hard to read
I will send the results shortly

[packet_hunter]

Thank you, I will give that a try. I will open another thread for my alert recipients question