Splunk Search

Display time in chart/table?

zsizemore
Path Finder

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.

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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

zsizemore
Path Finder

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?

0 Karma

woodcock
Esteemed Legend

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

sundareshr
Legend

Sure, add this to the end

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

sundareshr
Legend

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

zsizemore
Path Finder

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.

0 Karma

sundareshr
Legend

🙂 Yes, you should add FirstLogin

0 Karma

zsizemore
Path Finder

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?

0 Karma

sundareshr
Legend

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

0 Karma

sundareshr
Legend

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

0 Karma

zsizemore
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...