Reporting

[Search] Change Pivot search to datamodel search

test_qweqwe
Builder

Hi!
I never used Pivot command and now I need to change this:

| pivot Authentication Authentication count(Authentication) AS "Count of Authentication" SPLITROW src AS src SPLITCOL action LIMIT src BY TOP 15 count(is_Failed_Authentication) FILTER action isNot unknown FILTER action isNot search FILTER src isNot "10.10.20.58" FILTER src isNot "10.10.20.140" FILTER src isNot "10.10.20.200" FILTER src isNot unknown FILTER src isNot *az* FILTER src isNot struts FILTER src isNot *DESKTOP* SORT 1000 src ROWSUMMARY 0 COLSUMMARY 0 NUMCOLS 100 SHOWOTHER 0 |rename src AS Host | sort -failure

to something like:

| tstats `summariesonly` values(Authentication.app) as app,count from datamodel=Authentication.Authentication by Authentication.action,Authentication.src | `drop_dm_object_name("Authentication")` | eval success=if(action="success",count,0) | eval failure=if(action="failure",count,0) | stats values(app) as app,sum(failure) as failure,sum(success) as success by src | where success > 0 | xswhere failure from failures_by_src_count_1d in authentication is above medium | `settags("access")`

^ this search shows success/failed authentication only by remote (and i need in general all success/failed by all users)
And by low skill with working tstats and Pivot syntax I'm stack :<

And one more question, it's possible to run my Pivot search with acceleration?

0 Karma
1 Solution

test_qweqwe
Builder

This.

| tstats `summariesonly` count from datamodel=Authentication.Authentication where Authentication.src!=unknown by Authentication.action,Authentication.src 
| `drop_dm_object_name("Authentication")` 
| eval success=if(action="success",count,0) 
| eval failure=if(action="failure",count,0) 
| stats sum(failure) as failure,sum(success) as success by src 
| where success > 0
| xswhere failure from failures_by_src_count_1d in authentication is above medium 
| fields + src failure success 
| rename src as Host failure as Failure success as Success
| sort -Failure

View solution in original post

0 Karma

test_qweqwe
Builder

This.

| tstats `summariesonly` count from datamodel=Authentication.Authentication where Authentication.src!=unknown by Authentication.action,Authentication.src 
| `drop_dm_object_name("Authentication")` 
| eval success=if(action="success",count,0) 
| eval failure=if(action="failure",count,0) 
| stats sum(failure) as failure,sum(success) as success by src 
| where success > 0
| xswhere failure from failures_by_src_count_1d in authentication is above medium 
| fields + src failure success 
| rename src as Host failure as Failure success as Success
| sort -Failure
0 Karma

mayurr98
Super Champion

hey @test_qweqwe
Try this,

| tstats count from datamodel=Authentication where (Authentication.action!="unknown" OR Authentication.action!="search") AND (Authentication.src!="10.10.20.58" OR Authentication.src!="10.10.20.200" OR Authentication.src!="10.10.20.140" OR Authentication.src!="unknown" OR Authentication.src!=*az* OR Authentication.src!="struts" OR Authentication.src!=*DESKTOP*) by Authentication.src Authentication.action | rename Authentication.* as *  | <further query>

This is not an exact query but this is how you must start.
Let me know if it helps!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...