Splunk Search
Highlighted

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
Highlighted

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

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
Highlighted

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

Motivator

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDITANSYSUSER_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
Highlighted

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

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
Highlighted

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

Motivator

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDITANSYSUSER_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
Highlighted

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

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
Highlighted

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

Motivator

can you post a sample event

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

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
Highlighted

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

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=AUDITANSYSUSER_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