Splunk Search

How to modify my search to add a column that sums CostPerDay field for each Feature field?

Path Finder

Hello all,

First thanks for the participation in this forum, many of your older solutions have helped greatly in my success in getting Splunk into our enterprise. But I find myself stuck searching, trying, etc.

We have an existing search to show how many engineering licenses are checked out past 24 hours and display as follows:

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | stats count by Username,AnsysFeature,AnsysDays | sort-AnsysDays 

The challenge is that we need to add a column "Cost Per Day" using the cost of the 'Feature' from the lookup we've uploaded. The lookup is called ANSYSCOST.csv and is formatted:

PRODUCT FEATURE SplunkFeatureName    GACostPerDay 

Can someone help me get the search working to add a column that sums Cost Per Day for each Feature checked out?

Regards.

0 Karma
1 Solution

Motivator

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | lookup ANSYSCOST.csv FEATURE as AnsysFeature OUTPUTNEW PRODUCT as Product, SplunkFeatureName GACostPerDay | stats count by Username,AnsysFeature,AnsysDays GACostPerDay | rename GACostPerDay as "Cost Per Day" | sort-AnsysDays

Note: Need to use common field from lookup and events before "OUTPUTNEW" keyword

View solution in original post

0 Karma

Esteemed Legend

Like this:

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE
| bin _time span=1d
| stats count BY _time Username AnsysFeature AnsysDays
| lookup ANSYSCOST.csv FEATURE as AnsysFeature OUTPUT GACostPerDay
| stats sum(count) AS count sum(GACostPerDay) AS TotalCost BY Username AnsysFeature AnsysDays
0 Karma

Motivator

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | lookup ANSYSCOST.csv FEATURE as AnsysFeature OUTPUTNEW PRODUCT as Product, SplunkFeatureName GACostPerDay | stats count by Username,AnsysFeature,AnsysDays GACostPerDay | rename GACostPerDay as "Cost Per Day" | sort-AnsysDays

Note: Need to use common field from lookup and events before "OUTPUTNEW" keyword

View solution in original post

0 Karma

Path Finder

You guys rock!

I just realized I didn't pose a perfect question here. I also need to compute (eval?) the AnsysDays x Cost Per Day and provide a Total cost per session to Date.

Thanks for the progress, can't tell you how much this has helped.

0 Karma

Motivator

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | eval totalCost = AnsysDays * GACostPerDay |bucket span=1d _time | stats sum(totalCost) as "Total cost per session" by Username,AnsysFeature

I hope this helps

0 Karma

Path Finder

Hmm, close. The columns look good but the math isn't executing the AnsysFeature*GACostPerDay to add amount to Total cost per session.

0 Karma

Motivator

can you post a sample event

0 Karma

Path Finder

Aug 3 03:50:03 torlicvlp02 AUDIT_ANSYS_USER_LICENSE: usera has been using feature ansys since: 1 days 16 hours 59 minutes 03 seconds on the hosts: XXXX.ga.com

0 Karma

Path Finder

So, I'm thinking the 'days' and $ wording in AnsysDays and GACostPerDay are tripping up the eval. Taking them out so that I only have digits. Will take a few before the new csv is seen.

0 Karma

Path Finder

Well, it's not pretty but it works!!!! Taking your helpful inputs I got the following to work:

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | lookup ansyscost.csv FEATURE as AnsysFeature OUTPUTNEW PRODUCT as Product, SplunkFeatureName GACostPerDay | stats count by Username,AnsysFeature,AnsysDay,GACostPerDay | eval Session Cost=AnsysDay*GACostPerDay | rename GACostPerDay as "Cost Per Day" | rename Username as User | rename AnsysFeature as "ANSYS Item" | rename AnsysDay as "Days Checked Out" | sort-"Days Checked Out" | fields - count

0 Karma

Motivator

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | lookup ansyscost.csv FEATURE as AnsysFeature OUTPUTNEW PRODUCT as Product, SplunkFeatureName GACostPerDay | stats count by Username,AnsysFeature,AnsysDay,GACostPerDay | eval Session Cost=AnsysDay*GACostPerDay | rename GACostPerDay as "Cost Per Day" | rename GACostPerDay as "Cost Per Day", Username as User, AnsysFeature as "ANSYS Item",AnsysDay as "Days Checked Out" | sort - "Days Checked Out" | fields - count

it enough to use single rename command

0 Karma

Path Finder

You're absolutely correct and thanks, was eating the elephant bite by bite.

Thanks again!

0 Karma

SplunkTrust
SplunkTrust

This should get you started. If the same feature might be present in more than one product then you'll need a product field to narrow the lookup.

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | stats count by Username,AnsysFeature,AnsysDays | sort-AnsysDays | lookup ANSYSCOST.csv FEATURE as AnsysFeature OUTPUT GACostPerDay | ...
---
If this reply helps you, an upvote would be appreciated.
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!