Splunk Search

how to use transaction command for event spanning multiple days

jassal
New Member

I'm trying to show the total HVAC usage during the day using transaction command:

name=thermostatoperatingstate | transaction device startswith=cooling endswith=idle | eval duration=round(duration/3600,1) | timechart span=1d sum(duration) as sum

However for transactions that span through midnight I'd like to split the "duration" at midnight so I can show how much the AC ran during a given day. In the command above if AC started at 8PM and stopped at 2AM, I'd like 4 hours usage to be listed for the given day and 2 hours usage to be listed against next day. With the command about I get 6 hours of AC usage for the given day.

Is there a way to day that?

0 Karma

jassal
New Member

This is how the raw data is logged in. Thx.

[02/Jul/2018:06:17:42 -0400] "GET /log.php?device=Nest-Up&name=thermostatOperatingState&value=idle
[01/Jul/2018:19:44:41 -0400] "GET /log.php?device=Nest-Up&name=thermostatOperatingState&value=cooling
[01/Jul/2018:18:56:41 -0400] "GET /log.php?device=Nest-Up&name=thermostatOperatingState&value=idle
[01/Jul/2018:18:32:42 -0400] "GET /log.php?device=Nest-Up&name=thermostatOperatingState&value=cooling

0 Karma

niketn
Legend

@jassal, can you please share couple of sample raw data for cooling and idle? We might need to create field extraction for action as cooling and idle so that we can use stats instead of transaction.

For your scenario you should be better off choosing stats over transaction command. Refer to my older answer: https://answers.splunk.com/answers/483152/is-the-transaction-command-suitable-for-large-volu-1.html

Also, Answers should have several posts on similar lines for converting transaction to stats. Following are couple such example, please adapt as per your need.

1) https://answers.splunk.com/answers/507469/will-using-the-join-and-transaction-commands-work.html
2) https://answers.splunk.com/answers/624143/how-to-make-a-transaction-using-same-id-that-has-e.html

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...