How to write a search query to retrieve the two different values in Security ID, Account Name and Account Domain fields as a single row table? Result as:
"Security ID1 | Account Name1 | Account Domain1 | Security ID2 | Account Name2 | Account Domain2
===========================================================
An account was successfully logged on.
Subject:
Security ID: SYSTEM
Account Name: DESKTOP-LLHJ389$
Account Domain: WORKGROUP
Logon ID: 0x3E7
Logon Information:
Logon Type: 7
Restricted Admin Mode: -
Virtual Account: No
Elevated Token: No
Impersonation Level: Impersonation
New Logon:
Security ID: AzureAD\RandyFranklinSmith
Account Name: rsmith@montereytechgroup.com
Account Domain: AzureAD
Logon ID: 0xFD5113F
Linked Logon ID: 0xFD5112A
Network Account Name: -
Network Account Domain: -
Logon GUID: {00000000-0000-0000-0000-000000000000}
Process Information:
Process ID: 0x30c
Process Name: C:\Windows\System32\lsass.exe
Network Information:
Workstation Name: DESKTOP-LLHJ389
Source Network Address: -
Source Port: -
Detailed Authentication Information:
Logon Process: Negotiat
Authentication Package: Negotiate
Transited Services: -
Package Name (NTLM only): -
Key Length: 0
I tried using below search:
index=wineventlog EventCode="4624" | stats values("Security ID") as ids,values("Account Name") as names,values("Account Domain") as domains
|eval "Security ID1"=mvindex(ids,0),"Security ID2"=mvindex(ids,1)
|eval "Account Name1"=mvindex(names,0),"Account Name2"=mvindex(names,1)
|eval "Account Domain1"=mvindex(domains,0),"Account Domain2"=mvindex(domains,1)
|table "Security ID1","Account Name1","Account Domain1","Security ID2","Account Name2","Account Domain2"
It outputs into as single row table. Its values keep changing in that row instead of writing into the next row when new event found. The time field is empty too.
My required output should look like below:
_time Security ID1 Account Name 1 Account Domain1 Security ID2 Account Name 2 Account Domain2
8.15am System DESKTOP-LLHJ389$ Workgroup Randy Franklin@montereytechgroup.com rsmith@montereytechgroup.com New Workgroup
8.17am System SG12345 Workgroup helen_phua Helen_phua newDomain
@moksw,
Try using multi value fields
your search|stats values("Security ID") as ids,values("Account Name") as names,values("Account Domain") as domains
|eval "Security ID1"=mvindex(ids,0),"Security ID2"=mvindex(ids,1)
|eval "Account Name1"=mvindex(names,0),"Account Name2"=mvindex(names,1)
|eval "Account Domain1"=mvindex(domains,0),"Account Domain2"=mvindex(domains,1)
|table "Security ID1","Account Name1","Account Domain1","Security ID2","Account Name2","Account Domain2"