topic Re: Put stats(values) and stats(count) in the same table (with tstats) in Splunk Search

Put stats(values) and stats(count) in the same table (with tstats)

3DGjos — Thu, 07 Mar 2019 20:17:18 GMT

Hello,

I need help with a dashboard Panel I need to make for a client. This guy wants a failed logins table, but merging it with a a count of the same data for each user. My data is coming from an accelerated datamodel so I have to use tstats.

Let me give you an example of what I need to do:

I need to merge this query:

| tstats summariesonly=true allow_old_summaries=true count from datamodel=Authentication.Authentication where   Authentication.action="failure"   by Authentication.user

with this one:

| tstats summariesonly=true allow_old_summaries=true values from datamodel=Authentication.Authentication where   Authentication.action=failure  by _time  Authentication.user Authentication.src Authentication.dest Authentication.app
|`truncate_name("Authentication")`
| eval Time = strftime(_time, "%d-%b-%Y %H:%M:%S")
| table Time  user app dest src
| rename user AS User src AS From dest AS "Destination" app AS "App"
| sort -Time

in the same table.

Thanks!

Re: Put stats(values) and stats(count) in the same table (with tstats)

chrisyounger — Thu, 07 Mar 2019 20:22:52 GMT

Does this work:

| tstats summariesonly=true allow_old_summaries=true count from datamodel=Authentication.Authentication where Authentication.action="failure" by Authentication.user 
| append 
    [| tstats summariesonly=true allow_old_summaries=true values from datamodel=Authentication.Authentication where Authentication.action=failure by _time Authentication.user Authentication.src Authentication.dest Authentication.app]
| `truncate_name("Authentication")` 
| eval Time = strftime(_time, "%d-%b-%Y %H:%M:%S") 
| stats values(Time) as Time values(app) as "App"  values(dest) as "Destination" values(src) as "Source" sum(count) by user
| rename user AS User
| sort -Time

Re: Put stats(values) and stats(count) in the same table (with tstats)

woodcock — Fri, 08 Mar 2019 03:16:18 GMT

Like this:

| tstats summariesonly=true allow_old_summaries=true values
FROM datamodel=Authentication.Authentication 
WHERE nodename=Authentication.Failed_Authentication 
BY _time Authentication.user Authentication.src Authentication.dest Authentication.app 
| rename Authentication.* AS *
| rename _time AS Time
| fieldformat Time = strftime(Time, "%d-%b-%Y %H:%M:%S") 
| rename user AS User src AS From dest AS Destination app AS App
| table Time User App Destination From 
| sort 0 -Time
| eventstats count AS TotalForThisUser BY User

Re: Put stats(values) and stats(count) in the same table (with tstats)

3DGjos — Thu, 25 Apr 2019 14:14:08 GMT

Hello, sorry for the delay. I took your query and it worked, then my client changed the requirement and I had to go with something like woodcock did.

Thanks

Re: Put stats(values) and stats(count) in the same table (with tstats)

3DGjos — Thu, 25 Apr 2019 14:16:50 GMT

Hey, I managed to do it this way:

index=myindex action=failure

   | eval "Time"=strftime(_time, "%d/%m/%Y %H:%M:%S")
   | eval "accesstype" =`mymacro(1)`
     | table Time "accesstype" user action app src dest
  |  rename myrenames

     | eventstats count AS "Total by destination" BY src, user
     | sort - "Total by destination"

Thanks!