Why is eval term counting not working properly with my current search?

[zsizemore]

Hi,

So I have a report that is counting the number of users from each country by their longest "duration" or term of stay, as shown in the search:

| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User    
| eval term=last_login-first_login    
| eval term=case(term>(86400*7), "Long", term>86400 AND term<(86400*7), "Short", term<86400, "Very Short")  
| iplocation IP_address  
| where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="") 
| stats dc(User) AS usercount by Country City term User 
| dedup User Country
| chart sum(usercount) over Country by term

I'm doing some real-world testing with someone who's been in Canada the past two weeks, but in different cities. The report should show him as in Canada for "short" or "long", as an aggregate of the time he's been in the country, not in each individual city.

e.g. "user123" was in Burlington, Halifax, Toronto, Montreal all in "Very short" time periods. So it's counting "user123" as a very short user, when in fact it should be counting them as a Short user, because the total duration of the trip was more than 24 hours but less than 7 days.

Any ideas as to why this isn't working correctly?

[somesoni2]

What I get from your question description is that your term should be based on duration on country of stay, no the country and city both (ip address is associated with both). So what you need to do is to do the term calculation after your identify the Countr, like this

 base search | stats earliest(_time) as first_login latest(_time) as last_login by IP_address User    
 | eval term=last_login-first_login
 | iplocation IP_address
 | where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="")
 | stats sum(term) as term by User Country
 | eval term=case(term>(86400*7), "Long", term>86400 AND term<(86400*7), "Short", term<86400, "Very Short")    
 | dedup User Country
 | chart dc(User) over Country by term

[sundareshr]

I just tested this and its giving me the right results

| makeresults | eval term=86400*8 | eval term1=case(term>(86400*7), "Long", term>86400 AND term<(86400*7), "Short", term<86400, "Very Short") | table term term1

What are you getting for

| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User    
 | eval term=last_login-first_login    
 | eval term1=case(term>(86400*7), "Long", term>86400 AND term<(86400*7), "Short", term<86400, "Very Short")
| eval first_login=strftime(first_login, "%c")
| eval last_login=strftime(last_login, "%c") 
| eval term=tostring(term, "duration")
 | table first_login last_login term term1

[zsizemore]

I'm not totally sure I understand your answer. I adjusted my query to

| stats earliest(_time) as first_login latest(_time) as last_login by IP_address User    
| eval term=last_login-first_login    
| eval term1=case(term>(86400*7), "Long", term>86400 AND term<(86400*7), "Short", term<86400, "Very Short")  
| iplocation IP_address  
| where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="") 
| eval first_login=strftime(first_login, "%c")
| eval last_login=strftime(last_login, "%c") 
| eval term=tostring(term, "duration")
| table first_login last_login term term1

This doesn't show me really any of the information I'm actually looking for though..or in the format I need it in: Country - VS - S - L as column headers.

[sundareshr]

So, I tried the logic for term and it worked as expected. So what I wanted to verify is the data before the term logic. I was not proposing a solution, rather, trying to debug why you're getting no results. Hope this makes it clear.

In other words, if the first_login and last_login is not valid, your term will not right either. The second query I gave is only to validate the data is accurate, Next step will be to debug rest.

[zsizemore]

Okay I get what you're saying. I'm pretty sure it's capturing the term data accurately, I'm just not sure where to go from here.