topic How to to sort the date so that my graph is coherent? in Splunk Search

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

numeroinconnu12 — Fri, 07 Apr 2023 17:18:49 GMT

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

Re: sorting date

gcusello — Thu, 06 Apr 2023 12:30:31 GMT

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

Re: sorting date

richgalloway — Thu, 06 Apr 2023 12:54:21 GMT

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.

Re: sorting date

numeroinconnu12 — Thu, 06 Apr 2023 13:42:54 GMT

it's work, thank you @gcusello 🙂

Re: sorting date

gcusello — Thu, 06 Apr 2023 14:28:27 GMT

Hi @numeroinconnu12 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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