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

[charlottelimcl]

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. 

[gcusello]

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]

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

[gcusello]

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

[charlottelimcl]

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

[charlottelimcl]

Thank you, i will try this