Splunk Cheat Sheet

Timechart

M=CB PCC=SYDA83210 | timechart max(DTM) as CRSMessages span=30s

Sparkline

M=CB  | stats sparkline max(DTM) as Messages by PCC

Lookups

Lookups are used to normalize data, currently there are lookups defined for:

| inputlookup airports
| inputlookup airlines
| inputlookup errors
| inputlookup pcc

• Sample Lookup Query, Show the top bookings and show the carrier name

M=BOI earliest=-1d  latest=now   | stats count(AIR) as Amount by AIR | sort Amount desc, limit=20 |  lookup airlines Code as AIR OUTPUT Hint 
| rename Hint as Carrier | fields Carrier, Amount

• Sample Lookup Query, Show the top PCCs and show the customer name

M=FAPI CMD=GetFares | top PCC showperc=f |  lookup pcc PCC as PCC OUTPUT Owner,CRSName | rename Owner as Customer | fields Customer, count

IP Location

M=F2N |  iplocation IP | table clientip, status, City, Country

Geo Lookup

M=F2N |  iplocation IP  |  geostats latfield=lat longfield=lon count by IP globallimit=20

Advanced Search Samples

Regex Samples

String to search:

Feb 13 14:07:02 10.0.3.30 Feb 13 14:07:02 mail mimedefang.pl[10780]: MDLOG,s1DD71da017590,mail_in,,,<support@explorer.de>,<support@explorer.de>,Warning Message

Regex to extract the message id:

explorer mimedefang.pl | rex field=_raw "MDLOG\,(?<MSGID>.*),mail*" | top 100 MSGID,_time | fields _time, MSGID

String to search:

Feb 13 13:59:57 10.0.3.6 Feb 13 13:59:57 neptun vsftpd[8973]: [keytravel] FTP response: Client "194.74.154.185", "226 Transfer complete."

Regex to extract the login:

host="10.0.3.6" ": [*]" FTP | rex field=_raw "(?<Login>\s{1}\[.*\])" | top Login

String to search:

Mar 5 15:07:10 10.0.3.30 Mar 5 15:07:10 mail sm-mta[15042]: s25E727n015042: Milter add: header: X-Spam-Status: Yes, score=21.8 required=5.0 tests=BAYES_99,GEO_MAIL_SEARCH,\n \tHELO_DYNAMIC_IPADDR,HTML_MESSAGE,MIME_HTML_ONLY,RCVD_IN_BL_SPAMCOP_NET,\n\tRCVD_IN_BRBL_LASTEXT,RCVD_IN_PBL,RCVD_IN_PSBL,RCVD_IN_RP_RNBL, \n\tRCVD_IN_SORBS_DUL,RCVD_IN_XBL,RDNS_DYNAMIC,SPF_NEUTRAL,URIBL_DBL_SPAM,\n\tURIBL_WS_SURBL 

Regex to extract the message id:

host="10.0.3.30"  "X-Spam-Status: Yes" |  rex field=_raw "]: (?<MSGID>.*): Milter" | top MSGID

String to search:

M=FEEDEDF OAD=142 TOTFLIGHTFILES=71 TOTALOMAFILES=71 TOTNBRFLIGHTS=4406 TOTNBRALOMAS=6066 TOTKEYS=10614 SIZETOT=8839080 DURATION=13 TTL=432000 INFO=0 Host=VM-XC01 Job=hhh_edf_NL_2018-11-19-1349-1-90-RT.csv   Code=HHH-FR-01 

Regex to extract the date range (1-90):

M=FEEDEDF | rex field=_raw "Job=hhh_edf_\w+-\d+-\d+-\d+-(?<STR>.*\d*-\d*)-RT"  |  top STR

Regex to expand date to day, month and year, sample:

DATE=2020-01-01 .... 

Regex

rex field=DATE "(?<Year>[^\-]+)\-(?<Month>[^\-]+)\-(?<Day>[^\-]+)"

Then aggregate by

stats sum(...) as Something  by Month Year 

Sample:

Oct 31 12:14:39 192.168.100.1 %ASA-4-106023: Deny tcp src outside:185.176.27.178/46086 dst inside:192.168.100.237/12834 by access-group "static_outside" [0x0, 0x0]

Regex:

host="192.168.100.1" | rex field=_raw "Deny tcp src outside:(?<SRC>[^\/]+).*dst inside:(?<DST>[^\/]+)\/(?<PORT>[^\s+]+)" 
|  top SRC,DST,PORT

Sample:

Jun  3 15:29:32 192.168.100.1 %ASA-6-302013: Built inbound TCP connection 2154199512 for outside:212.19.51.190/64499 (212.19.51.190/64499) to inside:192.168.100.240/443 (146.0.228.21/443)

Regex to get a table of SRC,DST and Port

host="192.168.100.1" Built inbound TCP connection *  | rex field=_raw "for outside:(?<SRC>[^\/]+)" | rex field=_raw "to inside:(?<DST>[^\/]+)\/(?<PORT>[^\s+]+)" | top 500 SRC,DST,PORT

