start a search certain number of hours/days/weeks from time picker set value

[mpasha]

Good day everyone,
I am dealing with a challenge and really hope i can get an answer here.
I am running a Join search, the first part searches for DNS queries for the past day and then the second search in the join is looking for the IP address client info which is stored in another index.
here is the query:

index=dnsa AND Query_Type!=12
| join Client_IP 
    [ search index=Sum_User_Computer_Map ]

problem is that there are times the "Sum_User_Computer_Map" index does not have a record for that IP address over the timepicker time range, and i need to change the second search earliest to start say 3 days before the time set in the time picker.
how can i do this?!!

[rbechtold]

Hey Mpasha,

I have a possible solution using the map command, but it's very expensive and complicated. Proceed with caution. I'm hesitant to even post this, because I'm unsure of the accuracy of it due to timing, but it's the only way I've found to accomplish what you're trying to do.

I've created an example search for you that makes use of the license usage logs in the _internal index. I'm not sure what your main index looks like, but ideally you'd have less than 20 unique hosts in the main index.

If this is not the case, try to find an index that has 20 hosts or less, and replace any instance of "idx=main" with the index that has 20 or less hosts. The purpose of this is because the map command is very expensive, and I just want you to see what the search is doing.

index=_internal source=*license_usage.log idx=main 
| table _time h 
| stats latest(_time) AS _time by h
    `comment("for sake of getting one log per host - the map command is very expensive so try to limit your results as much as possible in base search")` 
| rename h AS host 
| eval info_min_time= _time - 259200 
| eval info_max_time = _time 
| eval time = _time 
| sort - _time
    `comment("Create time fields for use in the map search -- in your case I'm using "min_time = _time - 259200" because I want to look 3 days (259200 seconds) prior to the event. You can change the max time to be anything you want, but in this case, I'm just leaving the max time as _time. ")` 
| streamstats count AS idfield
    `comment("This idfield will be used for re-adding our main search results back to our table later on")` 
| map maxsearches=50 search="search index=_internal source=*license_usage.log idx=main  $host$ earliest=$info_min_time$ latest=$info_max_time$ 
`comment(\"You can also look in different indexes, sources, and sourcetypes, but for the sake of explanation I'm keeping the base search the same, just looking 3 days earlier\")`
| eval idfield=$idfield$
| stats earliest(_time) AS earliest_time_in_map_command by idfield
| appendpipe 
    [ stats count 
    | where count=0 
    | eval idfield = $idfield$ , earliest_time_in_map_command=\"None Found\", _time = $time$] 
`comment(\"This checks to see if there are no results. If there aren\'t any results, it will return \"None Found\"\")`
| fields - count 
| eval _time = $time$ 
| sort - _time " 
    `comment("the map search uses tokens from the main search. In your case, you would want to replace the "$host$" token with the field your trying to correlate to a different index to find the ip address client info")` 
| join type=left idfield 
    [ search index=_internal source=*license_usage.log idx=main 
    | table _time h 
    | stats latest(_time) AS _time by h 
    | rename h AS host 
    | sort - _time 
    | streamstats count as idfield 
    | eval earliest_base_search_time = strftime(_time, "%Y-%m-%d %H:%M:%S")] 
    `comment("Re-adding main search, because we lose our hosts field after the map command")` 
| table host earliest_base_search_time earliest_time_in_map_command 
| eval earliest_time_in_map_command = strftime(earliest_time_in_map_command, "%Y-%m-%d %H:%M:%S")

I've added comments along the way to try to help break down what is going on in the search, although I know it is probably daunting to even look at.

Regardless, I hope it helps. If nothing else, I hope it points you in the right direction.

Best of luck!

[mpasha]

Thanks for the Elaborate response rbechtold
I tried the map as well since it is the only way you can pass previous search filed to it, however this method is EXTREMELY Slow and does not work well when you are dealing with 1000+ user's DNS queries.
Thanks again for the time

[Vijeta]

@mpasha - You can use earliest in your query-

index=dnsa AND Query_Type!=12
| join Client_IP
[ search index=Sum_User_Computer_Map earliest=-3d@d ]

[mpasha]

that will not do the trick. Imagine the timepicker is set to last week, what i want in the second search to start searching from 10 days ago to now. Earliest=3d@d keep the second search to look only for the past 3 days.

[Vijeta]

so you are looking for start of time picker - 3? Is it a saved search or dashboard, in ad-hoc search you can manually enter value as -10d@d. If its a dashboard query you can use tokens.

[mpasha]

What i want is to add say 3 days to whaterver the timepicker earliest time is.
if time picker is set to -24h then i want to search for the last 4 days, if it is set to past week i want to search the past 10 days, and so on and so forth.

[mpasha]

by the way this is an Ad-hoc search.