topic Re: Merge dataset from tstat with data from external *.csv file. in Splunk Search

Merge dataset from tstat with data from external *.csv file.

Czakanski — Tue, 29 Sep 2020 19:45:17 GMT

Hello,

I have to merge dataset with data from csv file.
CSV file is well added.

Dataset:
ACTION,
CLASS,
CURRENT_PAGE,
F_WorkFlowNumber,
FULL_TIME

map.csv:
CURRENT_PAGE,
KIND

CURRENT_PAGE is common field.

I have to show data from dataset filtered by KIND?

How can I achieve this ?

Best
Dawid

Re: Merge dataset from tstat with data from external *.csv file.

somesoni2 — Fri, 01 Jun 2018 15:16:11 GMT

Is the CSV data added as lookup table file?

Re: Merge dataset from tstat with data from external *.csv file.

Czakanski — Fri, 01 Jun 2018 17:30:09 GMT

Somesoni2: yes of course... is fully readable by splunk

Re: Merge dataset from tstat with data from external *.csv file.

Azeemering — Tue, 29 Sep 2020 19:45:31 GMT

Simple way to do this would be something like this:

| from datamodel:"dataset_name_here" | inputlookup append=t inputlook_name_here.csv

Re: Merge dataset from tstat with data from external *.csv file.

FrankVl — Mon, 04 Jun 2018 08:16:52 GMT

If you just want to add the KIND field from the lookup for lines with matching CURRENT_PAGE value, to the results of a dataset search, then that sounds like a typical job for the lookup command: http://docs.splunk.com/Documentation/Splunk/7.1.0/SearchReference/Lookup

So in your case (you might need to replace map.csv with the name you defined for this lookup in Splunk):

...your search that returns the dataset results ...
| lookup map.csv CURRENT_PAGE OUTPUT KIND

This will add the KIND column to the search results, and you can add further search commands to filter / sort / count whatever you want 🙂

Re: Merge dataset from tstat with data from external *.csv file.

Czakanski — Tue, 29 Sep 2020 19:49:23 GMT

yes, I know but unfortunately this commmand doesn't associate records by common field.

Output looks like:
record from datamodel,
record from csv,
record from datamodel,
record from csv,

Instead of:
ACTION, CLASS, F_WorkFlowNumber, FULL_TIME, CURRENT_PAGE, KIND

Re: Merge dataset from tstat with data from external *.csv file.

FrankVl — Mon, 04 Jun 2018 08:20:10 GMT

That doesn't add the KIND field as a column to his dataset search results, that just glues the content of the lookup to the bottom of his search results. The way I understand his question a simple | lookup command would suffice.

Re: Merge dataset from tstat with data from external *.csv file.

Czakanski — Tue, 29 Sep 2020 19:49:21 GMT

Yes it was that i lookin for but my main question was: how to do it with "tstats".

Current query:

| from datamodel:"DATAMODEL"
| lookup map.csv CURRENT_PAGE
| where FULL_TIME > 0 and FULL_TIME < 10000000 and FORM="specified form from dropdown menu"

but how to transform it to "tstats"?

best
Dawid

Re: Merge dataset from tstat with data from external *.csv file.

FrankVl — Mon, 04 Jun 2018 13:51:18 GMT

Don't think that comment was aimed at my answer, was it? @richgalloway may have linked it wrongly?

Re: Merge dataset from tstat with data from external *.csv file.

FrankVl — Mon, 04 Jun 2018 13:54:12 GMT

Guess this comment belongs to my answer?

To use a tstats datamodel search, you just need to change that first line. I'm not much of an expert on tstats datamodel search syntax, so if you need specific help with writing the tstats query, that would have to come from someone else.

Re: Merge dataset from tstat with data from external *.csv file.

Czakanski — Tue, 29 Sep 2020 19:51:16 GMT

so I will repeat that question:

Yes it was that i lookin for but my main question was: how to do it with "tstats".

Current query:

| from datamodel:"DATAMODEL"
| lookup map.csv CURRENT_PAGE
| where FULL_TIME > 0 and FULL_TIME < 10000000 and FORM="specified form from dropdown menu"

but how to transform it to "tstats"?

I am lookin for solution like:
| tstats avg(FULL_TIME) from datamodel="DATAMODEL"
| lookup map.csv CURRENT_PAGE
| where FULL_TIME > 0 and FULL_TIME < 10000000 and FORM="specified form from dropdown menu"

but without pipe before lookup (I know it's necessary)

best
Dawid