Regex to get SSH Login

Dec  3 15:53:45 vm-splunk01 sshd[29184]: Accepted password for root from 192.168.178.31 port 53258 ssh2

* | rex field=_raw "(?<result>Accepted|Failed) password for (?<username>\w+) from (?<ipaddr>[0-9A-Fa-f:\.]+)" | top username, ipaddr, result

* | 
rex field=_raw "\(?<user>.*)\<\/Data>\<Data Name='Protocol'" | 
rex field=_raw "\(?<srcip>.*)\<\/Data><Data Name='SourceHostname'" | 
rex field=_raw "\(?<dstip>.*)\<\/Data><Data Name='DestinationHostname'" | 
rex field=_raw "\(?<dstport>.*)\<\/Data><Data Name='DestinationPortName'"  | 
lookup servicestcp Code as dstport OUTPUT Name |
rename Name as srvname |
top 500 user,srcip,dstip,dstport,srvname

 <Event xmlns='http://schemas.microsoft.com/win/2004/08/events/event'><System><Provider Name='Microsoft-Windows-Sysmon' Guid='{5770385f-c22a-43e0-bf4c-06f5698ffbd9}'/><EventID>3</EventID>        <Version>5</Version>
<Level>4</Level><Task>3</Task><Opcode>0</Opcode><Keywords>0x8000000000000000</Keywords><TimeCreated SystemTime='2020-12-02T18:05:16.005999600Z'/><EventRecordID>31147</EventRecordID>  <Correlation/>
 <Execution ProcessID='2880' ThreadID='3832'/><Channel>Microsoft-Windows-Sysmon/Operational</Channel><Computer>vm-winclient01</Computer><Security UserID='S-1-5-18'/></System>
 <EventData>NetworkConnection2020-12-02 18:05:13.923{3a64bc92-d753-5fc7-4900-000000001700}
 3028C:\ProgramData\Microsoft\Windows Defender\Platform\4.18.2010.7-0\MsMpEng.exeNT AUTHORITY\SYSTEM
 tcptruefalse192.168.178.47-
 49678-false51.144.227.73-
 443-</EventData></Event>

Lookahead Sample

Records(s) to look ahead and group

Mar 5 15:34:20 10.0.3.30 Mar 5 15:34:20 spamd child[6707]: GSCORE=0 COU=ES ASN=AS12357 IP=77.230.132.146 MFROM=ibe@elegancejewelrydesigns.com MTO=ibe@hitchhiker.com  MSGID=s25EYHtn016074 HELO=static-146-132-230-77.ipcom.comunitel.net IPN=1306952850 LAT=40.0000 LON=-4.0000 CTY=0 
Mar 5 15:34:24 10.0.3.30 Mar 5 15:34:24 mail sm-mta[16074]: s25EYHtn016074: Milter add: header: X-Spam-Status: Yes, score=23.4 required=3.0 tests=BAYES_99,CK_HELO_GENERIC,\n\tGEO_MAIL_SEARCH,HELO_DYNAMIC_IPADDR,HTML_MESSAGE,MIME_HTML_ONLY,\n\tRAZOR2_CF_RANGE_51_100,RAZOR2_CF_RANGE_E8_51_100,RAZOR2_CHECK,\n\tRCVD_IN_BL_SPAMCOP_NET,RCVD_IN_BRBL_LASTEXT,RCVD_IN_PSBL,RCVD_IN_SORBS_WEB,\n\tRCVD_IN_XBL,SPF_NEUTRAL,URIBL_BLACK,URIBL_DBL_SPAM,URIBL_JP_SURBL,\n\tURIBL_WS_SURBL autolearn=spam version=3.3.1

Query:

earliest=-1d  latest=now host="10.0.3.30"  "X-Spam-Status: Yes" OR GSCORE |  rex field=_raw "]: (?<MSGID>.*): Milter" |  transaction MSGID | search "X-Spam-Status: Yes" | top MFROM

Look to Book Chart

Show the Look to Book Ratio

• Count PCC, GetFares,BookFare
• Calculate to Ratio
• Append a trailing identifier, :1 or :0 if no bookings were made
• Lookup PCC to Wonername
• Select output Fileds
• leave the total as the last row

M=FAPI (CMD=GetFares OR (CMD=BookFare AND STAT>=0)) STAT>=0 
| chart count by PCC,CMD 
| sort BookFare,GetFares desc 
| eval L2B=round(GetFares/BookFare) 
| eval STATBOOK=if(BookFare>0,"1","0") 
| eval STATGF=if(L2B>0,L2B,GetFares)  
| eval LookToBook=STATGF . ":" . STATBOOK  
| lookup pcc PCC as PCC OUTPUT Owner  
| fields Owner,PCC, GetFares, BookFare, LookToBook 
| addtotals col=t row=f labelfield=PCC

Last 50 Bookings

Show the recnt bookings

