topic Re: Display time in chart/table? in Splunk Search

Display time in chart/table?

zsizemore — Thu, 30 Jun 2016 13:02:12 GMT

I'm trying to have a table that displays

Time | Country | City | Very Short |Short | Long

right now I've managed to get it to show everything I need, except the time.

My query is

| 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, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long") 
| chart dc(User) as usercount by IP_address,term
| iplocation IP_address  
| dedup City
| where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="")
| table Country City "Very Short" Short Long

I tried using eval to do a first_login=strftime(first_login,"F%") and then aggregate it with stats first() but that made it so the term columns didn't display (VS/S/L).

Any ideas? I've googled for hours and can't seem to come up with much.

Re: Display time in chart/table?

sundareshr — Tue, 29 Sep 2020 10:07:34 GMT

Which time are you looking for. There's a _time, first_login & last_login.

Re: Display time in chart/table?

zsizemore — Tue, 29 Sep 2020 10:05:06 GMT

to be honest i'm not 100% sure. I think the first_login but i tried putting _time as a column in the chart also first_login and didn't get any results.

Re: Display time in chart/table?

sundareshr — Thu, 30 Jun 2016 13:29:57 GMT

Try this

| 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, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long")  | eval ti=first_login."#".IP_address | chart dc(User) as usercount by ti ,term | rex field=ti "(?<FirstLogin>[^#]+)#(?<IP_address>.*)" | iplocation IP_address | dedup City | where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="") | table Country City "Very Short" Short Long

Re: Display time in chart/table?

zsizemore — Thu, 30 Jun 2016 13:36:28 GMT

is there another field i should be adding to the table? because its just giving me the same results as the query I started with.

Re: Display time in chart/table?

sundareshr — Thu, 30 Jun 2016 13:42:17 GMT

🙂 Yes, you should add FirstLogin

Re: Display time in chart/table?

woodcock — Thu, 30 Jun 2016 13:44:46 GMT

Like this:

... | 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, "Very Short", term>86400 AND term<(86400*7), "Short", term>(86400*7), "Long") 
| eval chartByComboField = IP_address . ":::" . first_login
| chart dc(User) as usercount BY chartByComboField term
| rex field=chartByComboField "(?<IP_address>.*):::(?<Time>.*)" | fields - chartByComboField 
| iplocation IP_address  
| dedup City
| where (isnotnull(Country) AND isnotnull(City) AND NOT Country="United States" AND trim(Country)!="" AND trim(City)!="")
| table Time Country City "Very Short" Short Long

Re: Display time in chart/table?

zsizemore — Thu, 30 Jun 2016 14:11:15 GMT

Thanks it's showing up now. I don't totally understand what format its displayed in... for example it says 1467082744.850 for the time. Is there a way i can convert that to M/D/Y?

Re: Display time in chart/table?

zsizemore — Thu, 30 Jun 2016 14:11:22 GMT

Thanks it's showing up now. I don't totally understand what format its displayed in... for example it says 1467082744.850 for the time. Is there a way i can convert that to M/D/Y?

Re: Display time in chart/table?

sundareshr — Thu, 30 Jun 2016 14:14:28 GMT

Sure, add this to the end

... | eval FirstLogin=strftime(FirstLogin, "%m/%d/%Y")

Re: Display time in chart/table?

woodcock — Thu, 30 Jun 2016 14:14:32 GMT

Yes, just add this:

... | fieldformat Time = strftime(Time, "%m/%d/%Y")

Or maybe you meant this:

... | fieldformat Time = strftime(Time, "%m/%d/%Y %H:%M:%S")

Re: Display time in chart/table?

sundareshr — Thu, 30 Jun 2016 14:14:49 GMT

... | eval FirstLogin=strftime(FirstLogin, "%m/%d/%Y")