Splunk Search

How to to sort the date so that my graph is coherent?

numeroinconnu12
Path Finder

Hello,

thank you in advance for your feedback.

I would like to sort the date so that my graph is coherent, can you please help me?

 

| tstats summariesonly=t allow_old_summaries=t count from datamodel=Authentication.Authentication where [| inputlookup ****.csv  | eval host=Machine |  table host  ] AND NOT Authentication.src_user="*$" AND NOT Authentication.src_user="unknown" by host,Authentication.src,Authentication.src_user,_time
| eval host=upper(host)
| eval Serveur='Authentication.src'
| eval **** = upper(trim(('Authentication.src_user')))
| eval samaccountname=substr(trim(upper('Authentication.src_user')),1,7)
| eval domaine="****"
| lookup **** samaccountname as samaccountname domaine as domaine
| search email="*"
| eval **** = samaccountname
| table  ****, ****, host, email,ua,cn,_time,cn
| join host type=left  [| inputlookup *****.csv  | eval host=Machine]
| where  Ferme="****" OR  Ferme="****" OR Ferme="*****" OR Ferme="*****" OR Ferme="****"
| stats values(Ferme) as Ferme values(_time) as _time by *****,cn
| eval Date=strptime(_time,"%d/%m/%Y")
| sort Date
| eval Date=strftime(_time,"%d/%m/%Y")
| stats count as "nb. de connexion par jour" by Ferme,Date

 

 


date.PNG

 

Labels (4)
Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @numeroinconnu12 ,

to sort a date you have to transform them in epochtime, so, to sort your search:

| tstats summariesonly=t allow_old_summaries=t count from datamodel=Authentication.Authentication where [| inputlookup ****.csv  | eval host=Machine |  table host  ] AND NOT Authentication.src_user="*$" AND NOT Authentication.src_user="unknown" by host,Authentication.src,Authentication.src_user,_time
| eval host=upper(host)
| eval Serveur='Authentication.src'
| eval **** = upper(trim(('Authentication.src_user')))
| eval samaccountname=substr(trim(upper('Authentication.src_user')),1,7)
| eval domaine="****"
| lookup **** samaccountname as samaccountname domaine as domaine
| search email="*"
| eval **** = samaccountname
| table  ****, ****, host, email,ua,cn,_time,cn
| join host type=left  [| inputlookup *****.csv  | eval host=Machine]
| where  Ferme="****" OR  Ferme="****" OR Ferme="*****" OR Ferme="*****" OR Ferme="****"
| stats values(Ferme) as Ferme values(_time) as _time by *****,cn
| stats count as "nb. de connexion par jour" by Ferme,Date
| eval epochDate=strptime(Date,"%d/%m/%Y")
| sort epochDate
| fields - epochDate

in addition, it's unuseful to sort before a stats command.

Ciao.

Giuseppe

View solution in original post

0 Karma

numeroinconnu12
Path Finder

it's work, thank you @gcusello 🙂

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You did well to convert the Date field to epoch form before sorting.  However, the stats command spoiled that work by re-sorting by the ferme field.  Where the ferme field has repeated values, they are sorted lexicographically by Date.

I believe you can resolve the problem by putting the strftime call after the final stats.

---
If this reply helps you, Karma would be appreciated.
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @numeroinconnu12 ,

to sort a date you have to transform them in epochtime, so, to sort your search:

| tstats summariesonly=t allow_old_summaries=t count from datamodel=Authentication.Authentication where [| inputlookup ****.csv  | eval host=Machine |  table host  ] AND NOT Authentication.src_user="*$" AND NOT Authentication.src_user="unknown" by host,Authentication.src,Authentication.src_user,_time
| eval host=upper(host)
| eval Serveur='Authentication.src'
| eval **** = upper(trim(('Authentication.src_user')))
| eval samaccountname=substr(trim(upper('Authentication.src_user')),1,7)
| eval domaine="****"
| lookup **** samaccountname as samaccountname domaine as domaine
| search email="*"
| eval **** = samaccountname
| table  ****, ****, host, email,ua,cn,_time,cn
| join host type=left  [| inputlookup *****.csv  | eval host=Machine]
| where  Ferme="****" OR  Ferme="****" OR Ferme="*****" OR Ferme="*****" OR Ferme="****"
| stats values(Ferme) as Ferme values(_time) as _time by *****,cn
| stats count as "nb. de connexion par jour" by Ferme,Date
| eval epochDate=strptime(Date,"%d/%m/%Y")
| sort epochDate
| fields - epochDate

in addition, it's unuseful to sort before a stats command.

Ciao.

Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @numeroinconnu12 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...