topic Splunk Query - How to create a table from event in Splunk Search

Splunk Query - How to create a table from event

pclooi — Wed, 30 Sep 2020 01:43:36 GMT

I'm quite new to Splunk and currently am trying to do a simple with Splunk using syslog. I have a firepower syslog which I need to understand how to do a table of the events but unsuccessfully.

My current query is

sourcetype="syslog"  URL
| rex "^\[(?[^]]+)\]\[(?[^]]+)\]\[(?[^]]+)\] \"(?[^\"]+)\" \[Classification: (?[^]]+)\] \[Priority: (?[^]]+)\] {(?[^}]+)} (?\d+\.\d+\.\d+\.\d+):(?\d+) -\> (?\d+\.\d+\.\d+\.\d+):(?\d+)"
| eval c_time=strftime(_time,"%m/%d/%y %H:%M:%S") 
|table  c_time, src_ip, src_port, dest_ip, dest_port, message, blocked
|rename _time AS "Time", eventtype AS "Event Type", src_port AS "Src Port" , dest_ip AS "Dst" , dest_port AS "Dst Port", ip_proto AS "Protocol", hostname AS "Hostname", message AS "Message", blocked AS "Blocked?", src_ip AS "Src", c_time As "Time"

From the events, I could see
Aug 14 17:47:08 xx.xx.xx.xx Aug 14 09:47:08 firepower SFIMS: Protocol: TCP, SrcIP: xx.xx.xx.xx, OriginalClientIP: ::, DstIP: xx.xx.xx.xx, SrcPort: xxxxx, DstPort: xxx, TCPFlags: 0x0, IngressInterface: CBY-IFW-01/IFW_TRANSIT, EgressInterface: CBY-IFW-01/IFW_OUTSIDE_2, IngressZone: INSIDE_Internet, EgressZone: OUTSIDE_INTERNET_2, Policy: MY_Policy, ConnectType: End, AccessControlRuleName: MY_POLICY, AccessControlRuleAction: Allow, Prefilter Policy: Unknown, UserName: No Authentication Required, Client: SSL client, ApplicationProtocol: HTTPS, WebApplication: Some App, InitiatorPackets: 8, ResponderPackets: 9, InitiatorBytes: 1403, ResponderBytes: 3716, Context: CBY-IFW-01, NAPPolicy: Question Mark, DNSResponseType: No Error, Sinkhole: Unknown, URLCategory: Unknown, URLReputation: Risk unknown, URL: https://this.splunkquery.com

I would like to see a table like the ones below.
time | src_ip | dst_ip | URL

Is it possible? Any help is appreciated.

Re: Splunk Query - How to create a table from event

richgalloway — Wed, 14 Aug 2019 13:21:52 GMT

The forum formatter mangled your regex strings. Please edit your question to restore them. Putting SPL and other code-like text inside backticks will preserve formatting.

Despite the damage done to the rex command, we can see it doesn't match your sample event. The regex expects [ as the first character of the event, but there are no brackets anywhere in the data. Likewise, the texts "Classification:" and "Priority:" are sought, but are not in the event. Are you sure you gave us the right sample event?

Re: Splunk Query - How to create a table from event

Sukisen1981 — Wed, 14 Aug 2019 15:50:04 GMT

| makeresults 
|  eval payload="Aug 14 17:47:08 xx.xx.xx.xx Aug 14 09:47:08 firepower SFIMS: Protocol: TCP, SrcIP: xx.xx.xx.xx, OriginalClientIP: ::, DstIP: xx.xx.xx.xx, SrcPort: xxxxx, DstPort: xxx, TCPFlags: 0x0, IngressInterface: CBY-IFW-01/IFW_TRANSIT, EgressInterface: CBY-IFW-01/IFW_OUTSIDE_2, IngressZone: INSIDE_Internet, EgressZone: OUTSIDE_INTERNET_2, Policy: MY_Policy, ConnectType: End, AccessControlRuleName: MY_POLICY, AccessControlRuleAction: Allow, Prefilter Policy: Unknown, UserName: No Authentication Required, Client: SSL client, ApplicationProtocol: HTTPS, WebApplication: Some App, InitiatorPackets: 8, ResponderPackets: 9, InitiatorBytes: 1403, ResponderBytes: 3716, Context: CBY-IFW-01, NAPPolicy: Question Mark, DNSResponseType: No Error, Sinkhole: Unknown, URLCategory: Unknown, URLReputation: Risk unknown, URL: https://this.splunkquery.com"; 
|  rex field=payload "SrcIP:(?<src_ip>.*?)," 
| rex field=payload "DstIP:(?<dst_ip>.*?)," 
|  rex field=payload "URL:(?<url>.*)" 
|  table _time,src_ip,dest_ip,url

This is simple, but i think you are stuck at someplace..

Re: Splunk Query - How to create a table from event

mayurr98 — Wed, 14 Aug 2019 16:05:45 GMT

I think this should work too which will extract almost all the fields require :

.. | extract pairdelim="," kvdelim=": " | rex "URL:\s*(?<URL>.*)" 
| table _time SrcIP DstIP URL