Splunk Search

How to feed results of a query into another query of a different time and index?

charlottelimcl
Engager

Hi all, I am trying to feed results of a query into another of a different time and index and I'm facing issues with this.

Context: I want to look for any user activity across my servers on d+1 for list of user accounts which shows up as disabled on the active directory (windows event code=4725).

From the search query below, I want to parse the list of usernames where count=1 and look for any user activity on d+1 onwards after earliest(_time) is recorded. 

index=useractivitylogs 
[search index=wineventlog EventCode=4725
| eval timestamp=strftime(_time, "%Y-%m-%dT%H:%M"%S")
| stats count as count, earliest(timestamp) by username | where count=1]

Example:

Eventcode 4725 is recorded for these 2 users based on my inner search:

Timestamp | User:
5 September 2022 | Anna
10 September 2022 | Betty 

Then, I want to feed these results to identify any user activity found on any servers on d+1 after the recorded Timestamp.

 

Thank you. 

 

Labels (1)
0 Karma

gcusello
Esteemed Legend

Hi @charlottelimcl,

you have to put attention only to one thing: that the fields used as key for the correlation must have the same name in both the searches.

In other words, if the correlation key has a different file name, you have to rename the key fields in the subsearch to have the same field name of the main search and then explicitate the key fields using the fields command,

Then you can create your main search and sub search with all the parameters and time periods you need, for time periods.

something like this.

index=useractivitylogs [search 
   index=wineventlog EventCode=4725 earliest=-1h latest=now
   | eval timestamp=strftime(_time, "%Y-%m-%dT%H:%M"%S")
   | stats count as count by username 
   | where count=1
   fields username ]
| stats earliest(timestamp) AS timestamp count BY username
| eval timestamp=strftime(_time, "%Y-%m-%dT%H:%M"%S")

Ciao.

Giuseppe

eriods, you have to set one (or both) of these searches with earliest and latest fields.

charlottelimcl
Engager

i tried this but it appears that there are 0 results. Also another thing i noticed is when i used the time modifiers, i.e. (earliest=-1h latest=now), it is relative to the time now, instead of relative to the datetime range i chose at the side bar. Is there a way to change the time modifiers to be relative to the datetime selected at the sidebar?

 

Thank you

0 Karma

gcusello
Esteemed Legend

Hi @charlottelimcl,

if your time borders are fices you can use earliest and latest as you like ,now isn't mandatory!

You can also use two timePickers, but for the second one use tokens in earliest and latest

earliest=$token2.earliest$ latest=$token2.latest$

About 0 results, check the field names that must be exactly the same (field names are also case sensitive!), then check what are the results of the subquery and manually compare with the main search.

Ciao.

Giuseppe

0 Karma

charlottelimcl
Engager

Regarding the 0 results, this is my current query:

index= useractivitylogs
[search 
   index=wineventlog EventCode=4725 earliest=-1h latest=now
   | eval timestamp=strftime(_time, "%Y-%m-%dT%H:%M"%S")
   | stats count as count by username 
   | where count=1
   fields username ]

 Without inputting any table or running any stats on it, it shows up as 0. Even if i were to change my index to the same as the subsearch index, it is still 0 results. 

Currently, the subsearch results will list a column of many different usernames. I am trying to troubleshoot as to why running the subsearch results in 0. Will appreciate your help. Thank you

0 Karma

charlottelimcl
Engager

Thank you, i will try this

0 Karma
Get Updates on the Splunk Community!

Platform Newsletter Highlights | March 2023

 March 2023 | Check out the latest and greatestIntroducing Splunk Edge Processor, simplified data ...

Enterprise Security Content Updates (ESCU) - New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 3 releases of new content via the Enterprise ...

Thought Leaders are Validating Your Hard Work and Training Rigor

As a Splunk enthusiast and member of the Splunk Community, you are one of thousands who recognize the value of ...