Getting Data In

Write to Output lookup

vijaysubramania
Path Finder

HI Team,

Need one help, I want to run a schedule for the below search events  every 1 hr and capture the inportant fields  like responseStatus, requestMethod, requestURL, servicePath, Total request, hour, day,  etc. and write to outputfile in csv. So that I can use this report for my dashboards. 

The idea behind this is becase, our application logs millions of events per day and if we want to look for the historical data for reports, it takes long time to run and load the dashboard.

I want to run this every hour and append the data it in the existing csv file.

I tried the lookup but didn't work for me. Any solutions welcome

time=2021-04-14T17:57:07+00:00 requestId=751411798490203 traceId=751411798490203 servicePath="/ecp/" remoteAddr=71.74.45.8 clientIp=24.161.128.196 clientAppVersion=NOT_AVAILABLE app_version=- apiKey=72c07648-ea14-34f2-abed-e38263580b5c oauth_leg=2-legged authMethod=oauth apiAuth=true apiAuthPath=/ecp/ oauth_version=1.0 target_bg=default requestHost=api.spectrum.net requestPort=8080 requestMethod=GET requestURL="/ecp/entitlements/v2/entitlements?divisionId=NEW.004&accountNumber=28290420" requestSize=560 responseStatus=200 responseSize=8422 responseTime=0.025 userAgent="IPVS" mapTEnabled="F" charterClientIp="V-1|IP-24.161.128.196|SourcePort-|TrafficOriginID-24.161.128.196" sourcePort="" oauth_consumer_key="72c27648-ea14-44f2-abed-e38263580b5c" x_pi_auth_failure="-" pi_log="pi_ngxgw_access"

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

vijaysubramania
Path Finder

Thanks @aasabatini . this helps 👍

View solution in original post

0 Karma

vijaysubramania
Path Finder

Thanks @aasabatini . this helps 👍

0 Karma

vijaysubramania
Path Finder

Thanks @aasabatini .
I actually tried other way too using timechart and look up the statstics gives the data in the format i need. But, will this data can be saved in the outputlookup file. I want to use this outputlookup file to plot the charts for multiple services reporting
"my search" | dedup requestId | timechart span=1h count by responseStatus limit=0

vijaysubramania_0-1619650350782.png

 

your query
"my search" | dedup requestId | stats count by responseStatus limit=0 | transpose header_field=responseStatus

vijaysubramania_1-1619650715118.png

outputlookup

"mysearch" |  | dedup requestId | stats count by responseStatus limit=0 | transpose header_field=responseStatus | outputlookup "ecpstats.csv" append=true

vijaysubramania_2-1619651073432.png

 

0 Karma

vijaysubramania
Path Finder

Thanks for the inputs.

But, I want to see the data count in horizontal format with count.

200201202203204TotalApplicationDateDayTimeRequestURL
1245820ECP04/14/21Wed14:00Entitlements

 

0 Karma

aasabatini
Motivator

Hi @vijaysubramania 
in this case you need the transpose comand.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose

Now I don't know your dataset but let me show you an example:

normal search have this results:

index=_internal sourcetype=splunk_web_access | stats count by status 

aasabatini_0-1618481605527.png

 

 use transpose comand at the end of the search and specify the header_field

index=_internal sourcetype=splunk_web_access | stats count by status | transpose header_field=status

aasabatini_1-1618481754062.png

if you want filter the first field column use table after the search

index=_internal sourcetype=splunk_web_access | stats count by status | transpose header_field=status | table 200 303 304 404

 

hope can help

 

 

“The answer is out there, Neo, and it’s looking for you, and it will find you if you want it to.”
0 Karma

aasabatini
Motivator

Hi @vijaysubramania 

you can use the outputlookup comand

"your search" | table "the list of your fields" | outputlookup "lookup name" 

you can check the documentation

here:https://docs.splunk.com/Documentation/Splunk/8.1.3/SearchReference/Outputlookup

p.s if you want save your data in append you need to add the append condition in your search.

"your search" | table "the list of your fields" | outputlookup "lookup name" append=true

 

“The answer is out there, Neo, and it’s looking for you, and it will find you if you want it to.”
0 Karma

vijaysubramania
Path Finder

Hi Team,

 I have created the lookupfile which i created with Status count of each status codes, Application Name and day. I want to calculate the success % from the data I saved without performing any transpose as I am capturing all the http status codes each day. The purpose is as the volume of data is too high, I am using lookup table to reduce the load and faster response

| inputlookup MC_V2_DAILY.csv

 

vijaysubramania_0-1620633450450.png

 

Tags (2)
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...