topic Re: How to feed results of a query into another query of a different time and index? in Splunk Search

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

charlottelimcl — Mon, 03 Oct 2022 10:01:30 GMT

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.

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

gcusello — Mon, 03 Oct 2022 10:09:54 GMT

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.

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

charlottelimcl — Mon, 03 Oct 2022 12:59:14 GMT

Thank you, i will try this

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

charlottelimcl — Tue, 04 Oct 2022 09:56:12 GMT

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

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

gcusello — Tue, 04 Oct 2022 14:32:39 GMT

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

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

charlottelimcl — Wed, 05 Oct 2022 07:55:01 GMT

Regarding the 0 results, this is my current query:

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