Splunk Search

How to write a search to organize data from a CSV file into a table format?

denisevw
Path Finder

I am trying to arrange some information received in a CSV file in a table format (as per example)

The two searches I used was as follow:
Search 1

index="labour" | stats sum(Hours) as TotalHours BY Client_Name, Resource | streamstats sum(Hours) BY Client_Name, Resource | stats list(Resource) as Resource_Name, list(TotalHours) as "Per Resource", sum(TotalHours) as TotalHours by Client_Name

Result:

Client_Name Resource_Name    Per Resource     TotalHours
Client 3        Resource 1          8              12
             Resource 5             4   

Search 2

index="labour" | stats first(Tper) as Week by Hours, Client_Name, Resource  | xyseries Client_Name Week Hours | addtotals

Result:

Client_Name Week25    Week26    Total
Client 3         6       6       12

Hope someone can make sense of this to assist me please.

Thanks in advance!

alt text

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

... | stats sum(Hours) as TotalHours BY Client_Name, Resource, Tper
| eval host=Client_Name . "::" . Resource | fields - Client_Name Resource
| xyseries host Tper TotalHours
| rex field=host "^(?<Client_Name>.*)::(?<Resource>.*)$" | fields - host
| eval BothHours=0 | foreach 2* [ eval BothHours = BothHours + $<<FIELD>>$ ]
| stats sum(BothHours) AS TotalHours list(2015*) AS week* sum(2015*) AS sum* list(Resource) AS Resource BY Client_Name
| addtotals row=f col=t
| fillnull value="CLIENT_TOTALS"
| foreach week* [ eval <<FIELD>> = if((Client_Name = "CLIENT_TOTALS"), $sum<<MATCHSTR>>$, $<<FIELD>>$) ]
| fields - sum*

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

... | stats sum(Hours) as TotalHours BY Client_Name, Resource, Tper
| eval host=Client_Name . "::" . Resource | fields - Client_Name Resource
| xyseries host Tper TotalHours
| rex field=host "^(?<Client_Name>.*)::(?<Resource>.*)$" | fields - host
| eval BothHours=0 | foreach 2* [ eval BothHours = BothHours + $<<FIELD>>$ ]
| stats sum(BothHours) AS TotalHours list(2015*) AS week* sum(2015*) AS sum* list(Resource) AS Resource BY Client_Name
| addtotals row=f col=t
| fillnull value="CLIENT_TOTALS"
| foreach week* [ eval <<FIELD>> = if((Client_Name = "CLIENT_TOTALS"), $sum<<MATCHSTR>>$, $<<FIELD>>$) ]
| fields - sum*
0 Karma

woodcock
Esteemed Legend

Show CSV file (just the 4 fields: Client_Name, Resource, Tper, Hours) data as comment under this and I will help you.

0 Karma

denisevw
Path Finder

Thanks for the reply.

You can use this example csv:

Client_Name,Resource_Name,Text,Activity,Activity_Description,Time_Code,Time_Code_Description,Tper,Date,Month,Month_Description,Unit_Description,Hours,Percentage
Client 1,Resource 1,Documentation,CON,Consulting,0,Regular Hours,201525,2015-08-31,201508,15-Aug,Hours,1,0.6
Client 1,Resource 2,Documentation,SUP,Client Support,0,Regular Hours,201526,2015-08-11,201508,15-Aug,Hours,5,3
Client 1,Resource 3,Documentation,CON,Consulting,0,Regular Hours,201525,2015-08-04,201508,15-Aug,Hours,2,1.2
Client 1,Resource 4,Documentation,TRA,Travel,0,Regular Hours,201526,2015-08-04,201508,15-Aug,Hours,2,1.2
Client 1,Resource 5,Documentation,CON,Consulting,0,Regular Hours,201525,2015-08-12,201508,15-Aug,Hours,2,1.2
Client 2,Resource 1,Documentation,CON,Consulting,0,Regular Hours,201526,2015-08-19,201508,15-Aug,Hours,6,3.6
Client 2,Resource 3,Documentation,TRA,Travel,0,Regular Hours,201525,2015-08-19,201508,15-Aug,Hours,1,0.6
Client 2,Resource 5,Documentation,PRM,Project Management,0,Regular Hours,201526,2015-08-26,201508,15-Aug,Hours,2,1.2
Client 3,Resource 1,Documentation,TRA,Travel,0,Regular Hours,201525,2015-08-13,201508,15-Aug,Hours,1,0.6
Client 3,Resource 5,Documentation,CON,Consulting,0,Regular Hours,201526,2015-08-13,201508,15-Aug,Hours,5,3

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...