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

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

gabarrygowin — Thu, 03 Aug 2017 16:45:16 GMT

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.

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

richgalloway — Thu, 03 Aug 2017 17:31:55 GMT

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 | ...

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

sbbadri — Tue, 29 Sep 2020 15:13:34 GMT

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

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

gabarrygowin — Thu, 03 Aug 2017 17:48:39 GMT

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.

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

sbbadri — Tue, 29 Sep 2020 15:13:37 GMT

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

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

woodcock — Thu, 03 Aug 2017 18:49:43 GMT

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

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

gabarrygowin — Thu, 03 Aug 2017 19:02:53 GMT

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

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

sbbadri — Thu, 03 Aug 2017 19:15:38 GMT

can you post a sample event

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

gabarrygowin — Tue, 29 Sep 2020 15:13:55 GMT

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

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

gabarrygowin — Thu, 03 Aug 2017 19:47:01 GMT

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.

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

gabarrygowin — Tue, 29 Sep 2020 15:14:09 GMT

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

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

sbbadri — Tue, 29 Sep 2020 15:14:12 GMT

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

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

gabarrygowin — Thu, 03 Aug 2017 21:04:52 GMT

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

Thanks again!