Splunk Search

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

zsizemore
Path Finder

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?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

sundareshr
Legend

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
0 Karma

zsizemore
Path Finder

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.

0 Karma

sundareshr
Legend

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.

0 Karma

zsizemore
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...