Scheduled Search using dynamic values for parameters other than time

[DanielFordWA]

Hi,

I have a request to run a search that looks back over the previous month and returns the number of distinct users grouped by the number of days logged on, excluding users onboarded that month.

The search runs off a summary index in which all time buckets span=1d, hence no "| bucket span=1d _time" in the below search.

Is there a way to tokenize the | search NOT OnboardedDate=*/05/2015 to something like */-1mon@mon/@yr or something similar?

 index=dbuserday earliest=-1mon@mon latest=@mon cs_username="EXT*" NOT [| inputlookup testers_lookup | fields cs_username] 
| lookup EXTdata cs_username OUTPUTNEW OnboardedDate 
| search NOT OnboardedDate=*/05/2015 
| stats sum(count) as Hits  dc(_time) AS Days by cs_username OnboardedDate 
| rangemap field=Days 1=1-1 2-11=2-11 12+=12-40 
| stats dc(cs_username) as users by range 
| rename range AS "Days Logged on" 
| sort "Days Logged on"

Thanks,

Dan

[richgalloway · Accepted solution]

... | lookup EXTdata cs_username OUTPUTNEW OnboardedDate | eval Onboarded=strptime(OnboardedDate,"%d/%m/%Y")  | eval BegMonth=relative_time(now(), "-1mon@mon")  | search where Onboarded < BegMonth | ...

[DanielFordWA]

Thanks for the help with the above.

I have been asked to add the number of users that have logged on to the system before but not logged on that month and show this in the "Days Logged on" = 0 range.

"Days Logged on" users
0 1023
1 643
2-11 4923

12+ 30768

Is it possible to add a custom range form a sub search. For example..

index=dbuserday latest=-1mon@mon cs_username=EXT* NOT [| inputlookup testers_lookup  | fields cs_username] | stats dc(cs_username) 

...gives me the total users that have ever logged on, I can the subtract this from the other rangemap values or just subtract the current users for the month.

Is this even possible with rangemap or should I try to use Eval for everything?

[richgalloway]

I don't have an answer, but suggest you try adding to your rangemap.

[DanielFordWA]

Nealry there!

index=dbuserday earliest=-1mon@mon latest=@mon cs_username="EXT*" NOT [| inputlookup testers_lookup | fields cs_username] 
| lookup EXTdata cs_username OUTPUTNEW OnboardedDate 
| eval Onboarded=strptime(OnboardedDate,"%d/%m/%Y")  
| eval BegMonth=relative_time(now(), "-1mon@mon")  
| WHERE BegMonth>Onboarded  
| stats dc(_time) AS Days by cs_username
| eval Days=case(Days=1,"1",Days>=2 AND Days<=11,"2-11",Days>11,"12+")
| append [search index=dbuserday earliest=-32mon@mon latest=-1mon@mon cs_username=EXT* NOT [| inputlookup testers_lookup  | fields cs_username] | Eval Days="ALL" | dedup cs_username | table cs_username Days ]
| stats dc(cs_username) as Users by Days

This gives me....

Days Users
1 643

2-11 4923
12+ 30768
ALL 41027

Just need to do something like Days="0" = (Days="ALL"-((Days="1")+(Days="2-11")+(Days="12+")) and then remove the "ALL"

[DanielFordWA]

I had to remove the 'search' before the where but it worked great.

Thanks