• Use top (no counting)
• Append OK or Error, if error then lookup the error code
• Lookup pcc, owner, carrier, city codes
• Rename and format fields

M=BOI earliest=-1d  latest=now   
| top 50 _time,PCC,CRS,AIR,DEP,ARR,STAT,PAS,SEG, NET, TAX, CUR, DIST 
| lookup errors Code as STAT OUTPUT Description   
| eval STATX=if(STAT>=0,"OK", Description)   
| eval field-description=STAT. " = " . STATX  
| lookup airlines Code as AIR OUTPUT Hint   
| lookup pcc PCC as PCC OUTPUT Owner,CRSName   
| lookup airports IATA as DEP OUTPUT CityName as From   
| lookup airports IATA as ARR OUTPUT CityName as To   
| rename Hint AS Carrier   
| rename Owner AS Customer   
| rename Carrier as CarrierName   
| rename field-description as STATUS  
| fields _time, CRS,Customer, AIR, CarrierName, From, To, PAS, SEG, NET, TAX, CUR, DIST, STATUS

Revenue

Show the revenue

• Use stats for counting
• Use the new fields EURNET and EURTAX as unique currency source (available since FEB2014)

M=BOI STAT>=0 TST=0  
| stats count(_time) as Bookings, sum(EURNET) as TotalFareEuro,sum(EURTAX) as TotalTAXEuro,sum(PAS) as Passenger by CRS, PCC   
| eval AverageFarePerPassenger=round(TotalFareEuro/Passenger)  
| eval AveragePassengerPerBooking=round(Passenger/Bookings)  
| lookup pcc PCC as PCC OUTPUT Owner,CRSName   
| fields CRSName, Owner, Bookings, TotalFareEuro,TotalTAXEuro,Passenger,AverageFarePerPassenger,AveragePassengerPerBooking  
| addtotals col=t row=f labelfield=CRSName

Transaction

Use transaction and table to map FAPI/WFE data.

TIME>20  | transaction TID | rename TIME as ResponseTime | table _time,TID,host,ResponseTime,DEP,ARR,M,SC,SS,PCC | search PCC=LCH

WebFare Searches

Search for the slowest carrier and list the errors (if any)

M=WFE SS<0 | top SC,SS

Search for W6 if the Agent Plugini is used:

M=WFE WAGTD="*W6*" 

List all errors for U2 with Agent Plugin used.

M=WFE WAGTD="*U2*" SC=U2 SS<0 | top SS

List all errors for U2 with NO Agent Plugin used.

M=WFE NOT WAGTD="*U2*" SC=U2 SS<0 | top SS

List all U2 traffic with Agent Plugin used.

M=WFE WAGTD="*U2*"

Splunk Cnfiguration

server.conf

• Allow remote login when using the free license

[general]
allowRemoteLogin = always

• Do not show the update information

[applicationsManagement]
allowInternetAccess = false

inputs.conf

• Set the sourcetype on the forwarder machines

[monitor://d:\internetbackend\log\splunk\fapi*]
sourcetype = flightapi
disabled = 0
[monitor://d:\internetbackend\log\splunk\ibe*]
sourcetype = internetbackend
disabled = 0
[monitor://d:\internetbackend\log\splunk\mnt*]
sourcetype = maintenance
disabled = 0
[monitor://d:\internetbackend\log\splunk\wfe*]
sourcetype = webfares
disabled = 0
[monitor://d:\internetbackend\log\splunk\fq*]
sourcetype = farequote
disabled = 0
[monitor://c:\hitchhiker\log\splunk\pg*]
sourcetype = paymentgate
disabled = 0

Append two searches

• Use appendcols

M=FAPI FT=1 STAT=0 USR=QUNAR.PROD | stats count(CMD) as QUNAR | appendcols [search M=FAPI FT=1 STAT=0 USR NOT QUNAR.PROD | stats count(CMD) as OTHER]

Event count

just a draft:

M=WFE  | stats list(PSTAT) as PSTAT count(AIR) as total by AIR | where mvindex(PSTAT,1)="0" or mvindex(PSTAT,1)="1"
M=WFE  | stats list(PSTAT) as PSTAT count(AIR) as total by AIR
M=WFE 0Y | stats values(PSTAT) as PSTAT count(AIR) as total by AIR

Inputs.conf

[default]
host = xyz

[monitor://c:\path\*.*]
sourcetype = MySourceType
disabled = 0
#index = MyIndex

Init Script

• /etc/systemd/system/splunk.service

[Unit]
Description=Splunk
After=network.target

[Service]
RemainAfterExit=yes
ExecStart=/opt/splunk/bin/splunk start
ExecStop=/opt/splunk/bin/splunk stop
ExecReload=/opt/splunk/bin/splunk restart
User=splunk
Group=splunk
 
[Install]
WantedBy=multi-user.target

Links

• http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/CommonEvalFunctions

• KV Store renew cert
• http://wiki.intern/index.php/Renew_internal_Splunk_License