Splunk Search

Help with subsearch that isn't working

lucasdc
New Member

I have this search "1" :

[index=br_activedirectory_microsoft EventCode=4624 Account_Domain=AGBANESPA Account_Name=A* 
| fields Account_Domain, Account_Name, Source_Network_Address
| dedup Account_Domain, Account_Name, Source_Network_Address
| lookup dnslookup clientip as Source_Network_Address OUTPUT clienthost
| eval user = substr(mvindex(Account_Name,1),2,4)
| eval hostname = substr(clienthost,3,4)
| where user !=hostname
| lookup new_hostname_lookup hostname as hostname OUTPUT ENDEREÇO, UF, REG, CEP , REDE
| lookup new_user_lookup user as user OUTPUT ENDEREÇO_user, UF_user, REG_user, CEP_user ,REDE_user
| where REG !=REG_user AND REDE !=REDE_user
| table Account_Name
| rename Account_Name as "Siglas dos usúarios com acessos indevidos"]

And this search "2":

index="br_activedirectory_microsoft"  EventCode=4624 OR EventCode=4634 Account_Name=A* Account_Domain=AGBANESPA
| search NOT (Account_Name=$$ OR Account_Name=SYSTEM OR Account_Name=ANONYMOUS*)
| eval User=if(mvcount(Account_Name)>1, mvindex(Account_Name,1), mvindex(Account_Name, 0)) 
| eval User=lower(User)
| search NOT (User=*$$ OR User=system)
| lookup dnslookup clientip as Source_Network_Address OUTPUT clienthost
| transaction User maxevents=2 startswith="EventCode=4624" endswith="EventCode=4634" maxspan=-1
| eval Logofftime=_time+duration
| convert timeformat="%m/%d/%y %H:%M:%S" ctime(_time) as Logontime
| convert timeformat="%m/%d/%y %H:%M:%S" ctime(Logofftime) as Logofftime
| eval h=floor(duration/3600) | eval m=floor((duration-(h*3600))/60) | eval s=floor(duration-(h*3600)-(m*60)) | eval SessionDuration=h."h ".m."m ".s."s"
| dedup Logontime, Logofftime, SessionDuration, User, clienthost
| table Logontime, Logofftime, SessionDuration, User, clienthost
| sort User ComputerName

I would like to put the search "1" inside of the search "2" . I have tried doing this following the tutorials , but it's still not working:

index="br_activedirectory_microsoft"  EventCode=4624 OR EventCode=4634 Account_Name=A* Account_Domain=AGBANESPA
[ search index="br_activedirectory_microsoft" EventCode=4624 OR EventCode=4634  Account_Domain=AGBANESPA Account_Name=A* 
| fields Account_Domain, Account_Name, Source_Network_Address
| dedup Account_Domain, Account_Name, Source_Network_Address
| lookup dnslookup clientip as Source_Network_Address OUTPUT clienthost
| eval user = substr(mvindex(Account_Name,1),2,4)
| eval hostname = substr(clienthost,3,4)
| where user !=hostname
| lookup new_hostname_lookup hostname as hostname OUTPUT ENDEREÇO, UF, REG, CEP , REDE
| lookup new_user_lookup user as user OUTPUT ENDEREÇO_user, UF_user, REG_user, CEP_user ,REDE_user
| where REG !=REG_user AND REDE !=REDE_user
| table Account_Name
| rename Account_Name as "Siglas dos usúarios com acessos indevidos"] 
| search NOT (Account_Name=$$ OR Account_Name=SYSTEM OR Account_Name=ANONYMOUS*)
| eval User=if(mvcount(Account_Name)>1, mvindex(Account_Name,1), mvindex(Account_Name, 0)) 
| eval User=lower(User)
| search NOT (User=*$$ OR User=system)
| lookup dnslookup clientip as Source_Network_Address OUTPUT clienthost
| transaction User maxevents=2 startswith="EventCode=4624" endswith="EventCode=4634" maxspan=-1
| eval Logofftime=_time+duration
| convert timeformat="%m/%d/%y %H:%M:%S" ctime(_time) as Logontime
| convert timeformat="%m/%d/%y %H:%M:%S" ctime(Logofftime) as Logofftime
| eval h=floor(duration/3600) | eval m=floor((duration-(h*3600))/60) | eval s=floor(duration-(h*3600)-(m*60)) | eval SessionDuration=h."h ".m."m ".s."s"
| dedup Logontime, Logofftime, SessionDuration, User, clienthost
| table Logontime, Logofftime, SessionDuration, User, clienthost
| sort User ComputerName

I have used the brackets [] and I was sure that the fields and the index were the same in both searches.
Thanks for all!

0 Karma

SathyaNarayanan
Path Finder

how you want to combine both the search,

we have append , join, commands

To join both the search with a common fields use the below syntax:

| join  [ search  <search 2>]

To append the 2nd search with the first search

| append  maxout=0 [ search  <search 2> ]
0 Karma

lucasdc
New Member

Sathya, I didn't understand, could you pick my queries (1,2) up and write with the code that you have put for me?

Thanks

0 Karma

SathyaNarayanan
Path Finder

To join both the search with a common fields use the below syntax:

| join [ search ]

To append the 2nd search with the first search

| append maxout=0 [ search ]

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...