topic Re: need table format data from csv raw data in Getting Data In

How can I table format data from csv raw data?

ravir_jbp — Tue, 18 Jul 2023 16:14:31 GMT

I have the below events getting generated which has list of file counts on diffrent directories with date. creating a table format output with headers "Directory" "date" and "Filecount". Need assitance in rex to orginzate this date in table format so that I can setup a dashboard for the same

"Directory","Date","FileCount" "E:\test\IEX\app1\Incoming","7/18/2023","12" "E:\test\IEX\Processed\Success","7/14/2023","11922" "E:\test\IEX\Processed\Success","7/15/2023","319"

"E:\test\IEX\Processed\Success","7/16/2023","449" "E:\test\IEX\Processed\Success","7/17/2023","14264" "E:\test\IEX\Processed\Success","7/18/2023","414" "E:\test\IEX\Error","7/13/2023","170" "E:\test\IEX\Error","7/14/2023","176" "E:\test\IEX\Error","7/15/2023","1" "E:\test\IEX\Error","7/17/2023","146" "E:\test\IEX\Error","7/18/2023","3" "E:\test\IEX\Error","7/10/2023","244" "E:\test\IEX\Error","7/11/2023","194" "E:\test\IEX\Error","7/12/2023","189"

Re: need table format data from csv raw data

gcusello — Tue, 18 Jul 2023 13:45:44 GMT

Hi @ravir_jbp,

if you configure your sourcetype as CSV or you add to your sourcetype INDEXED_EXTRACTIONS = csv, you automaticall have the values in each column as fields.

otherwise, you could extract these fields as delimited fields.

Ciao.

Giuseppe

Re: need table format data from csv raw data

ravir_jbp — Tue, 18 Jul 2023 13:52:15 GMT

This is my query

index=application1 source="D:\\app1\\filecount.csv" earliest=-5m latest=now | table Directory , Date, FileCount

This query does not bring any output.

FYI.. These events are generating every 5 minutes. Which gives the complete files count on specific directories. I need to populate data (5 minute refresh interval ) in table format on splunk dashboard.

Re: need table format data from csv raw data

gcusello — Tue, 18 Jul 2023 14:04:57 GMT

Hi @ravir_jbp,

what's the sourcetype of these data?

how is this sourcetype configurated?

it must be csv or it must contain "INDEXED_EXTRACTIONS = csv".

otherwise, you have to follow these steps:

run a search,
click on ">"
click on "event Actions"
Click on "Extract fields"
choose "Delimiters" and click on next,
follow the procedure to extract fields, identifying:
- delimiter,
- header (if present),
- field names.
save the field extractions

then, after some minutes, you can use these fields.

Anyway, the best approach is to correctly configure your sourcetype for a csv data type "INDEXED_EXTRACTIONS = csv".

for more information you can find many videos on internet.

Ciao.

Giuseppe

Re: need table format data from csv raw data

ravir_jbp — Tue, 18 Jul 2023 15:30:00 GMT

what's the sourcetype of these data?

The source of the data is from a powershell script which send output file (CSV) to specific location where splunk read that csv file as one event. That one event has all the list of directories and file counts.

how is this sourcetype configurated?

Not sure about this question.

Can you also confirm how to use ""INDEXED_EXTRACTIONS = csv"." in splunk query

Re: need table format data from csv raw data

gcusello — Wed, 19 Jul 2023 06:28:34 GMT

Hi @ravir_jbp,

sorry but there's some misunderstandings:

at first I spoke about "sourcetype", not "source", they are two different things: sourcetype is usually defined in the input stanza where you run the Powershell script and it's the way that Splunk uses to define specifications and parsing rules of a Data Flow, for this reason, you should set the sourcetype in the input.conf stanza containing the run of the script to exactly define the specifications of your data.

Then, in props.conf you have to add the option "INDEXED_EXTRACTIONS = csv" that it isn't possible to use in the search (that you defined "query") and that permits to you to automatically extract all the fields.

As I said, you should search in the YouTube Splunk Channel some video that describes how to ingest and parse csv data, these are some examples:

https://www.youtube.com/watch?v=fKoAB6n_ivs&pp=ygUKc3BsdW5rIGNzdg%3D%3D

https://www.youtube.com/watch?v=3kx0OGKy_XU&pp=ygUKc3BsdW5rIGNzdg%3D%3D

https://www.youtube.com/watch?v=SfEDJj7Jgpg&pp=ugMICgJpdBABGAHKBQpzcGx1bmsgY3N2

but you can find more others.

About sourcetype configuration, you have to create the props.conf file containing all the information to correctly parse you Data Flow, starting from Timestamp definition, Timestamp format and "INDEXED_EXTRACTIONS = csv".

Ciao.

Giuseppe